EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB484835: Apply a filter in the dataset when transferring the filter between datasets to improve performance

Min Zhao

Quality Engineer, Principal • MicroStrategy

This article provides steps for applying a filter in a dataset to improve performance when transferring a filter between datasets.

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

Description


When there are multiple datasets in the dossier and the filter is transferred between datasets via linked attribute, applying the filter in the dataset can improve performance.
You should apply the filer when the following conditions are met:

  • Multiple MTDI cubes as dataset
  • Need to transfer a filter between datasets via a linked attribute

Steps to reproduce


This example uses the Strategy Tutorial warehouse that is shipped with Strategy products.

  • Log into Strategy Web.
  • Go to Add External Data > Databases > Select Tables and pick the following table and columns.
ka0PW0000001Jf3YAE_0EM4W000001KVJi.jpeg
  • Click Finish.
  • Select to access your data via Import as an In-memory Dataset and save as "MTDI01".
ka0PW0000001Jf3YAE_0EM4W000001KVJn.jpeg
  • Go to Add External Data > Databases > Select Tables and pick the following table and columns.
ka0PW0000001Jf3YAE_0EM4W000001KVJs.jpeg
  • Click Finish.
  • Select to access your data via Import as an In-memory Dataset and save as "MTDI02".
  • Create a dossier based on MTDI01 and add MTDI02 as another dataset.
  • Link attribute Month Id between MTDI01 and MTDI02.
ka0PW0000001Jf3YAE_0EM4W000001KVJx.jpeg
  • Add the following to the grid: attribute Cust City Id, metric Tot Cost, and filter Category Id in (1).
ka0PW0000001Jf3YAE_0EM4W000001KVK2.jpeg
  • View Query Details and notice that the filter is not applied in the dataset (marked in red).
    
*********   Visualization Summary Start  **********
Time Spent: 0.061 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:45 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: 36
Time Spent: 0.005 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:44 PM

Tables Accessed:
Table0	[L_MONTH_ID]:	Month Id, 		LOOKUP_TABLE	

select	[Month Id]@[MONTH_ID]
from	MTDI01=CITY_MNTH_SLS
with Table Join Tree: 	[L_MONTH_ID]
to	Ex8_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 144
Time Spent: 0.004 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:44 PM

Tables Accessed:
Table2	[F_MNTH_CATEGORY_SLS9428FE6416B053D459419186F7328430]:	Category Id, Month Id, 	Tot Unit Sales, Row Count - MNTH_CATEGORY_SLS, 	FACT_TABLE	

select	[Category Id]@[CATEGORY_ID],
	([Month Id]@[MONTH_ID]) as ([Month Id]@[MONTH_ID])
from	MTDI02=MNTH_CATEGORY_SLS
with Table Join Tree: 	[F_MNTH_CATEGORY_SLS9428FE6416B053D459419186F7328430]
to	Ex8_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 144
Time Spent: 0.001 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:44 PM

select	*	
from	Left Outer Join(
	                Ex8_tempcube1, 
	                Ex8_tempcube2
	               ) on Month Id
to	Ex8_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:44 PM

Drop	Ex8_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:44 PM

Drop	Ex8_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.008 sec(s)
Query Execution Start Time: 12/2/2020 2:58:44 PM
Query Execution End Time: 12/2/2020 2:58:45 PM

select	[Month Id]@[MONTH_ID]
from	Ex8_tempcube3
where	[Category Id]@[CATEGORY_ID] in (1)
to	Ex8_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 2:58:45 PM
Query Execution End Time: 12/2/2020 2:58:45 PM

Drop	Ex8_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 436
Time Spent: 0.007 sec(s)
Query Execution Start Time: 12/2/2020 2:58:45 PM
Query Execution End Time: 12/2/2020 2:58:45 PM

Tables Accessed:
Table2	[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574]:	Month Id, Cust City Id, 	Row Count - CITY_MNTH_SLS, Tot Cost, 	FACT_TABLE	

select	[Cust City Id]@[CUST_CITY_ID],
	sum([[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574].Tot Cost])@{[Cust City Id]} as [Tot Cost]
from	MTDI01=CITY_MNTH_SLS
with Table Join Tree: 	[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574]<<Month Id> in (Ex8_tempcube0)>
************   Individual Step End     ************

     

Why is this happening?


This is a known issue in Strategy.

Solution


This issue has been addressed in Strategy 2020.
To take advantage of the fix, please upgrade to Strategy 2020 or a later version and enable the Data Engine Version project level setting to 2020 or later. For more details on enabling this setting, refer to Change Data Engine Version.
With the fix, notice that the filter is applied in the dataset (marked in green) when viewing Query Detail.


************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.005 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Tables Accessed:
Table0	[L_MONTH_ID]:	Month Id, 		LOOKUP_TABLE	

select	[Month Id]@[MONTH_ID]
from	MTDI01=CITY_MNTH_SLS
with Table Join Tree: 	[L_MONTH_ID]
to	Ex1_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.008 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Tables Accessed:
Table2	[F_MNTH_CATEGORY_SLS9428FE6416B053D459419186F7328430]:	Category Id, Month Id, 	Tot Unit Sales, Row Count - MNTH_CATEGORY_SLS, 	FACT_TABLE	

select	[Category Id]@[CATEGORY_ID],
	([Month Id]@[MONTH_ID]) as ([Month Id]@[MONTH_ID])
from	MTDI02=MNTH_CATEGORY_SLS
with Table Join Tree: 	[F_MNTH_CATEGORY_SLS9428FE6416B053D459419186F7328430]<[Category Id]@[CATEGORY_ID] in (1)>
to	Ex1_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.014 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

select	*	
from	Left Outer Join(
	                Ex1_tempcube1, 
	                Ex1_tempcube2
	               ) on Month Id
to	Ex1_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Drop	Ex1_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Drop	Ex1_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 36
Time Spent: 0.008 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

select	[Month Id]@[MONTH_ID]
from	Ex1_tempcube3
where	[Category Id]@[CATEGORY_ID] in (1)
to	Ex1_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Drop	Ex1_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 436
Time Spent: 0.012 sec(s)
Query Execution Start Time: 12/2/2020 3:17:36 PM
Query Execution End Time: 12/2/2020 3:17:36 PM

Tables Accessed:
Table2	[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574]:	Month Id, Cust City Id, 	Row Count - CITY_MNTH_SLS, Tot Cost, 	FACT_TABLE	

select	[Cust City Id]@[CUST_CITY_ID],
	sum([[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574].Tot Cost])@{[Cust City Id]} as [Tot Cost]
from	MTDI01=CITY_MNTH_SLS
with Table Join Tree: 	[F_CITY_MNTH_SLS9428FE6416B053B5DF41793271720574]<<Month Id> in (Ex1_tempcube0)>
************   Individual Step End     ************

 

Comment

0 comments

Details

Knowledge Article

Published:

January 17, 2021

Last Updated:

March 21, 2024