KB483253: Aggregate from Base (AFB) conditional metric with components in different datasets displays unexpected value.

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

Symptoms

• Use of an Aggregate From Base conditional metric where the filter attribute or metric is from a different dataset.
  
• Metric being aggregated is a derived metric.

Glossary

• Conditional metric - allows you to apply a filter to only one metric on a report while not affecting the other metrics; independent of report filters
• AFB metric - stands for "Aggregate From Base" metric; uses the attributes on the dataset (the base) when dynamic aggregation is triggered
• Dynamic aggregation - roll-up of metric values that occurs when an attribute is removed from a visualization or document

Steps to Reproduce

• Open Strategy Web with the Tutorial project.
• Create 2 new datasets, and save them in In-Memory mode. Each dataset will contain one table. We use Strategy Tutorial tables where each table has at least one attribute in common with the other table.
  
1. DS1: QTR_CATEGORY_SLS
2. DS2: MNTH_QTR_CATEGORY_SLS
• Create a new dossier, and add the 2 datasets that we just created. (Add Data > Existing Dataset).
• Create a new metric M00=Tot Cost/1000 in DS1_QTR_CATEGORY_SLS (Right-click the three dots next to the dataset name > Create metric...).
  
1. Switch to Formula Editor and enter:
   

   iserver.requestTimeout The XML call's socket default read timeoutConfigDefaults.propertiesdocument.polling.timeoutThe maximum waiting time when polling a job on a server. This only works for a document or dossier instance. When polling the result, you may send multiple requests to the server. This value is the total time of all of these requests. Each single request timeout value is set by iserver.requestTimeout.ConfigDefaults.propertiesisAliveRequestTimeoutMillisThe socket read request timeout value when the Auth Filter sends a GetFolderID XML API request to an Intelligence server nodesys_defaults.xmlisAliveCheckIntervalMillisThe time interval of the check when the Auth Filter sends a GetFolderID XML API request to an Intelligence server nodesys_defaults.xmlBefore fixAfter fix Tables Accessed: STATE_SUBCATEG_MNTH_SLS LU_MONTH SQL Statements: ... select a12.YEAR_ID YEAR_ID, sum(a11.TOT_COST) WJXBFS1 from STATE_SUBCATEG_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) group by a12.YEAR_ID Tables Accessed: LU_QUARTER QTR_CATEGORY_SLS SQL Statements: ... select a12.YEAR_ID YEAR_ID, sum(a11.TOT_COST) WJXBFS1 from QTR_CATEGORY_SLS a11 join LU_QUARTER a12 on (a11.QUARTER_ID = a12.QUARTER_ID) group by a12.YEAR_ID SAP HANA DatatypeStrategy DatatypeAlphanum(n)VarChar(n)BigInt Big IntegerBinaryBinary(1)BooleanBinary(1)Char(n)Char(n)Char Varying(n)VarChar(n)DateDateDayDateDateDecimal(p,s)Decimal(p,s)DoubleDoubleFloat(n)If n>24 Double If n<=24 Real IntegerInteger(10)LongDateTimeStampNchar(n)NChar (n)Numeric(p,s)Decimal(p,s)NVarChar(n)NVarChar(n)RealRealSecondDateTimeStampShortText(n)VarChar(n)SmallIntInteger(5)TextLongVarChar(n)TimeTimeTimeStampTimeStampTinyIntInteger(3)VarBinary(n)VarBin(n)VarChar(n)VarChar(n)Order NumberDate ShippedDate Received11/1/191/5/1921/1/191/5/1931/1/191/14/19Actual ResultExpected Result[Tot Cost]/1000Actual ResultExpected Result

   
   
• Create a new metric M01=Sum(Tot Cost/1000) with Month ID=200801 in DS1_QTR_CATEGORY_SLS.

• By default, the aggregation and subtotal behavior is automatic, so when a filter is applied, aggregate from base will be applied. However, for clarity, we set it manually.
• Drag M01=Sum(Tot Cost/1000) with Month ID=200801 to a Grid visualization.

Tables Accessed:
STATE_SUBCATEG_MNTH_SLS          
LU_MONTH    

SQL Statements:
...
select a12.YEAR_ID  YEAR_ID,
sum(a11.TOT_COST)  WJXBFS1
from STATE_SUBCATEG_MNTH_SLS a11      
join LU_MONTH a12  
on (a11.MONTH_ID = a12.MONTH_ID)
group by a12.YEAR_ID

Tables Accessed:
LU_QUARTER
QTR_CATEGORY_SLS 

SQL Statements:
...
select a12.YEAR_ID  YEAR_ID,
sum(a11.TOT_COST)  WJXBFS1
from QTR_CATEGORY_SLS a11
join LU_QUARTER a12         
on (a11.QUARTER_ID = a12.QUARTER_ID)
group by a12.YEAR_ID

Tables Accessed:
STATE_SUBCATEG_MNTH_SLS          
LU_MONTH    

SQL Statements:
...
select a12.YEAR_ID  YEAR_ID,
sum(a11.TOT_COST)  WJXBFS1
from STATE_SUBCATEG_MNTH_SLS a11      
join LU_MONTH a12  
on (a11.MONTH_ID = a12.MONTH_ID)
group by a12.YEAR_ID

Tables Accessed:
LU_QUARTER
QTR_CATEGORY_SLS 

SQL Statements:
...
select a12.YEAR_ID  YEAR_ID,
sum(a11.TOT_COST)  WJXBFS1
from QTR_CATEGORY_SLS a11
join LU_QUARTER a12         
on (a11.QUARTER_ID = a12.QUARTER_ID)
group by a12.YEAR_ID

Cause

This is a known issue in Strategy 10.11. 

• When calculating an Aggregate From Base conditional metric where the filter attribute or metric comes from a different dataset, Data Engine will sometimes perform double aggregation where it is not necessary.
  
• In the above case, Data Engine divides by 1000 twice in calculation.

Solution

• Upgrade to Strategy 11.0 or above. We recommend the latest version.
• Set Data Engine Version setting to 11.
  
• 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 extra step is removed so that aggregation is performed only once.
 
Strategy Internal Reference number for this technical note is KB483253 and DE95001.