KB484917: Disable double aggregation for metrics defined like M2 = Sum(M1) and M1 = Sum(Fact)

Quality Engineer, Principal • MicroStrategy

This article explains a scenario that disable double aggregation for specific metrics to fix data issue with Data Engine Version 2019.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Description
In order to improve performance, we enable double aggregation for metric which contains only one aggregation function and the function must be one of the followings: Sum, Max, Min. Refer to Appendix II session in Here for detailed information. This improvement causes data issue for specific Metric like M2 = M1/Sum(M1) and M1 = Sum(Fact), since it cannot be double aggregated due to one of M2's component metrics has no aggregation function in Strategy 2019 Update 3 with Data Engine Version 2019.
Disable double aggregation for specific metrics to fix the data issue which is made with Data Engine Version 2019. After the fix, the data is the same as prior to Strategy 2019 Update 3.
Steps to Reproduce
This procedure is using Strategy Tutorial WH that is shipped with Strategy products.

Click Finish to Import as an In-memory Dataset and save as MTDI01

Go to Add External Data -> Databases -> Pick Tables and pick the following table and columns

Click Finish to Import as an In-memory Dataset and save as MTDI02
Create Dossier with MTDI01 and MTDI02 as datasets, link Attribute Month Id in the two datasets

Create Derived Metric DM1 in dataset MTDI01 with expression: Sum([Tot Cost]){~+}
Create Derived Metric DM2 in dataset MTDI02 and change the Number Format as Percentage

Add into Visualization: Attribute Year Id and Metric DM1, DM2. The data is incorrect for both DM1 and DM2.

View Query Details, it fetches the aggregated data, but it’s incorrectly calculated to Grand Total.

************   Individual Step Start   ************
Number of Rows Returned: 24
Time Spent: 0.005 sec(s)
Query Execution Start Time: 3/19/2021 10:12:08 AM
Query Execution End Time: 3/19/2021 10:12:08 AM
Tables Accessed:
Table3   [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]:          Region Id, Month Id, Cust State Id,                 Tot Cost, Row Count - STATE_REGION_MNTH_SLS, Tot Dollar Sales, Tot Unit Sales,           FACT_TABLE
select    [Month Id]@[MONTH_ID],
                sum([[F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3].Tot Cost]) as [DM1]
from       MTDI01: STATE_REGION_MNTH_SLS
with Table Join Tree:         [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]
Save As TempTable4
select    [Month Id]@[MONTH_ID],
                sum([TempTable4.DM1])@{[Month Id]} as [Tot Cost]
from       MTDI01: STATE_REGION_MNTH_SLS
with Table Join Tree:         TempTable4
to            Ex5_tempcube0
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.002 sec(s)
Query Execution Start Time: 3/19/2021 10:12:08 AM
Query Execution End Time: 3/19/2021 10:12:08 AM
Tables Accessed:
Table9   [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]:             Year Id, Month Duration, Quarter Id, Month Id, Month Date, Month Desc, Prev Month Id, Lq Month Id, Ly Month Id,              Row Count - LU_MONTH, Month Of Year,                 FACT_TABLE
select    ([Month Id]@[MONTH_ID]) as ([Month Id]@[MONTH_ID]),
                [Year Id]@[YEAR_ID]
from       MTDI02: LU_MONTH
with Table Join Tree:         [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]
to            Ex5_tempcube1
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 24
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/19/2021 10:12:08 AM
Query Execution End Time: 3/19/2021 10:12:08 AM
select    *
from       Left Outer Join(
                                Ex5_tempcube0,
                                Ex5_tempcube1
                               ) on Month Id
to            Ex5_tempcube2
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.002 sec(s)
Query Execution Start Time: 3/19/2021 10:12:08 AM
Query Execution End Time: 3/19/2021 10:12:08 AM
select    [Year Id]@[YEAR_ID],
                sum([Tot Cost])@{[Year Id]} as [DM1],
                sum(sum([Tot Cost])) as [Embedded metric:D13E30EB49069A9A386F70A263646B57]
from       Ex5_tempcube2
************   Individual Step End     ************
Cause:
This is a known issue starting from Strategy 2019 Update 3 with Data Engine Version 2019 or above.
Solution:
This issue has been addressed in Strategy 2021 Update 1.
To pick up the fix, upgrade to Strategy 2021 Update 1 or above and enable project level setting "Data Engine Version" to 2019 or above. For more details on enabling the setting, refer to Data Engine Version.
After the fix, the data is the same as prior to Strategy 2019 Update 3.

Check Query Details, it picks raw data to calculate the embedded metric defined in DM2: Sum(DM1){}.
************   Individual Step Start   ************
Number of Rows Returned: 8939
Time Spent: 0.146 sec(s)
Query Execution Start Time: 3/19/2021 10:15:32 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
Tables Accessed:
Table3   [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]:          Region Id, Month Id, Cust State Id,                 Tot Cost, Row Count - STATE_REGION_MNTH_SLS, Tot Dollar Sales, Tot Unit Sales,           FACT_TABLE
select    [Region Id]@[REGION_ID],
                [Cust State Id]@[CUST_STATE_ID],
                [Month Id]@[MONTH_ID],
                [[F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3].Tot Cost] as [Tot Cost]
from       MTDI01: STATE_REGION_MNTH_SLS
with Table Join Tree:         [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]
to            Ex1_tempcube0
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.020 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
Tables Accessed:
Table9   [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]:             Year Id, Month Duration, Quarter Id, Month Id, Month Date, Month Desc, Prev Month Id, Lq Month Id, Ly Month Id,              Row Count - LU_MONTH, Month Of Year,                 FACT_TABLE
select    ([Month Id]@[MONTH_ID]) as ([Month Id]@[MONTH_ID]),
                [Year Id]@[YEAR_ID]
from       MTDI02: LU_MONTH
with Table Join Tree:         [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]
to            Ex1_tempcube1
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 8939
Time Spent: 0.035 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    *
from       Left Outer Join(
                                Ex1_tempcube0,
                                Ex1_tempcube1
                               ) on Month Id
to            Ex1_tempcube2
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.056 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    [Year Id]@[YEAR_ID],
                sum(sum([Tot Cost])@{[Year Id]}) as [Embedded metric:D13E30EB49069A9A386F70A263646B57]
from       Ex1_tempcube2
to            Ex1_tempcube3
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 24
Time Spent: 0.011 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
Tables Accessed:
Table3   [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]:          Region Id, Month Id, Cust State Id,                 Tot Cost, Row Count - STATE_REGION_MNTH_SLS, Tot Dollar Sales, Tot Unit Sales,           FACT_TABLE
select    [Month Id]@[MONTH_ID],
                sum([[F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3].Tot Cost])@{[Month Id]} as [Tot Cost]
from       MTDI01: STATE_REGION_MNTH_SLS
with Table Join Tree:         [F_STATE_REGION_MNTH_SLS9C927A25A02741AABA5DA6784F29A4A3]
to            Ex1_tempcube4
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.002 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
Tables Accessed:
Table9   [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]:             Year Id, Month Duration, Quarter Id, Month Id, Month Date, Month Desc, Prev Month Id, Lq Month Id, Ly Month Id,              Row Count - LU_MONTH, Month Of Year,                 FACT_TABLE
select    ([Month Id]@[MONTH_ID]) as ([Month Id]@[MONTH_ID]),
                [Year Id]@[YEAR_ID]
from       MTDI02: LU_MONTH
with Table Join Tree:         [F_LU_MONTHC5D2A4C6FA5F4D25A61ABB5F32741747]
to            Ex1_tempcube5
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 24
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    *
from       Left Outer Join(
                                Ex1_tempcube4,
                                Ex1_tempcube5
                               ) on Month Id
to            Ex1_tempcube6
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    [Year Id]@[YEAR_ID],
                sum([Tot Cost])@{[Year Id]} as [DM1]
from       Ex1_tempcube6
to            Ex1_tempcube7
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    *
from       Full Outer Join(
                                Ex1_tempcube3,
                                Ex1_tempcube7
                               ) on Year Id
to            Ex1_tempcube8
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/19/2021 10:15:33 AM
Query Execution End Time: 3/19/2021 10:15:33 AM
select    [Year Id]@[YEAR_ID],
                [DM1] as [DM1],
                [Embedded metric:D13E30EB49069A9A386F70A263646B57] as [Embedded metric:D13E30EB49069A9A386F70A263646B57]
from       Ex1_tempcube8
************   Individual Step End     ************
Strategy internal reference number for this technical note is DE189525.

Comment

0 comments

Details

Knowledge Article

Published:

March 4, 2021

Last Updated:

March 21, 2024