Modeling Parent-Child 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

Teradata Business Intelligence Repository only supports fully populated hierarchies. It does not support ragged hierarchies, where leaf nodes can be at different levels. Additionally, it does not support hierarchies that are modeled as a relational Parent-Child table.

If a hierarchy is defined within a Parent-Child table, it must be flattened into a normalized set of table(s). The following example demonstrates how to flatten a hierarchy from a Parent-Child table into a normalized dimension.

Example - Modeling Parent-Child Hierarchies

In the TAA database, Tdat_Sales_Org contains the Parent Child relationships of the TAA Sales Organization hierarchy. The TAA Sales Organization hierarchy contains four levels (Business Unit, Division, Area, and Sales Center), with names defined in the Region_Name column.

The database example also contains a ragged hierarchy, in that European data has three levels of reporting, where Sales Center, for example, may not have any values. In this case the Organization hierarchy will have to be flattened and then evened out.

Use the following examples and descriptions as a guide to flatten Parent-Child hierarchies.

The following example is a DDL for the Parent Child table:

CREATE SET TABLE TAA.tdat_Sales_Org ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT
   (
   Region_id CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
   Region_Name VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
   Parent_Region_id CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
   Country_cd VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( Region_id );

The following recursive view example navigates through the parent child table, defining the levels of the Organization hierarchy:

create  RECURSIVE
  view
Sales_Org_VW
 (Region_id, Region_Name, Parent_Region_id, root.Country_CD, level ) AS
  (SELECT root.Region_id,root.region_Name, Parent_Region_id, Country_CD, 0 as level
    FROM Tdat_Sales_Org root
  WHERE root.Parent_Region_id = 'ALL'
UNION ALL
   SELECT indirect.Region_id,indirect.Region_Name, indirect.Parent_Region_id, indirect.Country_Cd, direct.level + 1
    FROM  Sales_Org_VW direct, Tdat_Sales_Org indirect
   WHERE direct.Region_id=indirect.Parent_Region_id
);

Once a recursive view is created, you can use CREATE TABLE as SELECT from view to add tables at each level of the hierarchy.

Since there are four possible levels, each level represented in the view can be used to create an individual table. To flatten all four levels, add one table for each level as follows:

  1. Run the following statement to determine the number of levels in the hierarchy:

    select max(level) from Sales_Org_vw;

  2. Execute a Create table DDL for each level of the hierarchy, creating a snowflake representation of the hierarchy:
    Create table TAA_Sales_Center (Sales_Center_ID, Sales_Center_Name,Area_ID, Country_Cd)
    as
    (Select Region_ID,Region_Name, Parent_Region_ID, Country_Cd
    from Sales_Org_VW
    where LEVEL = 4)
    with data
    Unique Primary Index (Sales_Center_ID);
    
    Create table TAA_Areas (Area_ID, Area_Name, Division_ID, Country_Cd)
    as
    (Select Region_ID,Region_Name, Parent_Region_ID, Country_Cd
    from Sales_Org_VW
    where LEVEL = 3)
    with data
    Unique Primary Index (Area_ID);
    
    Create table TAA_Divisions (Division_ID, Division_Name, Business_Unit_ID, Country_Cd)
    as
    (Select Region_ID,Region_Name, Parent_Region_ID, Country_Cd
    from Sales_Org_VW
    where LEVEL = 2)
    with data
    Unique Primary Index (Division_ID);
    
    Create table TAA_Business_Units (Business_Unit_ID, Business_Unit_Name, Top_ID, Country_Cd)
    as
    (Select Region_ID,Region_Name, Parent_Region_ID, Country_Cd
    from Sales_Org_VW
    where LEVEL = 1)
    with data
    Unique Primary Index (Business_Unit_ID);
  3. To even-out the ragged hierarchy, add rows to the appropriate table to enable the three-level European Hierarchies to match the four-level U.S. data. This can be done by adding rows to the 4th level.

    In the TAA model, a European region code only goes down three levels to Area_id. For example, if an Area_id for a European region code is 09000015 in the TAA_Areas table, in order to join the transaction table, add rows into the TAA_Sales_Center table which have a 09000015 value for the Area_id column. This enables data from ragged hierarchies to join to your facts.