KB484834: Remove extra SQL passes that are generated when the metric join type is set to "Outer Join" and SQL Global Optimization is set to "Level 4"

Description

When the metric join type is set to Outer Join and the SQL Global Optimization is set to Level 4, extra SQL passes are generated.

Steps to reproduce

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

• Log into Strategy Developer.
• Create the filter, MonthNotInList: Month Not in list (Jan 02, Feb 02, Jan 03, Feb 03).
  

• Create the metric, M01: Sum([Units Received]) {~+} .
  

• Create the metric, M02: Sum([Units Sold]) {~+} .
  

• Create a report with the attribute item and metrics [Unit Received], [Units Sold], M01, M02.
• Go to Data > Calculations > Metric Join Type.
• Set the Join type to Outer for all 4 metrics.
  

• Go to Data > VLDB Properties > Show Advanced Settings.
• Set SQL Global Optimization to Level 4 under Query Optimizations.
  

• Go to SQL View and notice the red text in the query below. Notice that tables

  #ZZOP03

  ,

  #ZZOP04

  , and

  #ZZMD05

  are unnecessary.
  

  
  select	a11.ITEM_ID  ITEM_ID,
  	sum(a11.UNITS_RECEIVED)  WJXBFS1,
  	sum((Case when a11.MONTH_ID not in (200201, 200202, 200301, 200302) then a11.UNITS_RECEIVED else NULL end))  WJXBFS2,
  	max((Case when a11.MONTH_ID not in (200201, 200202, 200301, 200302) then 1 else 0 end))  GODWFLAG5_1
  into ##ZZSP00
  from	[INVENTORY_ORDERS]	a11
  group by	a11.ITEM_ID 
  
  select	a11.ITEM_ID  ITEM_ID,
  	sum(a11.TOT_UNIT_SALES)  WJXBFS1,
  	sum((Case when a11.MONTH_ID not in (200201, 200202, 200301, 200302) then a11.TOT_UNIT_SALES else NULL end))  WJXBFS2,
  	max((Case when a11.MONTH_ID not in (200201, 200202, 200301, 200302) then 1 else 0 end))  GODWFLAG4_1
  into ##ZZSP01
  from	[ITEM_MNTH_SLS]	a11
  group by	a11.ITEM_ID 
  
  select	coalesce(pa11.ITEM_ID, pa12.ITEM_ID)  ITEM_ID,
  	pa11.WJXBFS1  WJXBFS1,
  	pa12.WJXBFS1  WJXBFS2
  into ##ZZMD02
  from	##ZZSP00	pa11
  	full outer join	##ZZSP01	pa12
  	  on 	(pa11.ITEM_ID = pa12.ITEM_ID) 
  
  select	pa01.ITEM_ID  ITEM_ID,
  	pa01.WJXBFS2  WJXBFS1
  into ##ZZOP03
  from	##ZZSP01	pa01
  where	pa01.GODWFLAG4_1 = 1 
  
  select	pa01.ITEM_ID  ITEM_ID,
  	pa01.WJXBFS2  WJXBFS1
  into ##ZZOP04
  from	##ZZSP00	pa01
  where	pa01.GODWFLAG5_1 = 1 
  
  select	coalesce(pa11.ITEM_ID, pa12.ITEM_ID)  ITEM_ID,
  	pa11.WJXBFS1  WJXBFS1,
  	pa12.WJXBFS1  WJXBFS2
  into ##ZZMD05
  from	##ZZOP03	pa11
  	full outer join	##ZZOP04	pa12
  	  on 	(pa11.ITEM_ID = pa12.ITEM_ID) 
  
  select	coalesce(pa11.ITEM_ID, pa12.ITEM_ID)  ITEM_ID,
  	a13.ITEM_NAME  ITEM_NAME,
  	pa11.WJXBFS1  WJXBFS1,
  	pa11.WJXBFS2  WJXBFS2,
  	pa12.WJXBFS1  WJXBFS3,
  	pa12.WJXBFS2  WJXBFS4
  from	##ZZMD02	pa11
  	full outer join	##ZZMD05	pa12
  	  on 	(pa11.ITEM_ID = pa12.ITEM_ID)
  	join	[LU_ITEM]	a13
  	  on 	(coalesce(pa11.ITEM_ID, pa12.ITEM_ID) = a13.ITEM_ID) 
  

  
   
  

Why is this happening?

This is a known issue in Strategy.

Solution

This issue has beed 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 upgrade, notice that there are no unnecessary SQL passes generated.

select	[a11].[ITEM_ID]  [ITEM_ID],
	sum([a11].[UNITS_RECEIVED])  [WJXBFS1],
	sum((Case when [a11].[MONTH_ID] not in (200201, 200202, 200301, 200302) then [a11].[UNITS_RECEIVED] else NULL end))  [WJXBFS2]
into ##ZZSP00
from	[INVENTORY_ORDERS]	[a11]
group by	[a11].[ITEM_ID] 

select	[a11].[ITEM_ID]  [ITEM_ID],
	sum([a11].[TOT_UNIT_SALES])  [WJXBFS1],
	sum((Case when [a11].[MONTH_ID] not in (200201, 200202, 200301, 200302) then [a11].[TOT_UNIT_SALES] else NULL end))  [WJXBFS2]
into ##ZZSP01
from	[ITEM_MNTH_SLS]	[a11]
group by	[a11].[ITEM_ID] 

select	coalesce([pa11].[ITEM_ID], [pa12].[ITEM_ID])  [ITEM_ID],
	[pa11].[WJXBFS1]  [WJXBFS1],
	[pa12].[WJXBFS1]  [WJXBFS2],
	[pa12].[WJXBFS2]  [WJXBFS3],
	[pa11].[WJXBFS2]  [WJXBFS4]
into ##ZZMD02
from	##ZZSP00	[pa11]
	full outer join	##ZZSP01	[pa12]
	  on 	([pa11].[ITEM_ID] = [pa12].[ITEM_ID]) 

select	[pa12].[ITEM_ID]  [ITEM_ID],
	[a13].[ITEM_NAME]  [ITEM_NAME],
	[pa12].[WJXBFS1]  [WJXBFS1],
	[pa12].[WJXBFS2]  [WJXBFS2],
	[pa12].[WJXBFS3]  [WJXBFS3],
	[pa12].[WJXBFS4]  [WJXBFS4]
from	##ZZMD02	[pa12]
	join	[LU_ITEM]	[a13]
	  on 	([pa12].[ITEM_ID] = [a13].[ITEM_ID])