About Parent-Child Relationship Tables

In a relational table, the relationships between different members in a parent-child hierarchy are implicitly defined by the identifier key values in the associated base table.

For each parent-child hierarchy defined in a relational table, you must also explicitly define the inter-member relationships in a separate parent-child relationship table.

The parent-child relationship table must include four columns:

• A column that identifies the member

• A column that identifies an ancestor of the member

  An ancestor is the parent of the member, or a higher-level ancestor.

• A relationship distance column that specifies the number of parent-child hierarchical levels from the member to the ancestor

• A leaf node column that indicates if the member is a leaf member (1=Yes, 0=No)

The column names can be user-defined. The data types of the columns must satisfy the following conditions:

• The member and ancestor identifier columns have the same data type as the associated columns in the logical table that contains the hierarchy members.

• The distance and leaf columns are INTEGER columns.

For the rows in a parent-child relationship table:

• Each member must have a row pointing at itself, with distance zero.

• Each member must have a row pointing at each of its ancestors. For a root member, this is a termination row with null for the parent and distance values.

The example shown in the table uses text strings for readability, but you normally use integer surrogate keys for member_key and ancestor_key, if they exist in the source dimension table.

The table shows an example of a parent-child relationship table with rows that represent the inter-member relationships for the employees. See the figure in About Parent-Child Hierarchies.

Member_Key	Ancestor_Key	Distance	Isleaf
Andrew	Andrew	0	0
Barbara	Barbara	0	0
Carlos	Carlos	0	0
Dawn	Dawn	0	0
Emre	Emre	0	0
Andrew	null	null	0
Barbara	Andrew	1	0
Carlos	Andrew	1	1
Dawn	Barbara	1	1
Dawn	Andrew	2	1
Emre	Barbara	1	1
Emre	Andrew	2	1

You must generate the parent-child relationship table and then import it into the physical layer before associating it with the parent-child hierarchy. You use the Hierarchy Tab's Generate Relationship Table functionality to generate scripts that are run to create and populate the parent-child relationship table.

When you generate the relationship table, two scripts are created: one script to create the table, and the other script to load the table. Note the following information about the create and load scripts:

• You run the create script only once, to create the parent-child relationship table in the data source.

• You must run the load script after each time the data changes in the dimension table. Because of this, you typically call the load script in your ETL processing. The load script reloads the entire parent-child relationship table; it isn't incremental.

For information about generating the relationship table scripts, see Generate Scripts to Create a Parent-Child Relationship Table.