Modeling Dimensions and Hierarchies - Teradata Schema Workbench

Teradata Schema Workbench User Guide

Product
Teradata Schema Workbench
Release Number
16.20
16.10
15.10
Published
June 2015
Language
English (United States)
Last Update
2018-05-25
dita:mapPath
gvf1512702977003.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-4106
Product Category
Teradata Tools and Utilities

In a star schema, foreign keys in the fact table refer to dimensional table keys. When defining a dimension in Teradata Schema Workbench, specify the column(s) in the fact table that define the hierarchy or hierarchies of that dimension. The figure below shows a schema diagram with product_id as the foreign key in the Sales Fact table for the Product 1 dimension.

Example Fact Table

Each hierarchy created using Teradata Schema Workbench must specify a Main Hierarchy Table. A hierarchy uses the Main Hierarchy Table to create join key mappings to the fact table. Every Main Hierarchy Table must map to the fact table. Other tables for the hierarchies (such as arms of a normalized snowflake schema) then map directly or indirectly to the Main Hierarchy Table.

The figure below shows a Product table, Promotion table and Store table being used as Main Hierarchy Tables for the Product, Promotion and Store dimensions. Product Class is an example of an additional hierarchy table being mapped to the Main Hierarchy Table as part of a snowflake arm.

Teradata Schema Workbench also supports multiple column joins. For example, the Promotion Schedule table in the figure below is an example of a snowflake dimension table using a composite primary key; therefore, you must specify two columns for the join to the Promotion table.

Example Main Hierarchy Table

Teradata BI Repository does not handle ragged hierarchies where leaves are not all at the lowest level, nor skip-level hierarchies where some low level leaves do not descend from an immediately above parent level. See Creating a Hierarchy for a Dimension.