KB483532: Incorrect values for subtotal from base metrics with no facts

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

Symptoms

• Incorrect data returned by a Subtotal From Base metric
  
• Metric is defined as Count() of an attribute with "Include Distinct Elements" set to True (<Distinct=True> in the formula)
• Relationships in the dataset used are auto-managed.
  
• By default, datasets from MTDI Cubes are not auto-managed. This can be changed when defining the relationships for a table in a dataset.

Glossary

• Subtotal from Base metric - a metric with "Subtotals From Base" enabled
• Defining Attribute Relationships

Steps to Reproduce

• Open Strategy Web.
• Create a new Intelligent Cube. We use Strategy Tutorial objects:
  
• Add External Data > Databases > Select Tables:

• Prepare Data.
  
• Convert Month of Year to an attribute if necessary.
• Add a relationship in "LU_MONTH" between Month Id and Month Date. Make sure that it is auto-managed (the left icon shows as below): 

• Save the cube "In-Memory" mode.
• Create a dossier using the cube made in step 2.
  
• Create a new metric M01=Count(Month Id) with the following definition: 

• Add Quarter Desc, Cust State Id, Region Id, Month Date, and M01=Count(Month Id)  to a new Grid visualization.
  
• Show Totals.
• By default, the Aggregation and Subtotal Behavior for M01=Count(Month Id) was set to automatic when we created it. When we add the metric to the grid and show totals, "Subtotals From Base" will be enabled automatically. To check this, you can edit the metric, and in "Metric Options", change the behavior to "Manual" to see that "Subtotals From Base" is indeed enabled.

Below are the results. Note how the subtotal is incorrect as it should be 48, not 36. You can confirm this by adding Month Date and Month Id to a Grid visualization and viewing the total for that Grid.

Cause

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

• Data Engine logic does not check if the temporary table that is created contains the current fact and uses it in the join path regardless. This results int the wrong CSI being generated and from that, incorrect data.

In the example above, the temporary table TempTable 37 is used:
 
CSI before fix

...
select count(distinct [Month Id]@[MONTH_ID]) as [M01=Count(Month Id) Subtotal {GrandTotal}]
from C01
with Table Join Tree: TempTable37
Join [REL_MONTH_ID_MONTH_DATE] with output level Tuple([Month Id]@[MONTH_ID], [Month Date]@[MONTH_DATE], [Cust State Id]@[CUST_STATE_ID], [Region Id]@[REGION_ID], [Quarter Desc]@[QUARTER_DESC])
Save As TempTable38

select [Quarter Desc]@[QUARTER_DESC], count(distinct [Month Id]@[MONTH_ID])@{[Quarter Desc]} as [M01=Count(Month Id) Subtotal {[Quarter Desc]}]
from C01
with Table Join Tree: TempTable37
Join [REL_MONTH_ID_MONTH_DATE] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Desc]@[QUARTER_DESC])
Save As TempTable39

select [Cust State Id]@[CUST_STATE_ID], [Quarter Desc]@[QUARTER_DESC], count(distinct [Month Id]@[MONTH_ID])@{[Cust State Id],[Quarter Desc]} as [M01=Count(Month Id) Subtotal {[Cust State Id],[Quarter Desc]}]
from C01
with Table Join Tree: TempTable37
Join [REL_MONTH_ID_MONTH_DATE] with output level Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID], [Quarter Desc]@[QUARTER_DESC])
Save As TempTable40
...

Solution

• Upgrade to Strategy 2019 Update 1 or later. We recommend the latest version.
• Set Data Engine Version setting to 11 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.

In the new fix, the temporary table is not picked as the best table to use in the join path if it does not contain the current fact. The new CSI for the example above is as follows and uses a fact table instead:
 
CSI after fix

...
select count(distinct [Month Id]@[MONTH_ID]) as [M01=Count(Month Id) Subtotal {GrandTotal}]
from C01
with Table Join Tree: [F_LU_MONTHE7F7E91416C72555E929018956316789]
Save As TempTable38

select [Quarter Desc]@[QUARTER_DESC], count(distinct [Month Id]@[MONTH_ID])@{[Quarter Desc]} as [M01=Count(Month Id) Subtotal {[Quarter Desc]}]
from C01
with Table Join Tree: [F_LU_MONTHE7F7E91416C72555E929018956316789]
Join [F_LU_QUARTERE7F7E91416C72555E921272456447463] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Desc]@[QUARTER_DESC])
Save As TempTable39

select [Cust State Id]@[CUST_STATE_ID], [Quarter Desc]@[QUARTER_DESC], count(distinct [Month Id]@[MONTH_ID])@{[Cust State Id],[Quarter Desc]} as [M01=Count(Month Id) Subtotal {[Cust State Id],[Quarter Desc]}]
from C01
with Table Join Tree: [F_STATE_REGION_MNTH_SLSE7F7E91416C72555E921635356291223]
Join ([F_LU_MONTHE7F7E91416C72555E929018956316789]
Join [F_LU_QUARTERE7F7E91416C72555E921272456447463] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Desc]@[QUARTER_DESC])) with output level Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID], [Quarter Desc]@[QUARTER_DESC])
Save As TempTable40
...

Strategy Internal Reference number for this technical note is KB483532 and DE124440.