An unnecessary cross join occurs when a filter on an attribute not in the template is applied in Dashboard/Dossier

Product Owner, Principal • MicroStrategy

This knowledge base article describes a defect when executing a dossier, an unnecessary cross join occurs when a filter on an attribute not in the template is applied. The cross join may have negative performance implications, but should not affect the data.

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

An unnecessary cross join occurs when a filter on an attribute not in the template is applied in Dashboard/Dossier

This knowledge base article describes a defect when executing a dossier, an unnecessary cross join occurs when a filter on an attribute not in the template is applied. The cross join may have negative performance implications, but should not affect the data.
When using an attribute that is not in the template to filter a visualization in a dossier, an unnecessary cross join occurs when a filter on an attribute not in the template is applied in Dossier in Strategy 10.11. The filter attribute as shown in the below CSI:


Tables Accessed:
Table11    [REL_REGION_CALL_CENTER]:    Region, Call Center,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table14    [REL_QUARTER_MONTH]:    Quarter, Month,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table15    [REL_YEAR_QUARTER]:    Year, Quarter,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table20    [F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25]:    Call Center, Month, Item,     Tot Cost, Row Count - ITEM_CCTR_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Dollar Sales,     FACT_TABLE    


select    [Quarter]@[QUARTER_ID_2],
    [Year]@[YEAR_ID_2],
    sum([[F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25].Tot Cost])@{[Quarter]} as [Tot Cost]
from    ITEM_CCTR_MNTH_SLS LU_MONTH LU... (6 tables)
with Table Join Tree:     [F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25]<[Call Center]@[CALL_CTR_ID] in (1, 2, 3)>
     Join [REL_QUARTER_MONTH] with output level Tuple([Call Center]@[CALL_CTR_ID], [Item]@[ITEM_ID], [Month]@[MONTH_ID_INT], [Quarter]@[QUARTER_ID_2])
     Join [REL_YEAR_QUARTER] with output level Tuple([Call Center]@[CALL_CTR_ID], [Item]@[ITEM_ID], [Month]@[MONTH_ID_INT], [Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2])
Save As TempTable26    


select    [Quarter]@[QUARTER_ID_2],
    [Year]@[YEAR_ID_2],
    [TempTable26.Tot Cost] as [Tot Cost]
from    ITEM_CCTR_MNTH_SLS LU_MONTH LU... (6 tables)
with Table Join Tree:     [REL_REGION_CALL_CENTER]<[Call Center]@[CALL_CTR_ID] in (1, 2, 3)>
     CrossJoin (Set of Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2]) where Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2]) 
in [REL_YEAR_QUARTER]) with output level distinct Tuple([Call Center]@[CALL_CTR_ID], [Quarter]@[QUARTER_ID_2], [Region]@[REGION_ID], [Year]@[YEAR_ID_2])
     LeftOuterJoin TempTable26 with output level Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2])

Steps to reproduce

1. In Strategy tutorial, create a Data Import cube, which maps to project attributes containing many to many relationship in the schema (e.g. Quarter, Call Center). If not, add explicit many to many relationship between Quarter and Call Center in the Tutorial Schema.

2. Ensure that the many to many relationship table between Quarter and Call Center is not generated when publishing the cube. That can only happen if you enable following feature flag.
In Strategy 10.9 / 10.10 / 10.11: enable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults.
In Strategy 11.0 or above, the issue cannot be reproduced

3. Publish the cube. If already published, republish the cube.
4. Revert the changes done is step 2.
In Strategy 10.9 / 10.10 / 10.11: disable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults.
Note: In Strategy 11.0 or above, the issue cannot be reproduced.

5. Create a dossier using the Data Import cube, add Year and Quarter attributes, and a metric to the template.

6. Set the visualization "Join Behavior" for "All Attributes" to "Outer Join - Preserve Filter"

7. Add a filter condition based on Call Center attribute.

8. View the visualization's Query details and notice a cross join is used to join the template attributes and filter attribute.


select	[Quarter]@[QUARTER_ID_2],
	[Year]@[YEAR_ID_2],
	[TempTable26.Tot Cost] as [Tot Cost]
from	ITEM_CCTR_MNTH_SLS LU_MONTH LU... (6 tables)
with Table Join Tree: 	[REL_REGION_CALL_CENTER]<[Call Center]@[CALL_CTR_ID] in (1, 2, 3)>
	 CrossJoin (Set of Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2]) where Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2]) 
in [REL_YEAR_QUARTER]) with output level distinct Tuple([Call Center]@[CALL_CTR_ID], [Quarter]@[QUARTER_ID_2], [Region]@[REGION_ID], [Year]@[YEAR_ID_2])
	 LeftOuterJoin TempTable26 with output level Tuple([Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2])

Why is this happening?
This is a known defect in Strategy 10.11 or earlier release where a cross join occurs just by applying a filter on an attribute not in the template.
In order the defect to be observed, following conditions must be met:
a) Data Import cube is used as a source of a Dossier/Dashboard.
b) Template has “Join Behavior” for “All Attributes” set to “Outer Join Preserve filter”
c) Two attributes are related in a many to many fashion in the Schema. This can be achieved by mapping the attributes to project schema during cube creation process.
d) One of the two related attributes is on the template. There is a filter on the other attribute.
e) There is no M:M table relating those attributes in the cube. This can be achieved, by publishing the cube with enabling the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" during cube publishing and then disabling the flag during Dossier/Dashboard execution.
Status
ACKNOWLEDGED IN-PROGRESS SOLVED (Version: Strategy 11.0)

Next Steps
This issue is fixed in Strategy 11.0. Please upgrade to this version to take advantage of this fix. The new CSI look like the one below:


Tables Accessed:
Table14	[REL_QUARTER_MONTH]:	Quarter, Month, 		RELATIONSHIP_TABLE,	ONE_TO_MANY
Table15	[REL_YEAR_QUARTER]:	Year, Quarter, 		RELATIONSHIP_TABLE,	ONE_TO_MANY
Table20	[F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25]:	Call Center, Month, Item, 	
Tot Cost, Row Count - ITEM_CCTR_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Dollar Sales, 	FACT_TABLE	

select	[Quarter]@[QUARTER_ID_2],
	[Year]@[YEAR_ID_2],
	sum([[F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25].Tot Cost])@{[Quarter]} as [Tot Cost]
from	ITEM_CCTR_MNTH_SLS LU_MONTH LU... (6 tables)
with Table Join Tree: 	[F_ITEM_CCTR_MNTH_SLS5605309749A29E91B899BBBED78B5E25]<[Call Center]@[CALL_CTR_ID] in (1, 2, 3)>
	 Join [REL_QUARTER_MONTH] with output level Tuple([Call Center]@[CALL_CTR_ID], [Item]@[ITEM_ID], [Month]@[MONTH_ID_INT], [Quarter]@[QUARTER_ID_2])
	 Join [REL_YEAR_QUARTER] with output level Tuple([Call Center]@[CALL_CTR_ID], [Item]@[ITEM_ID], [Month]@[MONTH_ID_INT], [Quarter]@[QUARTER_ID_2], [Year]@[YEAR_ID_2])
Save As TempTable26	

select	[Quarter]@[QUARTER_ID_2],
	[Year]@[YEAR_ID_2],
	[TempTable26.Tot Cost] as [Tot Cost]
from	ITEM_CCTR_MNTH_SLS LU_MONTH LU... (6 tables)
with Table Join Tree: 	TempTable26

Comment

0 comments

Details

Knowledge Article

Published:

August 14, 2018

Last Updated:

March 21, 2024