Home > OLAP Dialog Boxes and Wizards > Create/Edit Cube Dimension:...
![]() Previous |
![]() Next |
Hierarchies identify the parent-child relationships among dimension members. For example, in a Time calendar hierarchy, a year is the parent of four quarters, and a quarter is the parent of three months. Hierarchies are used for aggregating and selecting data.
Hierarchies are usually based on levels (level-based), but may be based just on the parent-child relationships (value-based).
Hierarchies
Lists the defined hierarchies.
Click the Add icon to add a new hierarchy.
Click the Delete icon to delete the selected hierarchy.
Click a value to edit it.
Default: Identifies the default hierarchy for querying cubes with this dimension. This option is selected automatically for the first hierarchy that you create. For a dimension with multiple hierarchies, select the hierarchy that will be used most frequently for analysis.
Name: The object names of hierarchies defined for this dimension. When you add a hierarchy, it appears with a default name, labels, and description.
Short Label: A short descriptive name for display where space is limited, which can contain mixed case, spaces, and symbols.
Long Label: A long descriptive name for display, which can contain mixed case, spaces, and symbols.
Description: Additional descriptive text, which can contain mixed case, spaces, and symbols.
Hierarchy Properties
These properties identify characteristics of the data. If the data does not conform to the specified characteristics, errors may occur when the data is loaded into the cube.
Type: Select the hierarchy type appropriate for the dimension:
Level-Based Hierarchy: Select this property when the dimension has parent-child relationships that define levels, such as months and years, or cities and regions. You cannot change to a value-based hierarchy after creating a level-based hierarchy.
Value-Based Hierarchy: Select this property when parent-child relationships exist, but you cannot group them into meaningful levels. For example, an employee dimension may have parent-child relationships defined in the data that identify each employee's supervisor, but these relationships may not form meaningful levels across the organization.
You can define a value-based hierarchy only when the dimension members are unique in the data source; you cannot define a value-based hierarchy for a dimension that requires the creation of surrogate keys. Level names are required for the creation of surrogate keys in the analytic workspace.
Data Constraints: Select the type of constraint appropriate for your data:
Allow Level-Skipping Data: Allows dimension members to have parents that are more than one level above them, creating a hole in the hierarchy. For example, a City-State-Country hierarchy might have at least one city with a country as its parent (for example, Washington D.C. in the United States). In the relational source tables, a skip-level hierarchy contains nulls in the level columns.
Allow Ragged Data: Allows dimension members to have different bases, creating a "ragged" detail level for the hierarchy. For example, organizational hierarchies are typically ragged because individuals at the detail level (that is, with no one reporting to them) are at different depths from the top level: The administrative assistant to the president is one level from the top, while an administrative assistant to a department head might be eight levels from the top.
Available Levels: Displays a list of all levels defined for the dimension but not selected for this level-based hierarchy. Use the shuttle keys to move selected levels between Available Levels and Selected Levels.
Selected Levels: Displays a list of the levels selected for this level-based hierarchy. This list must be ordered from the highest level of aggregation to the lowest level of aggregation. A hierarchy can have a maximum of 126 levels.
To reorder the list, select a level and use the arrows to move it up or down.
The following is an example of correctly ordered levels for a Calendar hierarchy:
YEAR QUARTER MONTH DAY
Related Topics