KB483580: Aggregate From Base metric made with the Case function returns incorrect data in a In-Memory based Dossier

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptoms

• Visualization in a Dossier returns incorrect data for an Aggregate From Base metric made using the Case() function.
  
• Data comes from an In-Memory Intelligent Cube.
• Template contains a Derived Attribute.
• Dossier contains another dataset that is not used in the visualization.

Glossary

• Aggregate From Base (AFB) metric
• Derived Attribute
• Case function

Steps to Reproduce

• Open Strategy Web. We use Strategy Tutorial objects in the following steps.
• Create a new Intelligent Cube C01 using the "ORDER_FACT" table:
  
1. Add External Data > Databases > Select Tables.
2. Select the "ORDER_FACT" table.
3. Save as an In-Memory dataset.
• Create a new dossier:
  
• Create a new metric M01=Case: 

• Create a new metric M02=Sum(Case): 

• Create a derived attribute Ship Date (DA): 

• Drag Ship Date (DA) and M02 to a Grid Visualization. Note the values that are returned by M02=Sum(Case): 

• Add a second dataset to the dossier. The structure of the second dataset does not matter so much; we add the "CITY_MNTH_SLS" table. Note how adding an unrelated dataset unexpectedly changes the data returned by M02=Sum(Case): 

Cause

This is a known issue in Strategy 2019 and previous versions.

• When more than one dataset is used in a dossier, data execution passes through a different workflow. The logic there could not calculate the Aggregate From Base metric at the correct level when there is a Derived Attribute on the template. 
  
• Hence why, a grand total value is returned.

Solution

• Upgrade to Strategy 2020 or above. We recommend the latest version.
• Set Data Engine Version setting to 12 or above.
  
• The project level setting Data Engine Version can only be enabled through Workstation. For more details on enabling the setting, see Change Data Engine Version.
• After changing the Data Engine Version setting, you will need to republish your datasets to see the changes.

In the new fix, Data Engine fixes the logic for calculating Aggregate From Base metrics made with the Case function when there is more than one dataset on the dossier. Now, the metric will be calculated at the correct level and will return the according values.
Strategy Internal Reference Number for this technical note is KB483580 and DE138817.