Parent-Child Dimensions

Parent-Child Dimensions

A parent-child dimension is based on two dimension table columns that together define the lineage relationships among the members of the dimension. One column, called the member key column, identifies each member; the other column, called the parent key column, identifies the parent of each member. This information is used to create parent-child links, which are then combined into a single member hierarchy that represents a single meta data level.

For example, in the following Employee table, the column that identifies each member is Employee_Number. The column that identifies the parent of each member is Manager_Employee_Number. (This column stores the employee number of each employee's manager.)

These columns can be used to define a parent-child dimension that contains the following member hierarchy. (The hierarchy mirrors an organization chart of the employees in the Employee table.)

Both columns must have the same data type. Both columns must be in the same table.

Note  By default, any member whose parent key equals its own member key, null, 0 (zero), or a value absent from the column for member keys is assumed to be a member of the top level (excluding the (All) level).

By default, a top-level member (ignoring the (All) level) is identified by its parent key, which equals its own member key, null, 0 (zero), or a value not contained in the column for member keys. For example, in the preceding illustration, the only top-level member is Paul West. Both the Manager_Employee_Number value and Employee_Number value for Paul West are 3. These values are equal because Paul West is specified as the manager of Paul West. By default, Paul West would also be a top-level member if the Manager_Employee_Number value for Paul West were null, 0 (zero), or a value not contained in the Employee_Number column.

You can set the criteria for identifying top-level members by using the level's Root Member If property in the properties pane of Dimension Editor (if the dimension is shared), or Cube Editor (if the dimension is private).

When you define a parent-child dimension, you can also select a third column to provide member names, which are displayed to end users as they browse cubes. This third column, the member name column, defaults to the member key column. If you want to display an alternate value, a different column can be chosen. In the preceding illustration, the employee names would be displayed only if the member name column was set to:

"Employee"."Employee_Name"

The depth of a parent-child dimension can vary among its hierarchy's branches. For example, in the preceding illustration, the James Smith branch has lower-level members, but the Amy Jones and Jill Kelley branches do not. Therefore, the hierarchies of parent-child dimensions are usually unbalanced.
Unlike regular and virtual dimensions, which are defined with a number of levels that determines the number of levels seen by end users, a parent-child dimension is defined with a single level of a special type that usually produces multiple levels seen by end users. The number of displayed levels depends on the contents of the columns that store the member keys and the parent keys. This number can change when the dimension table is updated and the cubes using the dimension are subsequently processed.
You can use the Dimension Wizard to create parent-child dimensions. You cannot use Dimension Editor. You can use Cube Editor to create parent-child dimensions only if you start the Dimension Wizard from within Cube Editor. In the second step of the Dimension Wizard, select Parent-Child Dimension: Two related columns in a single dimension table.
After you create a parent-child dimension, you can edit it in Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private). In Dimension Editor or Cube Editor, you can access the Level Naming Template dialog box, in which you can specify the level names to be displayed to end users.
A parent-child dimension's table is graphically represented with a join between the column containing the members' keys and the column containing the keys of the members' parents. This join is visible in the Dimension Wizard and the Schema tab of Dimension Editor and Cube Editor.
Important  If a parent-child dimension is included in a cube with a fact table that has rows associated with the dimension's nonleaf members, you must set the dimension's Members With Data property to Nonleaf data visible or Nonleaf data hidden. Otherwise, processing the cube fails.

The Members With Data property indicates whether nonleaf members of a parent-child dimension are allowed to have associated fact table data. By default, nonleaf members are not allowed to have associated fact table data, so the property is initially set to Leaf members only. The related Data Member Caption Template property controls the names of data members when the Members With Data property is set to Nonleaf data visible. For more information about these properties, see Properties Pane (Cube Editor Data View) and Properties Pane (Dimension Editor Data View).
Parent-child dimensions are the only kind of dimension that you can write-enable. All parent-child dimensions are also changing dimensions. That is, their Changing property is set to True. You cannot change this value.
The storage mode of a parent-child dimension is always multidimensional OLAP (MOLAP).
See Also

Changing Dimensions
Dimension Wizard
Write-Enabled Dimensions