3373548 - Level selection in SAP Analytics Cloud stories has incorrect number of levels for a parent-child hierarchy

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.

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

Product

SAP Analytics Cloud 1.0