DWHPro Insights

data warehouse

Understanding Teradata Hash Collisions – A Case Study Widely read

Illustration of a stopwatch dial on an orange paint-splash background

To comprehend the issue of Teradata hash collisions, I will briefly explain how rows are allocated. If you are unfamiliar with Teradata Architecture or require a refresher, I suggest reading the following article beforehand: As you know, a hashing algorithm distributes a table’s rows to the AMPs. The Foundations The hashing algorithm accepts one or …

Read more

Building a Teradata Data Warehouse: Considerations for ETL Process, SQL Queries, and Physical Data Model Popular

Illustration of a network graph with circular nodes joined by lines

This post aims to compile all crucial aspects to be considered while constructing a Teradata Data Warehouse, including the ETL process and SQL queries. This list is just the beginning, and I anticipate receiving valuable feedback from my readers to expand it in the future. Initially, I have provided a few concepts, but I intend …

Read more

Understanding the Waterfall Model: Phases, Problems, and Solutions for Data Warehouse Projects Popular

Illustration of a dense isometric network of connected data nodes

What is the Waterfall Model? The waterfall model facilitates the sequential progression of a data warehouse project. Each phase must be concluded before the subsequent stage commences. The following stages will be navigated: What are the Problems with the Waterfall Model? Typically, these issues are identified solely during the verification stage of testing. Why do …

Read more

Teradata Referential Integrity: What it is and Why You Need it for Data Consistency and Performance Popular

Illustration of a dense isometric network of connected data nodes

Introduction to Teradata Referential Integrity Teradata implements 3 Types of Referential Integrity. 1. What is Standard Referential Integrity? The Standard Referential Integrity checks every Row INSERT, DELETE, or UPDATE immediately to ensure referential integrity. A reference index sub-table is required for referential integrity. Violation of referential integrity results in the failure of execution and generates …

Read more

The Importance of Teradata Surrogate Keys Popular

Illustration of a dense isometric network of connected data nodes

What are Teradata Surrogate Keys? A surrogate key is an artificial identifier mapped to a natural key, often INTEGER or BIGINT. Generated identity values need not be gapless or globally increasing in insertion or retrieval order. We can use the ROW_NUMBER() function in Teradata to programmatically generate the surrogate key or an IDENTITY column for …

Read more

Achieve Incredible Teradata Join Performance

Illustration of a stopwatch dial on an orange paint-splash background

Executive summaryGUEST POST BY ARTEMIY KOZYR Today I shed some light on how Data Warehousing lies at the core of Retail Banking operations. We will see the actual case of vital marketing process malfunction and dive deep under the surface level to comprehend data alchemy technical issues. You will learn how to approach such issues, …

Read more

Teradata ASCII: Built-in Function and Legacy CHAR2HEXINT Example

Illustration of a stopwatch dial on an orange paint-splash background

Introduction

The tested Teradata release supports SELECT ASCII('B');, which returned 66. The CHAR2HEXINT expression below is a legacy encoding-dependent example, not a necessary replacement for the built-in function.

The solution is as follows:

We use the Teradata SQL function CHAR2HEXINT to obtain a hexadecimal value for a character. Our objective is to convert this hexadecimal value to its decimal counterpart, which signifies the character’s ASCII code.

The following legacy expression demonstrates the calculation for this specific character and encoding.

SELECT
CASE SUBSTRING(CHAR2HEXINT('B') FROM 3 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END * 16 +
CASE SUBSTRING(CHAR2HEXINT('B') FROM 4 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END

Using a CASE statement, the query extracts two hexadecimal digits with the SUBSTRING function and converts them to decimal values. It then calculates the character’s ASCII code by multiplying the first digit’s decimal value by 16 and adding the second digit’s decimal value.

Conclusion

Prefer the documented ASCII function when its semantics match the task. The legacy workaround returned 66 for B with CHAR2HEXINT yielding 0042 in the tested context. Its fixed substring positions are not a general solution for arbitrary character sets or Unicode characters.

Teradata Viewpoint: Diagnose Slow and Waiting Queries Most read

Illustration of a person in a suit standing beside a database cylinder

What is Teradata Viewpoint? Teradata Viewpoint is a browser-based monitoring and administration interface built from portlets. For query diagnosis, its value is connecting a late report to a session, its current state and the evidence needed for the next investigation. Available systems, portlets and actions depend on your installation and assigned roles. Worked example: investigate …

Read more

Teradata Native Object Store on AWS, Microsoft Azure, and Google Cloud Popular

Illustration of three stacked database units of differing heights inside a circle

Teradata Vantage has also made its move into the public cloud. Teradata Vantage is now available in AWS, Microsoft Azure, and Google Cloud. It is worth noting that the same Teradata software runs on these three major cloud providers. This makes it relatively easy to run Teradata on-premise and in these 3 different public clouds …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam