Add the Parent-Child Relationship Table to the Semantic Model

For measures in fact tables that are aggregated by rolling up the facts from lower-level members, you must edit physical layer joins to include the parent-child relationship table.

You need to add the parent-child relationship table to the appropriate logical table source.

For fact tables containing pre-aggregated data for a parent-child hierarchy or for individual contribution measures, you should join the parent-child dimension table directly with the fact table rather than joining through the parent-child relationship table.

Joining the parent-child dimension table directly with the fact table ensures that the pre-aggregated value or individual contribution value is returned, rather than rolling up all the descendants. When pre-aggregated data is populated for all members, don't add the parent-child relationship table to the logical table source to avoid over counting.

1. On your home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer . Right-click a physical table, select Show Physical Diagram, and select Selected Tables Only and Direct Joins.
4. Right-click each direct join from the dimension table to each of the fact tables and select Delete Join.
5. Click Logical layer and locate and double-click the logical table source for the logical fact table that's used in your parent-child hierarchy.
6. In the Logical Table's tabs, click Joins and create a join from the parent-child relationship table to the dimension table using the ancestor key.
7. Create joins from the fact tables to the parent-child relationship table using the member key.
8. In the Logical Table's tabs, click Sources to edit the logical table source for the logical fact table that's used in your parent-child hierarchy.
9. In the Sources tab, click Add Physical Table.
10. Locate the parent-child relationship table in the Physical layer, and then click Select.
11. Click Save.