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

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

Description

Performance degradation occurs in multiple dataset dossiers with filters applied to linked attributes.

Steps to reproduce

This procedure using the Strategy Tutorial warehouse that is shipped with Strategy.

• Log in to Strategy Web.
• Choose Add External Data > Databases > Pick Tables.
• Pick the following table and columns.
  

• Click Finish.
  

• Click Import as an In-memory dataset.
• Save your dataset with the name MTDI01.
• Choose Add External Data > Database > Pick Tables.
• Pick the following table and columns.
  

• Click Finish.
• Click Import as an In-memory dataset.
• Save your dataset with the name MTDI02.
• Create a dossier based on the MTDI01 dataset and add MTDI01 as another dataset.
• Link the Month Id attribute between MTDI01 and MTDI02.
  

• Create a grid with the Cust City Id and a metric for Tot Cost.
• Create a Category Id flter with a selection of 1 as shown below.
  

• View the query details. Look at the red text in the query below. Notice the filter does not get applied in the dataset.
   
  

  
  *********   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     ************
  
  

  
  

Solution

This issue has been addressed in Strategy 2020 and above. Upgrade to Strategy 2020 or above and enable the project level setting Data Engine Version to 2020 or above.
After the upgrade, view the query details. Look at the green text in the query below. Notice the filter is now applied in the dataset.

************   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     ************