KB441547: New optimized sql in MicroStrategy 11.0 for Multi-Table Data Import (MTDI) cubes with multiple join paths between two or more One-to-Many relationship tables.

• Strategy

This knowledge base article covers a scenario where the best join path is used to join between two or more One-to-Many relationship tables in an MTDI cube rather than using the first available table join path, in an improved sql generation design implemented in MicroStrategy 11.0.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Starting in Strategy 11.0 if there are multiple join paths between two or more One-to-Many relationship tables in a multi-table data import cube, rather than using the first available table join path, the analytical engine will look for the path with the least number of tables and the smallest table base on the following order/criteria:
1. Number of common hierarchy, the more the better
2. Logical size of the table, the smaller the better
3. Row count of the table, the less the better.

This feature is best illustrated with an example. Take a MTDI cube similar to below.

Tables in Cube:
T1: Month Id, Cust City Id, Tot Cost
T2: Quarter Id, Month Id, Month Desc, Ly Month Id       Quarter Id is the parent of Month Id and Month Desc
T3: Quarter Id, Month Id, Month Desc, Month Of Year, Year Id
T4: Month Desc De, Prev Month Id, Month Desc           Month Desc De is the parent of Prev Month Id, Prev Month Id is the parent of Month Desc
If a Dossier/Document is created using the MTDI cube, and the Month Desc De attribute and Tot Cost metric added to the template, in older versions it would join up through the Build Query table to month and to both LU_MONTH relationship tables (table 13 and table 15) to get Month Desc De attribute. This ends up producing extra use of redundant and unnecessary relationship tables. With the new optimized sql in version 11.0, the query joins directly to LU_MONTH DE table (table 19) which is the smallest table and also the best join path to get Month Desc De attribute. This can improve performance by reducing redundant table joins in the sql. This can be seen in the below CSI statements generated between the two setups.
Old behavior:


Tables Accessed:
Table13	[REL_PREV_MONTH_ID_MONTH_DESC]:	Prev Month Id, Month Desc, 		RELATIONSHIP_TABLE,	ONE_TO_MANY
Table15	[REL_MONTH_DESC_DE_PREV_MONTH_ID]:	Month Desc De, Prev Month Id, 		RELATIONSHIP_TABLE,	ONE_TO_MANY
Table16	[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]:	Month Id, Cust City Id, 	Row Count - CITY_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Cost, Tot Dollar Sales, 	FACT_TABLE	
Table18	[F_Build Query13D9A9A44E68A92A85270C9DB55D4435]:	Year Id, Month Of Year, Quarter Id, Month Id, Month Desc, 	Row Count - Build Query, 	FACT_TABLE	

[BEGIN ALTERNATIVE CSI: both alternative and normal CSI are shown and only one will be executed]

Alternative CSI: 


select	[Month Id]@[MONTH_ID],
	sum([[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8].Tot Cost])@{[Month Id]} as [Tot Cost]
with Table Join Tree: 	[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]
Save As TempTable10000	


select	[Month Desc De]@[MONTH_DESC_DE],
	sum([Table10000.Tot Cost])@{[Month Desc De]} as [Tot Cost]
with Table Join Tree: 	TempTable10000
	 Join ((Set of distinct Tuple([Month Desc]@[MONTH_DESC], [Month Id]@[MONTH_ID]) 
       where Tuple([Month Desc]@[MONTH_DESC], [Month Id]@[MONTH_ID]) in [F_Build Query13D9A9A44E68A92A85270C9DB55D4435])
	 Join [REL_PREV_MONTH_ID_MONTH_DESC] with output level Tuple([Prev Month Id]@[PREV_MONTH_ID], [Month Id]@[MONTH_ID])
	 Join [REL_MONTH_DESC_DE_PREV_MONTH_ID] with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID])) 
       with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID])


[END ALTERNATIVE CSI]

ACTION
This issue has been addressed in Strategy 11.0. 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.
New behavior:


Tables Accessed:
Table16	[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]:	Month Id, Cust City Id, 	Row Count - CITY_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Cost, Tot Dollar Sales, 	FACT_TABLE	
Table19	[F_LU_MONTH67EBA162161C46BD661125131CB22141]:	Quarter Id, Month Id, Month Desc, Month Desc De, Ly Month Id, 	Row Count - LU_MONTH, 	FACT_TABLE	

Alternative CSI: 

select	[Month Id]@[MONTH_ID],
	sum([[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8].Tot Cost])@{[Month Id]} as [Tot Cost]
with Table Join Tree: 	[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]
Save As TempTable10000	

select	[Month Desc De]@[MONTH_DESC_DE],
	sum([Table10000.Tot Cost])@{[Month Desc De]} as [Tot Cost]
with Table Join Tree: 	TempTable10000
	 Join (Set of distinct Tuple([Month Desc]@[MONTH_DESC], [Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) 
       where Tuple([Month Desc]@[MONTH_DESC], [Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) 
       in [F_LU_MONTH67EBA162161C46BD661125131CB22141]) with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID])
************   Individual Step End     ************

Strategy reference numbers for this technical note are KB441547 and DE89990.

Comment

0 comments

Details

Knowledge Article

Published:

August 20, 2018

Last Updated:

March 21, 2024

Tables Accessed: Table13 [REL_PREV_MONTH_ID_MONTH_DESC]: Prev Month Id, Month Desc, RELATIONSHIP_TABLE, ONE_TO_MANY Table15 [REL_MONTH_DESC_DE_PREV_MONTH_ID]: Month Desc De, Prev Month Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table16 [F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]: Month Id, Cust City Id, Row Count - CITY_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Cost, Tot Dollar Sales, FACT_TABLE Table18 [F_Build Query13D9A9A44E68A92A85270C9DB55D4435]: Year Id, Month Of Year, Quarter Id, Month Id, Month Desc, Row Count - Build Query, FACT_TABLE [BEGIN ALTERNATIVE CSI: both alternative and normal CSI are shown and only one will be executed] Alternative CSI: select [Month Id]@[MONTH_ID], sum([[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8].Tot Cost])@{[Month Id]} as [Tot Cost] with Table Join Tree: [F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8] Save As TempTable10000 select [Month Desc De]@[MONTH_DESC_DE], sum([Table10000.Tot Cost])@{[Month Desc De]} as [Tot Cost] with Table Join Tree: TempTable10000 Join ((Set of distinct Tuple([Month Desc]@[MONTH_DESC], [Month Id]@[MONTH_ID]) where Tuple([Month Desc]@[MONTH_DESC], [Month Id]@[MONTH_ID]) in [F_Build Query13D9A9A44E68A92A85270C9DB55D4435]) Join [REL_PREV_MONTH_ID_MONTH_DESC] with output level Tuple([Prev Month Id]@[PREV_MONTH_ID], [Month Id]@[MONTH_ID]) Join [REL_MONTH_DESC_DE_PREV_MONTH_ID] with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID])) with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) [END ALTERNATIVE CSI]

Tables Accessed: Table16 [F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8]: Month Id, Cust City Id, Row Count - CITY_MNTH_SLS, Gross Dollar Sales, Tot Unit Sales, Tot Cost, Tot Dollar Sales, FACT_TABLE Table19 [F_LU_MONTH67EBA162161C46BD661125131CB22141]: Quarter Id, Month Id, Month Desc, Month Desc De, Ly Month Id, Row Count - LU_MONTH, FACT_TABLE Alternative CSI: select [Month Id]@[MONTH_ID], sum([[F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8].Tot Cost])@{[Month Id]} as [Tot Cost] with Table Join Tree: [F_CITY_MNTH_SLS9B8350FA4AF30289D552C9AADFA486F8] Save As TempTable10000 select [Month Desc De]@[MONTH_DESC_DE], sum([Table10000.Tot Cost])@{[Month Desc De]} as [Tot Cost] with Table Join Tree: TempTable10000 Join (Set of distinct Tuple([Month Desc]@[MONTH_DESC], [Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) where Tuple([Month Desc]@[MONTH_DESC], [Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) in [F_LU_MONTH67EBA162161C46BD661125131CB22141]) with output level Tuple([Month Desc De]@[MONTH_DESC_DE], [Month Id]@[MONTH_ID]) ************ Individual Step End ************