About Storing Facts for Parent-Child Hierarchies

You can store facts in the fact table for only the leaf members of the parent-child hierarchy, or for members at any level of the parent-child hierarchy, including non-leaf members.

Storing facts for only the leaf members of the parent-child hierarchy

Use this option when facts for the non-leaf members of the parent-child hierarchy can be derived entirely from the facts of the leaf members. For example, if you've a parent-child product hierarchy where the actual product members appear only as leaf members of the hierarchy, then it makes sense for a revenue fact table to only record revenue facts for the leaf members of this product hierarchy. The revenue figures for the non-leaf members of the product hierarchy such as the product categories can be derived entirely by aggregating the facts for the leaf product members at the bottom of the hierarchy.

The image shows example data for a situation where facts are stored only for leaf members in a parent-child hierarchy.

The following table shows example data for the dimension table PRODUCT_DIM:

MemberKey	Name	ParentKey
P1	Product1	C1
P2	Product2	C1
C1	Category1	C2
C2	Category2	C3
C3	Category3	-

The following table shows example data for the fact table REVENUE_FACTS:

ProductKey	YearKey	Revenue
P1	2020	100,000
P1	2021	105,000
P2	2020	75,000
P2	2021	80,000

Store facts for members at any level of the parent-child hierarchy, including non-leaf members

In this option, facts are stored for members at any level of the parent-child hierarchy. This is necessary when the facts for the non-leaf members aren't completely derived from facts of the leaf members. A good example is a sales person hierarchy where a sales person might report to a manager who is also a sales person. Each individual sales person, including the manager, could have a different revenue figure stored in the fact table.

The following table shows example data for the dimension table SALES_REP_DIM:

MemberKey	Name	ParentKey
101	Phillip	201
102	Vivian	201
201	Jacob	301
202	Audrey	301
301	Ryan	-

The following table shows example data for the fact table REVENUE_FACTS:

SalesRepKey	YearKey	Revenue
101	2021	1,200,000
102	2021	1,100,000
201	2021	250,000
202	2021	1,400,000

Storing facts for both leaf and non-leaf members is also appropriate when the rules for aggregating the parent-child hierarchy are complex, or when aggregating the hierarchy at query time is expensive and would lead to unacceptably long query response times. In this case, the fact table would store preaggregated facts for the non-leaf members in addition to the facts stored for the leaf members.