KB440718: Aggregate from base derived metric with an attribute to ignore filter does not get ignored in the metric calculation in Dossier/VI Dashboard if attribute join behavior is set to Outer Join To Lookup.

• Strategy

This article describes a defect in MicroStrategy 10.x where an aggregate from base derived metric with an attribute filtering gets ignored in the metric calculation in Dossier/VI Dashboard if the attribute join behavior is set to Outer Join To Lookup.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM:
Incorrect data is seen in a metric calculation of an aggregate from base derived metric with date attribute filtering in Dossier/VI Dashboard when the attribute join behavior is set to Outer Join To Lookup.
STEPS TO REPRODUCE:
1. Using Strategy Tutorial warehouse, create a Data Import cube with following tables and relationships as specified:

2. Create a dossier based on the cube.
3. Create a derived metric with definition: Sum([Tot Cost]){~+, [Month Id]%} and choose Aggregate from Base in the metric option.

4. Add Country Id, Year Id, and the derived metric to the grid, define visualization filter on Month and set the attribute Join Type to be Outer Join - Ignore Filter

5. The result is incorrect, and the attribute filtering in the derived metric is ignored as shown below.

6. Show Query Details, notice the filter on Month is applied when calculating the derived metric.


*********   Visualization Summary Start  **********
Time Spent: 0.009 sec(s)
Query Execution Start Time: 12/20/2018 5:41:54 PM
Query Execution End Time: 12/20/2018 5:41:54 PM

Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    **********

************   Individual Step Start   ************
Number of Rows Returned: 20
Time Spent: 0.006 sec(s)
Query Execution Start Time: 12/20/2018 5:41:54 PM
Query Execution End Time: 12/20/2018 5:41:54 PM

Tables Accessed:
Table0    [L_YEAR_ID]:    Year Id,         LOOKUP_TABLE    
Table1    [L_COUNTRY_ID]:    Country Id,         LOOKUP_TABLE    
Table14    [REL_QUARTER_ID_MONTH_ID]:    Quarter Id, Month Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table15    [REL_REGION_ID_CALL_CTR_ID]:    Region Id, Call Ctr Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table16    [REL_COUNTRY_ID_REGION_ID]:    Country Id, Region Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table17    [REL_YEAR_ID_QUARTER_ID]:    Year Id, Quarter Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table18    [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]:    Call Ctr Id, Month Id, Item Id,     Tot Cost, Tot Dollar Sales, Gross Dollar Sales, Tot Unit Sales, Row Count - ITEM_CCTR_MNTH_SLS,     FACT_TABLE    

select    [Year Id]@[YEAR_ID],
    [Country Id]@[COUNTRY_ID],
    sum([[F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063].Tot Cost])@{[Year Id],[Country Id]} as [DM01=Sum(Cost){~+, Month%}-AFB]
from    C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables)
with Table Join Tree:     [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]<[Month Id]@[MONTH_ID] in (200801)>
     Join [REL_QUARTER_ID_MONTH_ID] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Id]@[QUARTER_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])
     Join [REL_YEAR_ID_QUARTER_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])
     Join [REL_REGION_ID_CALL_CTR_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Region Id]@[REGION_ID])
     Join [REL_COUNTRY_ID_REGION_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Country Id]@[COUNTRY_ID])
Save As TempTable21    

select    [Year Id]@[YEAR_ID],
    [Country Id]@[COUNTRY_ID],
    [TempTable21.DM01=Sum(Cost){~+, Month%}-AFB] as [DM01=Sum(Cost){~+, Month%}-AFB]
from    C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables)
with Table Join Tree:     [L_YEAR_ID]
     CrossJoin [L_COUNTRY_ID] with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID])
     LeftOuterJoin TempTable21 with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID])
************   Individual Step End     ************

CAUSE:
This is a known defect in Strategy 10.x with the date attribute filtering on an aggregate from base derived metric gets ignored in a Dossier/VI Dashboard when attribute join behavior is set to Outer Join To Lookup.
ACTION:
To take advantage of this new feature upgrade to Strategy 10.11 or above.
If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation. For more details on enabling the setting, refer to Change Data Engine Version.
If you are upgrading to Strategy 10.11: To pick up the fix, enable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults. For more details on enabling Feature Flags refer to What are the Feature Flag options and what do they accomplish in MicroStrategy Web 10.8 and up. Starting Strategy 11.0, this Feature Flag is removed and replaced with project level setting called "Data Engine Version".
Now, the filter on Month is ignored as specified in derived metric filtering option.



*********   Visualization Summary Start  **********
Time Spent: 0.007 sec(s)
Query Execution Start Time: 12/20/2018 5:50:53 PM
Query Execution End Time: 12/20/2018 5:50:53 PM

Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    **********

************   Individual Step Start   ************
Number of Rows Returned: 20
Time Spent: 0.005 sec(s)
Query Execution Start Time: 12/20/2018 5:50:53 PM
Query Execution End Time: 12/20/2018 5:50:53 PM

Tables Accessed:
Table0    [L_YEAR_ID]:    Year Id,         LOOKUP_TABLE    
Table1    [L_COUNTRY_ID]:    Country Id,         LOOKUP_TABLE    
Table14    [REL_QUARTER_ID_MONTH_ID]:    Quarter Id, Month Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table15    [REL_REGION_ID_CALL_CTR_ID]:    Region Id, Call Ctr Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table16    [REL_COUNTRY_ID_REGION_ID]:    Country Id, Region Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table17    [REL_YEAR_ID_QUARTER_ID]:    Year Id, Quarter Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table18    [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]:    Call Ctr Id, Month Id, Item Id,     Tot Cost, Tot Dollar Sales, Gross Dollar Sales, Tot Unit Sales, Row Count - ITEM_CCTR_MNTH_SLS,     FACT_TABLE    

select    [Year Id]@[YEAR_ID],
    [Country Id]@[COUNTRY_ID],
    sum([[F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063].Tot Cost])@{[Year Id],[Country Id]} as [DM01=Sum(Cost){~+, Month%}-AFB]
from    C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables)
with Table Join Tree:     [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]
     Join [REL_QUARTER_ID_MONTH_ID] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Id]@[QUARTER_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])
     Join [REL_YEAR_ID_QUARTER_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])
     Join [REL_REGION_ID_CALL_CTR_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Region Id]@[REGION_ID])
     Join [REL_COUNTRY_ID_REGION_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Country Id]@[COUNTRY_ID])
Save As TempTable21    

select    [Year Id]@[YEAR_ID],
    [Country Id]@[COUNTRY_ID],
    [TempTable21.DM01=Sum(Cost){~+, Month%}-AFB] as [DM01=Sum(Cost){~+, Month%}-AFB]
from    C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables)
with Table Join Tree:     [L_YEAR_ID]
     CrossJoin [L_COUNTRY_ID] with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID])
     LeftOuterJoin TempTable21 with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID])
************   Individual Step End     ************

Note: The Data Import cube needs to be republished after enabling the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI".
Strategy internal reference number for this is DE84985 and KB440718.

Comment

0 comments

Details

Knowledge Article

Published:

April 30, 2018

Last Updated:

March 21, 2024

********* Visualization Summary Start ********** Time Spent: 0.009 sec(s) Query Execution Start Time: 12/20/2018 5:41:54 PM Query Execution End Time: 12/20/2018 5:41:54 PM Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below. There are preparation tasks for each step that are not individually measured. ********* Visualization Summary End ********** ************ Individual Step Start ************ Number of Rows Returned: 20 Time Spent: 0.006 sec(s) Query Execution Start Time: 12/20/2018 5:41:54 PM Query Execution End Time: 12/20/2018 5:41:54 PM Tables Accessed: Table0 [L_YEAR_ID]: Year Id, LOOKUP_TABLE Table1 [L_COUNTRY_ID]: Country Id, LOOKUP_TABLE Table14 [REL_QUARTER_ID_MONTH_ID]: Quarter Id, Month Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table15 [REL_REGION_ID_CALL_CTR_ID]: Region Id, Call Ctr Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table16 [REL_COUNTRY_ID_REGION_ID]: Country Id, Region Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table17 [REL_YEAR_ID_QUARTER_ID]: Year Id, Quarter Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table18 [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]: Call Ctr Id, Month Id, Item Id, Tot Cost, Tot Dollar Sales, Gross Dollar Sales, Tot Unit Sales, Row Count - ITEM_CCTR_MNTH_SLS, FACT_TABLE select [Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID], sum([[F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063].Tot Cost])@{[Year Id],[Country Id]} as [DM01=Sum(Cost){~+, Month%}-AFB] from C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables) with Table Join Tree: [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]<[Month Id]@[MONTH_ID] in (200801)> Join [REL_QUARTER_ID_MONTH_ID] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Id]@[QUARTER_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID]) Join [REL_YEAR_ID_QUARTER_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID]) Join [REL_REGION_ID_CALL_CTR_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Region Id]@[REGION_ID]) Join [REL_COUNTRY_ID_REGION_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Country Id]@[COUNTRY_ID]) Save As TempTable21 select [Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID], [TempTable21.DM01=Sum(Cost){~+, Month%}-AFB] as [DM01=Sum(Cost){~+, Month%}-AFB] from C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables) with Table Join Tree: [L_YEAR_ID] CrossJoin [L_COUNTRY_ID] with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID]) LeftOuterJoin TempTable21 with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID]) ************ Individual Step End ************

********* Visualization Summary Start ********** Time Spent: 0.007 sec(s) Query Execution Start Time: 12/20/2018 5:50:53 PM Query Execution End Time: 12/20/2018 5:50:53 PM Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below. There are preparation tasks for each step that are not individually measured. ********* Visualization Summary End ********** ************ Individual Step Start ************ Number of Rows Returned: 20 Time Spent: 0.005 sec(s) Query Execution Start Time: 12/20/2018 5:50:53 PM Query Execution End Time: 12/20/2018 5:50:53 PM Tables Accessed: Table0 [L_YEAR_ID]: Year Id, LOOKUP_TABLE Table1 [L_COUNTRY_ID]: Country Id, LOOKUP_TABLE Table14 [REL_QUARTER_ID_MONTH_ID]: Quarter Id, Month Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table15 [REL_REGION_ID_CALL_CTR_ID]: Region Id, Call Ctr Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table16 [REL_COUNTRY_ID_REGION_ID]: Country Id, Region Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table17 [REL_YEAR_ID_QUARTER_ID]: Year Id, Quarter Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table18 [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063]: Call Ctr Id, Month Id, Item Id, Tot Cost, Tot Dollar Sales, Gross Dollar Sales, Tot Unit Sales, Row Count - ITEM_CCTR_MNTH_SLS, FACT_TABLE select [Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID], sum([[F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063].Tot Cost])@{[Year Id],[Country Id]} as [DM01=Sum(Cost){~+, Month%}-AFB] from C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables) with Table Join Tree: [F_ITEM_CCTR_MNTH_SLS67EBA16216133B6263E840031BCC2063] Join [REL_QUARTER_ID_MONTH_ID] with output level Tuple([Month Id]@[MONTH_ID], [Quarter Id]@[QUARTER_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID]) Join [REL_YEAR_ID_QUARTER_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID]) Join [REL_REGION_ID_CALL_CTR_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Region Id]@[REGION_ID]) Join [REL_COUNTRY_ID_REGION_ID] with output level Tuple([Month Id]@[MONTH_ID], [Year Id]@[YEAR_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID], [Country Id]@[COUNTRY_ID]) Save As TempTable21 select [Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID], [TempTable21.DM01=Sum(Cost){~+, Month%}-AFB] as [DM01=Sum(Cost){~+, Month%}-AFB] from C01=InM, ITEM_CCTR_MNTH_SLS LU_MONTH LU... (3 tables) with Table Join Tree: [L_YEAR_ID] CrossJoin [L_COUNTRY_ID] with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID]) LeftOuterJoin TempTable21 with output level Tuple([Year Id]@[YEAR_ID], [Country Id]@[COUNTRY_ID]) ************ Individual Step End ************