Symptom
Hierarchy level selection/drill down menu does not show the expected number of levels when interacting with widgets (charts/tables) in a story. The number of levels may be lower or greater than the actual number of levels.
For the most part this kbase deals with SAP Analytics Cloud acquired data models, but similar principles apply to SAP Datasphere, HANA Live, and HANA Cloud Live models consumed in SAP Analytics Cloud stories.
Environment
- SAP Analytics Cloud (Enterprise) 2023.16
Cause
In general:
- Metadata caches are language dependent. Thus, depending on the data access language (DAL) setting one may get different results.
- The number of levels of a parent-child hierarchy (PCH) is data dependent so the hierarchy level information in the model metadata can get stale if the hierarchy data is changed for some reason after the first time the model metadata is requested in a particular DAL.
Things that can result in a hierarchy data change:
- Loading/changing fact table or dimension tables data - in this case all corresponding data caches are updated, but the metadata caches are NOT touched. Those are only updated if the structure of the underlying views change.
- Hierarchies may be permission dependent - this is unknown to the metadata layer, but has the same effect as a hierarchy data change, and the level information read by the first user to access the model metadata with be cached.
- Hierarchies may be variable dependent (not currently applicable to SAP Analytics Cloud acquired data models) - since variables cannot be set at the time of the metadata request, level information in the metadata response may be based on the wrong data.
For example:
- Model X is created and a dimension A has a parent-child hierarchy PCH1 which was defined with data that represents a 5 level structure.
- The model is consumed in a story by a user whose DAL is set to Japanese so the hierarchy metadata cache is populated with 5 levels for Japanese.
- Some time later, the data for dimension A is updated so that it represents a 6 level structure.
- Since loading/changing fact table or dimension table data does not invalidate hierarchy metadata caches, the entry for Model X under Japanese is not cleared.
- Consuming the model in a story by any user whose DAL is set to Japanese would show only 5 levels, but consuming the model by a user with DAL set to a previously unused language (e.g. English) would show 6 levels.
- Further updates to the data could cause the number of levels for English and other already used DALs to become stale; basically as long as the hierarchy metadata is not reset.
Similar scenarios could be possible for permission and/or variable dependent hierarchies.
Resolution
Workarounds which can help in some situations are:
For loading/changing fact table or dimension tables data any change in the model structure will invalidate the metadata cache including the hierarchy level information.
- For SAP Analytics Cloud acquired data models it is not sufficient to just resave the model - in fact a model cannot be resaved when there are no changes - so one of the quickest options is to change a measure setting like "Decimal Places" or "Scale", but other operations like adding/removing dimensions and/or measures would work equally well.
- Once a change is made the model should be saved to ensure the metadata cache is cleared.
- After the change is saved, it can then be reverted and the model resaved again to ensure that the only thing that is changed is the hierarchy data.
For permission dependent hierarchies where different users are expected to see a different number of levels making changes and resaving the model would never be sufficient to cover all cases.
- For HANA Live models a workaround, with corresponding performance penalties, is to make the metadata cache user-specific. This can be done via the MDS configuration parameter "mds_enable_user_specific_metadata_cache=true". This workaround is currently not available for SAP Analytics Cloud acquired data models.
For variable dependent hierarchies in HANA Live models there is a setting in the SAC Modeller under "Preferences" > "Data and Performance" > "Variable dependent hierarchies".
- When this setting is ON the variable level information will be refetched after each variable submit.
- This functionality is currently only available in Classic Design Experience stories.
Feature enhancements are planned to improve when and how hierarchy level information is fetched to eliminate the need for the above workarounds and handle cases which the workarounds cannot help with.
See Also
- 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
- Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
- 2487011 - What information do I need to provide when opening a case for SAP Analytics Cloud?
- 2511489 - Troubleshooting performance issues in SAP Analytics Cloud
- Search for SAP Analytics Cloud content using SAP for Me, Google or Bing:
- https://me.sap.com/servicessupport/search#?q=SAP%20Analytics%20Cloud%20OR%20SAC&tab=All
- https://www.google.com/search?q=site%3Ahttps%3A%2F%2Fuserapps.support.sap.com+SAP+Analytics+Cloud
- https://www.bing.com/search?q=site%3Ahttps%3A%2F%2Fuserapps.support.sap.com+SAP+Analytics+Cloud
- Note: Add relevant text or warning/error messages to the text search field to filter results.
- SAP Analytics Cloud Connection Guide
- Getting Started with SAP Analytics Cloud Expert Community page
- SAP Analytics Cloud Get More Help and SAP Support
- Need More Help? Contact Support or visit the solution finder today!
Your feedback is important to help us improve our knowledge base.
Keywords
SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, sapanalyticscloud, sap analytical cloud, sap analytical cloud, SAC, sap analyst cloud, connected, failure, stopped, sap analyst cloud, hierarchies, submit, performance , KBA , LOD-ANA-DES , Story Design & Visualizations , Problem