KB483316: Attribute outer join with preserve filter incorrectly applies cross join when filter and template attributes have a many to many relationship

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

Symptoms

• Data Engine Version setting is set to 11.
• An attribute used in a filter and an attribute on the Visualization have a Many-to-Many relationship.
• Join Type for "All Attributes" is set to "Outer Join - Preserve Filter" for the Visualization.

Glossary

• Cube Subsetting Instruction (CSI)

Steps to Reproduce

• Open Strategy Web.
• Create a new dataset. We use Strategy Tutorial objects:
  
• Add External Data > Databases > Select Tables.
• Select "CITY_MNTH_SLS" and "LU_MONTH".
• Prepare Data.
  
• In "LU_MONTH", convert Month of Year to an attribute.
• Define relationships for "LU_MONTH": 

• Create a new dossier, and add the dataset you just created.
  
• Create a new metric Sum(Cost){Month of Year%} with the following definition: 

• Drag Quarter Id and Sum(Cost){Month of Year%} to a Grid Visualization.
• Edit the filter of the visualization and add a qualification on Month of Year: 

• Change the Join Behavior for the visualization (Click the three dots in the top-right corner > More Options...):

• Open Query Details to inspect the CSI.

In the CSI below, a Cross Join is used to apply the filter on Month of Year. However, for large datasets, this unnecessary Cross Join can slow down execution considerably.
 
CSI before fix

...
select [Quarter Id]@[QUARTER_ID],
[TempTable25.Sum(Cost){Month of Year%}] as [Sum(Cost){Month of Year%}]
from C01.CITY_MNTH_SLS LU_MONTH (2 tables)
with Table Join Tree: (Set of distinct Tuple([Month Of Year]@[MONTH_OF_YEAR])
where Tuple([Month Of Year]@[MONTH_OF_YEAR])
in [REL_MONTH_OF_YEAR_MONTH_ID]<[Month Of Year]@[MONTH_OF_YEAR] in (1)>)
CrossJoin (Set of Tuple([Quarter Id]@[QUARTER_ID])
where Tuple([Quarter Id]@[QUARTER_ID])
in [L_QUARTER_ID]) with output level distinct Tuple([Month Of Year]@[MONTH_OF_YEAR], [Quarter Id]@[QUARTER_ID])
LeftOuterJoin TempTable25 with output level Tuple([Month Of Year]@[MONTH_OF_YEAR], [Quarter Id]@[QUARTER_ID])
display having [Month Of Year]@[MONTH_OF_YEAR] in (1)
...

Cause

• The cross join that is executed in the last section of CSI is not needed to filter the visualization.
  
• A new feature implemented under Data Engine Version 11 prevents Many-to-Many relationship tables from being built when an Intelligent Cube is published In-Memory. However, in this case, a local Many-to-Many relationship still exists because Data Engine logic failed to remove it.

Solution

• Upgrade to Strategy 2020 or above. We recommend the latest version.
• Set Data Engine Version setting to 12 or above.
  
• 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.
• After changing the Data Engine Version setting, you will need to republish your datasets to see the changes.

The motivation behind this fix is to improve performance. In the new fix, local Many-to-Many relationships are not loaded into the schema when Data Engine Version is set to 12 or above. This improves the CSI that is executed. 
 
CSI after fix

...
select [Quarter Id]@[QUARTER_ID],
[TempTable25.Sum(Cost){Month of Year%}] as [Sum(Cost){Month of Year%}]
from C01.CITY_MNTH_SLS LU_MONTH (2 tables)
with Table Join Tree: [L_QUARTER_ID]
LeftOuterJoin TempTable25 with output level Tuple([Quarter Id]@[QUARTER_ID])
...