KB482979: CSI performance optimization to improve the table join operation by using distinct to reduce the number of rows before join

Quality Architect, Senior • MicroStrategy

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
STEPS TO REPRODUCE:
1. This example uses Strategy Tutorial WH that is shipped with Strategy products.
2. In Strategy Web, create a new Dossier and click on New Data and select Databases and pick following 2 tables and no relationship is defined between any of the attributes.

3. Choose Access mode to be "In-Memory" and create a visualization as following:

4. Go to visualization menu and select Query Details... option and note that there is a direct join between the 2 tables (in red below) without reducing the number of rows by selecting minimum set of attributes before join operation.


*********   Visualization Summary Start  **********
Time Spent: 0.152 sec(s)
Query Execution Start Time: 3/20/2019 1:36:26 PM
Query Execution End Time: 3/20/2019 1:36:27 PM
Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    *********

*************   Individual Step Start   ************
Number of Rows Returned: 1152
Time Spent: 0.152 sec(s)
Query Execution Start Time: 3/20/2019 1:36:26 PM
Query Execution End Time: 3/20/2019 1:36:27 PM
Tables Accessed:

Table5	[F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]:	Region Id, Month Id, Cust State Id, 	Row Count - STATE_REGION_MNTH_SLS, Tot Unit Sales, Tot Dollar Sales, Tot Cost, Gross Dollar Sales, 	FACT_TABLE	
Table6	[F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]:	Call Ctr Id, Subcat Id, Month Id, 	Row Count - SUBCATEG_MNTH_CTR_SLS, 	FACT_TABLE	

select	[Subcat Id]@[SUBCAT_ID],	
[Cust State Id]@[CUST_STATE_ID]
from	MTDI01=FactTables
with Table Join Tree: 	[F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]	 
Join [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253] 
with output level Tuple([Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID])

************   Individual Step End     ************

CAUSE
This is a known performance problem in Strategy 2019.

ACTION
This issue has been addressed in Strategy 2019 Update 1.
If you are upgrading to Strategy 2019 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.
Product logic has been corrected to first select the distinct tuple of necessary attributes (in blue below) before the join operation.


*********   Visualization Summary Start  **********
Time Spent: 0.007 sec(s)
Query Execution Start Time: 3/20/2019 1:38:46 PM
Query Execution End Time: 3/20/2019 1:38:46 PM
Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    *********

*************   Individual Step Start   ************
Number of Rows Returned: 1152
Time Spent: 0.006 sec(s)
Query Execution Start Time: 3/20/2019 1:38:46 PM
Query Execution End Time: 3/20/2019 1:38:46 PM

Tables Accessed:
Table5	[F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]:	Region Id, Month Id, Cust State Id, 	Row Count - STATE_REGION_MNTH_SLS, Tot Unit Sales, Tot Dollar Sales, Tot Cost, Gross Dollar Sales, 	FACT_TABLE	
Table6	[F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]:	Call Ctr Id, Subcat Id, Month Id, 	Row Count - SUBCATEG_MNTH_CTR_SLS, 	FACT_TABLE	

select	[Subcat Id]@[SUBCAT_ID],	
[Cust State Id]@[CUST_STATE_ID]
from	MTDI01=FactTables
with Table Join Tree: 	(Set of distinct Tuple([Subcat Id]@[SUBCAT_ID], [Month Id]@[MONTH_ID]) where Tuple([Subcat Id]@[SUBCAT_ID], [Month Id]@[MONTH_ID]) in [F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465])	 
Join (Set of distinct Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID]) where Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID]) in [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]) with output level Tuple([Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID])
************   Individual Step End     ************

The Strategy Internal Reference Number for the issue discussed in this technical note is KB482979 and DE123254.

Comment

0 comments

Details

Knowledge Article

Published:

March 20, 2019

Last Updated:

March 21, 2024

********* Visualization Summary Start ********** Time Spent: 0.152 sec(s) Query Execution Start Time: 3/20/2019 1:36:26 PM Query Execution End Time: 3/20/2019 1:36:27 PM Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below. There are preparation tasks for each step that are not individually measured. ********* Visualization Summary End ********* ************* Individual Step Start ************ Number of Rows Returned: 1152 Time Spent: 0.152 sec(s) Query Execution Start Time: 3/20/2019 1:36:26 PM Query Execution End Time: 3/20/2019 1:36:27 PM Tables Accessed: Table5 [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]: Region Id, Month Id, Cust State Id, Row Count - STATE_REGION_MNTH_SLS, Tot Unit Sales, Tot Dollar Sales, Tot Cost, Gross Dollar Sales, FACT_TABLE Table6 [F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]: Call Ctr Id, Subcat Id, Month Id, Row Count - SUBCATEG_MNTH_CTR_SLS, FACT_TABLE select [Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID] from MTDI01=FactTables with Table Join Tree: [F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465] Join [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253] with output level Tuple([Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID]) ************ Individual Step End ************

********* Visualization Summary Start ********** Time Spent: 0.007 sec(s) Query Execution Start Time: 3/20/2019 1:38:46 PM Query Execution End Time: 3/20/2019 1:38:46 PM Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below. There are preparation tasks for each step that are not individually measured. ********* Visualization Summary End ********* ************* Individual Step Start ************ Number of Rows Returned: 1152 Time Spent: 0.006 sec(s) Query Execution Start Time: 3/20/2019 1:38:46 PM Query Execution End Time: 3/20/2019 1:38:46 PM Tables Accessed: Table5 [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]: Region Id, Month Id, Cust State Id, Row Count - STATE_REGION_MNTH_SLS, Tot Unit Sales, Tot Dollar Sales, Tot Cost, Gross Dollar Sales, FACT_TABLE Table6 [F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]: Call Ctr Id, Subcat Id, Month Id, Row Count - SUBCATEG_MNTH_CTR_SLS, FACT_TABLE select [Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID] from MTDI01=FactTables with Table Join Tree: (Set of distinct Tuple([Subcat Id]@[SUBCAT_ID], [Month Id]@[MONTH_ID]) where Tuple([Subcat Id]@[SUBCAT_ID], [Month Id]@[MONTH_ID]) in [F_SUBCATEG_MNTH_CTR_SLSEA9EE5FA16871E2AD621326912201465]) Join (Set of distinct Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID]) where Tuple([Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID]) in [F_STATE_REGION_MNTH_SLSEA9EE5FA16871DD45AA1073710051253]) with output level Tuple([Subcat Id]@[SUBCAT_ID], [Cust State Id]@[CUST_STATE_ID]) ************ Individual Step End ************