Hierarchy
Hierarchy is a concept used in dimensions to organize data hierarchically. Through hierarchies, users can define hierarchical relationships between dimension members, enabling more flexible querying and presentation in data analysis.
Terminology:
- Hierarchy
- Level
Hierarchies display relationships between groups of columns in a dimension table. For example, quarters contain months, and months contain days. Using hierarchies allows drilling down in reports.
A dimension can have one or more hierarchies. Hierarchies typically start with a total level, followed by child levels, and finally break down to the lowest level of detail.
All hierarchies of a dimension must share a common lowest level. For example, a time dimension may include a fiscal hierarchy and a calendar hierarchy, with day as the common lowest level. The day has two named parents (fiscal year and calendar year), both of which are children of the All root level.
Except for the total level, all levels must have at least one column designated as a key or display column. However, not all columns in the table need to be explicitly associated with a level. Any column not associated with a level will be linked to the lowest level in the hierarchy (corresponding to the dimension table).
There is no limit to the number of levels a hierarchy can contain. The total number of levels is not a definitive factor in query performance. However, note that for extremely complex queries, even a few levels can impact performance.
Creating a Hierarchy
Click the New Button in the hierarchy navigation bar of the dimension to create a hierarchy.
- Open the properties interface to edit hierarchy properties (see below).
- Drag the physical table name from the data table to the dimension table area to add it as a dimension table for the hierarchy.
- One or more dimension tables can be added.
- When multiple dimension tables are added, the dimension table relationship design interface on the right will open automatically. You can also open it manually to select relationships between tables and add associated left and right keys to link dimension tables.
Hierarchy Properties
List of all hierarchy properties:
| Property | English Name | Description | Possible Values |
|---|---|---|---|
| Name | Name | Unique identifier | Typically English characters |
| Caption | Caption | Language description | User language |
| Description | Description | Long text description | User language |
| Visible | Visible | Whether visible to users | |
| Has All Member | Has All Member | Whether an All member exists; if so, the hierarchy includes a default All member level as the first level | |
| All Member Name | All Member Name | Replaces the default All member name | |
| All Member Caption | All Member Caption | Caption for the All member | |
| All Level Name | All Level Name | Name of the All level | |
| Dimension Tables | Dimension Tables | Dimension tables | Array of physical tables |
| Primary Key | Primary Key | Select a dimension table field as the hierarchy's primary key | |
| Primary Key Table | Primary Key Table | Specify a table as the primary table when multiple dimension tables are used | |
| Default Member | Default Member | Used to limit the dimension when no dimension member is specified in a query |
Creating Levels
- Drag fields from the dimension table to the level area in order, from coarse-grained to fine-grained fields. You can also adjust the order in the level area.
- Dragging level fields:
- Adjust the order between levels.
- Drag back to the dimension table area to remove a level.
- Drag to the delete icon to remove it.
- Select a level and open the properties interface to set its properties (see the properties list below).
Level Properties
List of all level properties:
| Property | English Name | Description | Possible Values |
|---|---|---|---|
| Name | Name | Unique identifier | Typically English characters |
| Caption | Caption | Language description | User language |
| Description | Description | Long text description | User language |
| Visible | Visible | Whether visible to users | |
| Unique Members | Unique Members | Optimizes SQL generation. Set to "true" if the level column values are unique within the parent level; otherwise, "false". | |
| Column | Column | The data table field corresponding to the level, used as the level's primary key | |
| Type | Type | Database type of the level field, aiding the engine in generating correct execution statements | |
| Name Column | Name Column | Output ID for level members. If not set, the member ID defaults to the Column value | |
| Caption Column | Caption Column | Output caption for level members. If not set, the caption is taken from Name Column or Column if neither is set | |
| Ordinal Column | Ordinal Column | Field determining the order of level members | |
| Parent Column | Parent Column | When the table records have a parent-child structure, setting this generates a tree-like member structure | |
| Null Parent Value | Null Parent Value | Value determining if a parent member exists. Defaults to null, but some databases require an empty string, 0, or -1. | |
| Table | Table | Table name for the level when the hierarchy is composed of multiple related tables | |
| Level Time Type | Level Time Type | Time granularity for the level when the dimension is a time type | |
| Hide Member If | Hide Member If | Condition for hiding members. If a hierarchy has levels with hidden members, it forms a ragged hierarchy. | 1. Never hide; 2. Hide if name is empty; 3. Hide if name matches parent |
| Semantics | Semantics | Semantics of the dimension level to enhance expressiveness | Semantics Settings |
| Key Expression | Key Expression | Expression | |
| Name Expression | Name Expression | Expression | |
| Caption Expression | Caption Expression | Expression | |
| Ordinal Expression | Ordinal Expression | Expression | |
| Parent Expression | Parent Expression | Expression | |
| Closure | Closure | Closure table to optimize SQL performance for parent-child level relationships | 1. Table; 2. Parent field; 3. Child field |
| Property | Property | List of level properties | 1. Property name; 2. Property field; 3. Property expression |
Ordinal Column
The Ordinal Column is a field used to sort level members, e.g., a month’s ordinal column could be the month number.
The Ordinal Column attribute specifies a column in the hierarchy table that provides the order of the members in a given level, while the Name Column specifies a column that will be displayed.
Expression
SQL expressions can be used instead of field settings to provide richer computational functionality. For example, the caption field for the Month level can use the following expression to concatenate and display the year and month:
CONCAT("the_year", ' ', "the_month")
Note that SQL expression syntax must match the syntax of the database being used.
Closure Table
When a level has a parent field, the level members form a parent-child relationship. Calculating aggregate values for hierarchies with indeterminate depth can pose performance challenges. A closure table can be specified for parent-child relationship tables to improve the performance of aggregate calculations.
A closure table is an SQL table that, for example, in an employee closure table, contains records of every employee/supervisor relationship (including all higher-level supervisors, regardless of depth). In mathematical terms, this is called the "reflexive transitive closure" of the employee/supervisor relationship. A distance column is not strictly required but makes populating the table easier.
| employee_closure | ||
|---|---|---|
| supervisor_id | employee_id | distance |
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 1 | 5 | 3 |
| 1 | 6 | 2 |
| 1 | 4 | 1 |
| 2 | 2 | 0 |
| 2 | 3 | 1 |
| 2 | 5 | 2 |
| 2 | 6 | 1 |
| 3 | 3 | 0 |
| 3 | 5 | 1 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 6 | 0 |
The property settings are shown below:
Hide Member
Dimensions typically consist of multiple levels, such as Region > Province > City or Company > Department > Employee. Hide Rules determine whether members at a certain level should be hidden in the analysis view, commonly used in scenarios like organizational structures, geographic regions, or product classifications with "empty," "skipped," or "redundant" names.
🎯 Functionality
Member hiding rules support the following logic to automatically determine whether a level’s members should be omitted from the view:
| Rule Type | Description |
|---|---|
| Never Hide | All members are always displayed in the analysis results. Suitable for complete, consistent data structures. |
| Hide if Name is Empty | Members are not displayed if their name is empty, an empty string, or contains only spaces. Suitable for cases where some nodes are unnamed or meaningless. |
| Hide if Name Matches Parent | Members are not displayed if their name matches their parent’s name. Commonly used to remove redundant naming, such as "Head Office > Head Office" structures. |
📚 Application Examples
Organizational Structure Analysis
Some employees report directly to headquarters without an intermediate department. If the department name is empty, the department level can be hidden to avoid displaying blank nodes.Product Classification Display
Records where a product subclass has the same name as its parent class are automatically hidden, making the tree structure clearer and avoiding redundant information.Regional Level Adjustment
If some regions lack a provincial level and only include country and city, this rule can skip the provincial level to maintain analysis consistency.
✅ Usage Recommendations
- When levels may contain null, empty strings, or spaces, enable Hide if Name is Empty to skip invalid nodes and avoid displaying blank or meaningless members in analysis results.
- When a level’s member names often match their parent’s, enable Hide if Name Matches Parent to remove redundant displays and enhance hierarchy clarity.
- If the dimension structure is complete and data is standardized, keep the default Never Hide to ensure all members are displayed.