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:
- Run the following statement to determine the number of levels in the hierarchy:
select max(level) from Sales_Org_vw;
- 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);
- 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.