Filtering Across Models: Dimension Hierarchies
Let’s explore four cases where the common dimension between two models has hierarchies and how we use our existing SAP Analytics Cloud features to leverage the correct filters and optimize performance:
- Case 1: Linking Dimensions with Identical Hierarchical Structures
- Case 2: Linking Dimensions with Different Hierarchical Structures
- Case 3: Linking Level-Based Hierarchies in Acquired Models
- Case 4: Linking Dimensions with Multiple Hierarchies
If your hierarchies have identical structures, make sure that you apply your filter to the selected members only, not their descendants, to optimize performance. For the purpose of this case, allow us to introduce two new data sources: SAC_ORDER_FINANCE_HIERAR and SAC_SHIPPING_INFO_HIERAR.
As you can see below, out of 13 total dimensions, we link the common dimension Store from both models.
The dimension hierarchy is similar in structure for SAC_ORDER_FINANCE_HIERAR and SAC_SHIPPING_INFO_HIERAR models. More specifically, our Store hierarchy is structured by Store and Location for both models, with Store as the descendent of Location. To optimize performance, we have ticked the Exclude descendants option to ensure that the filtering across models takes place for selected hierarchy level members only.
To paint a clearer picture, imagine the following scenario. We have chosen the dimension, Store, for filtering. Furthermore, from the parent Location node, we have selected Italy as the parent node member:
The cross-model filtering will happen for the Italy Location level as opposed to descendent stores within Italy, also to optimize performance.
If the Store hierarchy structures are not identical, we may need to apply a filter to descendants in order to achieve the desired results. For example, let’s assume the following conditions:
- The Store dimension for our SAC_ORDER_FINANCE_HIERAR model is structured hierarchically by Store, Location, and Region, with Location as the parent node of Store and Region as the parent node of Location
- The Store dimension for our SAC_SHIPPING_INFO_HIERAR model is structured hierarchically by Store and Location only – not Region
- The two models are linked using the Store
- We want to filter across models using the Store dimension at the Region level for the Store hierarchy member “Europe”
- Finally, Region exists for our SAC_ORDER_FINANCE_HIERAR model and not for our SAC_SHIPPING_INFO_HIERAR model
With these conditions in mind, we will create a filter using the Store dimension from SAC_ORDER_FINANCE_HIERAR. Since the target model, SAC_SHIPPING_INFO_HIERAR, does not have Region as part of its Store hierarchy, the “Europe” filter will not be applied. As a result, we must untick the Exclude descendants option (meaning we should include descendants for cross-model filtering) for the Store dimension:
If we filter Store from SAC_ORDER_FINANCE_HIERAR with the value “Europe” at the Region level, SAP Analytics Cloud will take all descendants of the region “Europe” – meaning all locations and stores within Europe – and apply the filter on our SAC_SHIPPING_INFO_HIERAR model.
Please note that, as mentioned, the cost of following through with the above steps is a decrease in filtering performance. As a result, we recommend that you tick “Exclude Descendants” whenever hierarchies have similar structure.
For reference, the data sources we used to create our story were acquired models. For acquired data models with level-based hierarchies, we suggest that you link your dimensions based on ID rather than hierarchy. As you can see below, we have linked based on dimension ID, LocationDescription, instead of the LocationDescription hierarchy itself.
To create a filter based on Location across models, we must first link the common. The image above shows three options you can link by: ID, Description, and Location_Ship (which is the name of the LocationDescription dimension hierarchy for our SAC_SHIPPING_INFO model).
In this case, we have chosen ID instead of Description or Location_Ship to fine-tune performance. Recall that selecting Description or the hierarchy itself will increase the overall query time.
Similarly, the image above shows three options for linking within the SAC_ORDER_FINANCE model: ID, Description, and Location_Fin (which is the name of the LocationDescription hierarchy within SAC_ORDER_FINANCE). Once again, we will choose ID for linking.
At this point, when linking dimensions, you may have noticed another option called Link on matching hierarchies. To leverage this option, if there are multiple hierarchies per dimension, rename the hierarchies such that they have the same title and ensure that the Link on matching hierarchies box is ticked.
In our final use case, this option exists for our Store hierarchy:
This option comes into prominence when linked common dimension have multiple hierarchies. To better understand this concept, let’s assume the following conditions exist:
- Our Store dimension has two hierarchies for the SAC_SHIPPING_INFO_HIERAR model called Location and Sales Manager
- The Location hierarchy has a Location-Store parent-child relationship
- The Sales Manager hierarchy has a Sales Manger-Store parent-child relationship, meaning that Sales Manager exclusively covers a group of stores.
- Our Store dimension has one hierarchy for the SAC_ORDER_FINANCE_HIERAR model called Location, which has the same hierarchy structure as for the SAC_SHIPPING_INFO_HIERAR model
The Link on matching hierarchies feature ensures that filtering on the Store dimension happens on the Location hierarchy across models. If we do not tick the Link on matching hierarchies box, it is possible that a link will occur between SAC_ORDER_FINANCE_HIERAR’s Store dimension’s Location hierarchy and SAC_SHIPPING_INFO_HIERAR’s Store dimension’s Sales Manager hierarchy – which would yield incorrect filter results. Please note that this feature is currently available for HANA live and acquired models only.
As you can see, filtering across models for hierarchies can provide you with valuable insights into your data – especially when best practices are applied to ensure the best possible performance.