Hierarchies
The Hierarchies feature in AnalyticsCreator defines semantic relationships within a dimension table, allowing you to specify drill-down paths and organizational levels (such as Year → Quarter → Month → Day). These are used primarily for Power BI model generation and cube design.
Function
A hierarchy groups multiple columns of a dimension table in a defined sequence for user-friendly navigation and drill-downs in reports. It reflects how users naturally explore the data.
Access
Hierarchies are managed under the DWH > Hierarchies module. Each hierarchy is tied to a specific table and includes one or more levels defined by column mappings.
Properties
| ID | Property | Description |
|---|---|---|
| 1 | Schema | The schema where the hierarchy's base table is located |
| 2 | Table | The table that contains the hierarchy definition |
| 3 | Hierarchy Name | The name of the hierarchy shown in the semantic model |
| 4 | Description | Optional field to describe the purpose or content of the hierarchy |
| 5 | Column | The physical column used at a specific level of the hierarchy |
| 6 | SeqNr | The position of the column in the hierarchy order (e.g., 1 = Year, 2 = Month) |
| 7 | Name | Friendly name used in the reporting layer (Power BI, etc.) |
| 8 | Level Description | Optional description of each hierarchy level |
| 9 | Clustered | Optional checkbox that flags the hierarchy as part of a clustered structure |
| 10 | Delete | Removes the hierarchy definition from the project |
| 11 | New | Creates a new hierarchy configuration for a given table |
| 12 | Save | Persists changes to the current hierarchy and updates project metadata |
| 13 | Cancel | Cancels editing and returns to the hierarchy list screen |
Screen Overview
The image below shows the List Hierarchies interface with columns labeled for easy identification.
The image below shows the Edit Hierarchy screen used for modifying or adding hierarchy definitions.
Behavior
- Hierarchies define level-based navigation and grouping in semantic models
- Only one clustered hierarchy can exist per table (if used)
- Hierarchies are used during semantic model generation (e.g., for Power BI)
- Ordering is critical and determined by the sequence number
Notes
- Multiple hierarchies can be defined per table
- Hierarchies do not affect SQL code generation but influence semantic layers
- Each hierarchy level should correspond to meaningful, sequential data (e.g., Year → Month → Day)
