Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Description
When we are using an Attribute and a Grand Total metric in a document using an In-Memory Dataset, it will generate a cross join between the fact table and the lookup table if there is no join path from the fact table to the attribute. In order to avoid the cross join on Fact Table, we will generate an extra CSI pass to calculate the grand total.
Steps to reproduce
This procedure using the Strategy Tutorial warehouse that is shipped with Strategy.
1. Login to Strategy Web, go to Add External Data -> Databases -> Pick Tables and pick the following tables
2. Click Finish to Import as an In-memory Dataset and save as MTDI01
3. Create dossier based on the In-Memory dataset MTDI01
4. Create Derived Metric: Sum(Tot Cost){} as GTCost
5. Add into template: Attribute Month Id and Derived Metric GTCost
6. We can see there is single CSI pass which involves Cross Join:
Tables Accessed:
Table4 [L_MONTH_ID]: Month Id, LOOKUP_TABLE
Table18 [F_CITY_CTR_SLSA10C6B8E17685CF16372015612015642]: Call Ctr Id, Cust City Id, Tot Cost, Gross Dollar Sales, Tot Unit Sales, Row Count – CITY_CTR_SLS, Tot Dollar Sales, FACT_TABLE
select [Month Id]@[MONTH_ID],
sum([[F_CITY_CTR_SLSA10C6B8E17685CF16372015612015642].Tot Cost]) as [GTCost]
from MTDI01
with Table Join Tree: [F_CITY_CTR_SLSA10C6B8E17685CF16372015612015642]
CrossJoin [L_MONTH_ID] with output level Tuple([Month Id]@[MONTH_ID], [Cust City Id]@[CUST_CITY_ID], [Call Ctr Id]@[CALL_CTR_ID])
Cause:
This is a known issue in Strategy.
Solution:
This issue has been addressed in Strategy 2021 and Strategy 2020 Update 3.
To pick up the fix, upgrade to Strategy 2021 or Strategy 2020 Update 3 or above and enable project level setting "Data Engine Version" to 2020 or above. For more details on enabling the setting, refer to Change Data Engine Version
With the upgrade, notice that there’s an extra pass so that we avoid to cross join on Fact table:
Tables Accessed:
Table4 [L_MONTH_ID]: Month Id, LOOKUP_TABLE
Table18 [F_CITY_CTR_SLS974A26617DAA4C9780AB4433E56F7271]: Call Ctr Id, Cust City Id, Tot Unit Sales, Tot Dollar Sales, Row Count - CITY_CTR_SLS, Tot Cost, Gross Dollar Sales, FACT_TABLE
select sum([[F_CITY_CTR_SLS974A26617DAA4C9780AB4433E56F7271].Tot Cost]) as [GTCost]
from CITY_CTR_SLS LU_MONTH (2 tables)
with Table Join Tree: [F_CITY_CTR_SLS974A26617DAA4C9780AB4433E56F7271]
Save As TempTable20
select [Month Id]@[MONTH_ID],
[TempTable20.GTCost] as [GTCost]
from CITY_CTR_SLS LU_MONTH (2 tables)
with Table Join Tree: [L_MONTH_ID]
CrossJoin TempTable20 with output level Tuple([Month Id]@[MONTH_ID])
Strategy internal reference number for this technical note is DE170264.
