When you model data that is inherently hierarchical, it can be extremely helpful to define dependencies and relationships within data sets.
A parent-child hierarchy (PCH) is a hierarchy in a standard dimension that contains a parent attribute. A parent attribute describes a self-referencing relationship, or self-join, within a dimension main table. In other words, child and parent tables are just normal database tables, but they’re linked in a way that describes a special relationship: a parent-child relationship. This is used most often when one table’s value refers to the value in another table (usually a primary key of another table).
For instance, most profit and loss statements have a native parent-child hierarchy for representing the list of accounts. Also, a list of components of a product, a car for instance, usually is a native parent-child hierarchy because each component has other sub-components, with different levels of depth in different branches of the hierarchy. And, of course, organizational structures of companies, are often represented as parent-child hierarchies, as well.
One of the limitations of a parent-child hierarchies is that each node (item, name, etc.) must have a single parent. Complex organizations, of course, don’t respect this constraint and require more complex graphs. Mapping these graphs to a parent-child hierarchy has to be proceeded by the normalization to a regular organization tree.
When you define a parent-child hierarchy within a parent-child dimension, be aware of the following constraints:
A dimension containing a parent-child hierarchy cannot include any other hierarchies.
The attributes used for the parent key and member key cannot be composite keys.
A parent-child hierarchy member cannot contain multiple parents.
If the imported data source contains hierarchy members with multiple parents, you can use surrogate keys in the data source to overcome this issue.
In SAP Data Warehouse Cloud, you can create parent-child hierarchies within the data. To learn how, click here.