There are various kinds of hierarchies within the dimensions: regular hierarchies, parent-child hierarchies, drill path hierarchies, and attribute hierarchies. Unlike attribute hierarchies, which have a "flat" structure, the other hierarchies have multiple levels, enabling you to drill into the data in the visualization.
In MS OLAP, Tabular, SAP BW or HANA models, regular hierarchies reflect the hierarchical structures configured in the cube. In SQL models, on the other hand, regular hierarchies are built during the ETL in the Model component; they are configured by arranging multiple flat attribute hierarchies in such a way as to facilitate the desired drill path.
Regular hierarchies are denoted by a pyramid icon in the Dimensions panel: . Each level of the hierarchy is designated with an icon indicating its position in the hierarchy: .
Regular vs Drill-Path
While regular and drill path hierarchies are structurally the same, they differ in terms of their behavior in grid visualizations. Firstly, regular hierarchies display expand and collapse buttons in the grid, enabling the user to simultaneously show or hide multiple levels of the hierarchy for specified member elements. Secondly, when utilizing drill functionality, the drill path will be reflected in the Elements panel.
There are a few ways to add a regular hierarchy to the query:
- Add the hierarchy label to add the top level of the hierarchy.
- Add any level of the hierarchy to the query to add the given level.
- Browse the elements tree for a specified level of the hierarchy and select whichever elements you want to add specific elements from the chosen level.
Regular Hierarchy Behavior
Expand and Collapse
When a regular hierarchy is added to a grid, it exposes expand and collapse icons in the visual. In the example below, the first level of the Products hierarchy was added to the query. The Clothing element was expanded (green arrow) exposing its children elements. The Caps (blue arrow) and Shorts (yellow arrow) elements were then also expanded, exposing their child elements.
In this example, Drill Down is selected from the Accessories element.
The visualization now displays only the children of the Accessories element:
Unlike drill path hierarchies, when a regular hierarchy is added to the query, its blue chip reflects the hierarchy name and displays the full hierarchy icon: . When drilling into the query, the chip does not reflect the changes made. However, if you open the hierarchy's Elements tree, the drill path will be reflected there.
In the example below, the Products hierarchy was added to the query; its chip is labeled according to the hierarchy name, rather than the current level of the hierarchy (red arrow). From the hierarchy's Elements tree (green highlight), we can see that the drill path is Bikes > Mountain Bikes > Children of Mountain Bikes.