Define parent-child hierarchies

Parent-child hierarchies are hierarchies in the standard dimension that contain parent attributes. The Parent property is used to describe a "self-referential relationship" or "self-join" inside a dimension's main table. Parent-child hierarchies are constructed from a single parent property. The level that appears in the hierarchy is formed by a parent-child relationship between the members associated with the parent property, so only one level is assigned to a parent-child hierarchy. The position of a member within a parent-child hierarchy is determined by the KeyColumns and RootMemberIf properties of the parent attribute , and the position of the member within the level is determined by the parent attribute's by attribute. For more information about attribute properties, see properties and attribute hierarchies.

Because parent-child relationships exist between levels in a parent-child hierarchy, some non-leaf members can contain data derived from the underlying data source in addition to the data that is aggregated from the child members.

Dimension Schema

The dimension schema of a parent-child hierarchy relies on the self-referential relationships provided in the main table of the dimension. For example, the following diagram illustrates the dimorganization Dimension Master table in the AdventureWorksDW sample database.

In the dimension table, theparentorganizationkey column has a foreign key relationship with the Organizationkey primary key column. In other words, each record in the table can be associated with other records in the table through a parent-child relationship. This self-join is typically used to represent entity data for a unit, such as an employee management structure within a department.

hierarchies and Levels

A dimension that does not have a parent-child relationship constructs a hierarchy by grouping by property and by property by sort. These dimensions derive the level name of their hierarchy from the attribute name.

However, a parent-child dimension constructs a parent-child hierarchy by examining the data contained in the Dimension master table and then evaluating the parent-child relationship between the records in the table. For more information about parent-child hierarchies, see user hierarchies.

The parent-child hierarchy does not derive the names of the levels in the parent-child hierarchy from the properties used to create the hierarchy. By using a named template, a string expression that can be specified at the parent property level that controls how the property generates the attribute hierarchy, these dimensions automatically create a level name. For more information about how to set up a named template for a parent property, see Properties and attribute hierarchies.

data members

In general, leaf members in a dimension contain data that derives directly from the underlying data source, rather than leaf members that contain data derived from the aggregations performed by the child member.

However, in a parent-child hierarchy, some non-leaf members may contain data derived from the underlying data source, in addition to data that is based on the aggregation of child members. For these non-leaf members in a parent-child hierarchy, you can create special system-generation child members that contain the underlying fact table data. These special child members are called data members, and they contain a value that is directly related to a non-leaf member and is independent of the sum calculated by the descendants of that non-leaf member. For more information about data members, see Using properties in parent-child hierarchies.

Define parent-child hierarchies