Pyramid supports parent child hierarchies defined in engines like MS OLAP, SAP HANA and SAP BW as well as those constructed using the internal data modeling inside Pyramid itself. Unlike regular level-based hierarchies, the relationships between each hierarchy level is not defined by level columns. Rather, the hierarchy structure is defined by assigning to each attribute the unique key of its parent. Parent-child structures are therefore very convenient when the levels are not even (i.e. they are ragged); when the relationship between elements is a "nested" or "cork-scerw" type structure; or, when the number of levels changes frequently.
Pyramid natively supports parent-child hierarchies in MS OLAP and SAP BW data models. These parent child hierarchy structures are automatically reflected in the Dimensions panel in Pyramid.
Hierarchy Tree
In the Dimensions panel in Discover, parent child hierarchies are designated by a pyramid icon with a loop back arrow (green arrow), as opposed to the flat 6-pack attribute icon (red arrow) or plain pyramid regular icon (blue arrow).
Expand the hierarchy to expose its levels. You can add any level of the hierarchy to the query, or right click on any level to view its elements.
In the Query
When parent-child hierarchies are added to the query, the expand and collapsefunctions are exposed like regular hierarchies, enabling you to expand specified levels of the hierarchy in the visualization. The Elements panel will reflect the expanded selections in the visual.
Elements
From the Elements panel (green highlight below), you can navigate each level of the hierarchy:
Elements in the Element Tree
From the Elements panel, you can navigate each level of the hierarchy:
Function Selections
Right click on a level or element to display specified descendants and access hierarchical formula or list functions. This allows you to create a logical formulation based on a given hierarchy level, and add the formulation to the query.
Descendants
Right click on any of the upper levels to select the required level or descendants and add them to the query.
- Me and Descendants:display the selected level and all levels under it.
- Descendants:display the descendents of the selected level.
- Leaf Descendants:display leaf descendants only (members that don't have any children).
If changing your Descendants selection, be sure to click the Deselect button first (red arrow below) to ensure that your previous selection is erased.
Include All Level
Your parent child hierarchy may have an "All" root level. If this is the case, you can show or hide the All level via the Elements panel. Open the hierarchy in the Elements panel, and select the "All" level to add it to the query. The "All" level will show the total value of the hierarchy.
Formulas
Create a context calculation that calculates, for the given element or level, the percentage of the total value, or the percentage of its parent's value, for which it accounts.
List
Create a quick list comprised of member elements belonging to the selected hierarchy relationship type for the given hierarchy level. For example, create a list of ascendents for the member element "French Herb".
