KB483240: Incorrect aggregated metric value displayed after removing all attributes

Community Admin

• Strategy

Incorrect data is is displayed for an Aggregate From Base level metric that aggregates the difference between two attributes when it is placed on a visualization without the attribute that its level is defined on.

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

Symptoms

Use of an Aggregate From Base level metric where the value being aggregated is the difference between two attributes, say A1 and A2, and level is defined by a third attribute A3.

Two rows have the same value for the A1 attribute, and also for the A2 attribute, but a different value for a third attribute A3. This column makes the rows unique.

Incorrect data when the attribute is alone on a visualization or without the attribute its level is defined on (A3)

Glossary

Aggregate From Base (AFB) 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
Level metric - metrics that are evaluated at a set level of data, regardless of what is contained on the dossier they are placed in

Steps to Reproduce

We will use the mock data below. Order Number is A3, Date Shipped and Date Received are A1 and A2. Note how the first two rows have the same values for Date Shipped and Date Received.

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/19


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


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


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


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

Open Strategy Web.
Create a new dossier.
Add new data using Clipboard.

Copy the grid above the paste it into the editor. Make sure the column headers are in the header row.
Prepare Data.

Convert Order Number to an attribute.
Finish.

Create a new metric Days to Receive Order with the following definition:

Create a Grid visualization with Order Number, Date Shipped, and Date Received on the "Rows", and Days to Receive Order on the "Columns".

Right-click the Days to Received Order column on the Grid, and select "Show Totals".
Note that the grand total at the bottom is 21.

Duplicate the grid and remove all of the attributes in the "Rows".

The value of Days to Receive Order shows 17 instead of 21. This is an incorrect sum.

Cause

This is a known defect in Strategy 10.11. When the metric is alone on a visualization, it displays the aggregated value of all of its rows, so in this case where the value is incorrect, it indicates that the sum was calculated incorrectly.

Data Engine calculates metrics based on what's also on the visualization (or is not), so even when the metric is defined at a level, if those attributes aren't on the visualization as well, then the metric will be calculated at the overall level.
The incorrect sum is because Data Engine only considers distinct combinations, so because there are two rows with the same data for the two columns, it only counts their difference once.

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, Data Engine does not let the visualization interfere with the level at which metrics are calculated at. In other words, the metric will always calculate at the level it is defined at, even when it is alone in a visualization. Furthermore, aggregated metrics do not disregard duplicate combinations of data when leveled.

Note:
1) If after the upgrade and Data Engine Version change, the issue still exists, it is mostly likely that the derived metric has "Use Lookup For Attributes" set to True. When it is set to True, the Engine will treat the duplicate attribute tuples as same element in the Lookup table, and when aggregating it, only counts once. So the issue may persist.

2) In Strategy 2019 with Data Engine set to 10, user may observe null in similar metric. It is due to some a change on datetime datatype that was put only in Data Engine version 11 to avoid regression. To fix the issue, set Data Engine Version setting to 11.
Strategy Internal Reference number for this technical note is KB483240 and DE86726.

Comment

0 comments

Details

Knowledge Article

Published:

May 1, 2019

Last Updated:

March 21, 2024

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