KB440712: Outer Join to lookup table is not working in a dashboard/dossier based on Intelligent Cube with joint element list filter

• Strategy

This knowledge base article describes a defect in MicroStrategy 10.10 or earlier where outer join to lookup table not working in a dashboard/dossier based on Intelligent Cube with joint element list filter when both attributes used in the filter is present in the visualization.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM:
In Strategy 10.10 or earlier, outer join to Lookup table in a dashboard/dossier report based on Intelligent Cube with joint element list filter will not work, and incorrect CSI will be generated when both attributes used in the filter are present in visualization.
STEPS TOP REPRODUCE:
1. In Strategy Tutorial, create an Intelligent Cube with attributes: Year, Category, Subcategory, and a metric M01 with following expression. Sum(Revenue){~+}.

2. Add a Joint Element List Filter as: (Year, Subcategory) IN {(2014, Art & Architecture), (2015, TVs), (2016, Pop)}. The element value could be different in different Tutorial Warehouses that is is shipped with Strategy products but that should not matter to reproduce the defect. If these elements are not available then please pick different elements that are available in the Tutorial Warehouse used.

3. Create a Document/Dashboard with attributes: Year, Category, and metric M01.
4. Set the Attribute Join Behavior to "Outer Join - Preserve Filter"

4. Notice the outer join is applied, and that in the last pass of the CSI, there is a cross join between L_Category & L_Year and then "left outer join" to the Metric result.


Tables Accessed:
Table0	[L_YEAR]:	Year, 		LOOKUP_TABLE	
Table1	[L_CATEGORY]:	Category, 		LOOKUP_TABLE	
Table3	[F_MAIN_INDEX]:	Year, Category, Subcategory, 	M01, 	FACT_TABLE	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Year]@[YEAR_ID],
	sum([[F_MAIN_INDEX].M01])@{[Category],[Year]} as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[F_MAIN_INDEX]
Save As TempTable4	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Year]@[YEAR_ID],
	[TempTable4.M01] as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[L_CATEGORY]
	 CrossJoin [L_YEAR] with output level Tuple([Category]@[CATEGORY_ID], [Year]@[YEAR_ID])
	 LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Year]@[YEAR_ID])

5. Create another Document/Dashboard with attributes: Year, Category, Subcategory, and metric M01, with attribute join behavior also set to "Outer Join - Preserve Filter".
6. Notice the outer join is not being applied, and that the CSI is incorrect. The analytic engine uses [F_MAIN_INDEX] as a Relationship table between Category & Subcategory, but [F_MAIN_INDEX] also has Year and the CSI Engine does not generate a cross-join.


Tables Accessed:
Table0	[L_YEAR]:	Year, 		LOOKUP_TABLE	
Table1	[L_CATEGORY]:	Category, 		LOOKUP_TABLE	
Table2	[L_SUBCATEGORY]:	Subcategory, 		LOOKUP_TABLE	
Table3	[F_MAIN_INDEX]:	Year, Category, Subcategory, 	M01, 	FACT_TABLE	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Subcategory]@[SUBCAT_ID],
	[Subcategory]@[SUBCAT_DESC],
	[Year]@[YEAR_ID],
	[[F_MAIN_INDEX].M01] as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[F_MAIN_INDEX]
Save As TempTable4	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Subcategory]@[SUBCAT_ID],
	[Subcategory]@[SUBCAT_DESC],
	[Year]@[YEAR_ID],
	[TempTable4.M01] as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[L_YEAR]
	 Join [F_MAIN_INDEX] with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])
	 LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])

CAUSE:
This is a known issue in Strategy 10.x when using outer join to Lookup table in a subset report based off an OLAP Cube with joint element list filter.
ACTION/RESOLUTION:
The defect is addressed in Strategy 10.11.
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".


Tables Accessed:
Table0	[L_YEAR]:	Year, 		LOOKUP_TABLE	
Table1	[L_CATEGORY]:	Category, 		LOOKUP_TABLE	
Table2	[L_SUBCATEGORY]:	Subcategory, 		LOOKUP_TABLE	
Table3	[F_MAIN_INDEX]:	Year, Category, Subcategory, 	M01, 	FACT_TABLE	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Subcategory]@[SUBCAT_ID],
	[Subcategory]@[SUBCAT_DESC],
	[Year]@[YEAR_ID],
	[[F_MAIN_INDEX].M01] as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[F_MAIN_INDEX]
Save As TempTable4	


select	[Category]@[CATEGORY_ID],
	[Category]@[CATEGORY_DESC],
	[Subcategory]@[SUBCAT_ID],
	[Subcategory]@[SUBCAT_DESC],
	[Year]@[YEAR_ID],
	[TempTable4.M01] as [M01]
from	DE83062 - OLAP
with Table Join Tree: 	[L_YEAR]
	 CrossJoin (Set of Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID]) where Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID]) in [F_MAIN_INDEX]) with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])
	 LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])

Note: The Intelligent Cube needs to be republished after enabling
- The Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" for the fix to take effect.
- Or the Data Engine Version to 11.
Strategy reference numbers for this technical note are KB440712 and DE83062.

Comment

0 comments

Details

Knowledge Article

Published:

April 29, 2018

Last Updated:

March 21, 2024

Tables Accessed: Table0 [L_YEAR]: Year, LOOKUP_TABLE Table1 [L_CATEGORY]: Category, LOOKUP_TABLE Table3 [F_MAIN_INDEX]: Year, Category, Subcategory, M01, FACT_TABLE select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Year]@[YEAR_ID], sum([[F_MAIN_INDEX].M01])@{[Category],[Year]} as [M01] from DE83062 - OLAP with Table Join Tree: [F_MAIN_INDEX] Save As TempTable4 select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Year]@[YEAR_ID], [TempTable4.M01] as [M01] from DE83062 - OLAP with Table Join Tree: [L_CATEGORY] CrossJoin [L_YEAR] with output level Tuple([Category]@[CATEGORY_ID], [Year]@[YEAR_ID]) LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Year]@[YEAR_ID])

Tables Accessed: Table0 [L_YEAR]: Year, LOOKUP_TABLE Table1 [L_CATEGORY]: Category, LOOKUP_TABLE Table2 [L_SUBCATEGORY]: Subcategory, LOOKUP_TABLE Table3 [F_MAIN_INDEX]: Year, Category, Subcategory, M01, FACT_TABLE select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Year]@[YEAR_ID], [[F_MAIN_INDEX].M01] as [M01] from DE83062 - OLAP with Table Join Tree: [F_MAIN_INDEX] Save As TempTable4 select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Year]@[YEAR_ID], [TempTable4.M01] as [M01] from DE83062 - OLAP with Table Join Tree: [L_YEAR] Join [F_MAIN_INDEX] with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID]) LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])

Tables Accessed: Table0 [L_YEAR]: Year, LOOKUP_TABLE Table1 [L_CATEGORY]: Category, LOOKUP_TABLE Table2 [L_SUBCATEGORY]: Subcategory, LOOKUP_TABLE Table3 [F_MAIN_INDEX]: Year, Category, Subcategory, M01, FACT_TABLE select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Year]@[YEAR_ID], [[F_MAIN_INDEX].M01] as [M01] from DE83062 - OLAP with Table Join Tree: [F_MAIN_INDEX] Save As TempTable4 select [Category]@[CATEGORY_ID], [Category]@[CATEGORY_DESC], [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Year]@[YEAR_ID], [TempTable4.M01] as [M01] from DE83062 - OLAP with Table Join Tree: [L_YEAR] CrossJoin (Set of Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID]) where Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID]) in [F_MAIN_INDEX]) with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID]) LeftOuterJoin TempTable4 with output level Tuple([Category]@[CATEGORY_ID], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])