Description
Extra SQL passes are generated when the metric join type is set to Outer Join and SQL Global Optimization is set to Level 4 in a report, we will remove the extra SQL passes to improve performance.
Steps to Reproduce
This procedure is using the Strategy Tutorial warehouse that is shipped with Strategy.
1. Login Strategy Developer
2. Create Filter MonthNotInList: Month Not in list (Jan 02, Feb 02, Jan 03, Feb 03)
3. Create Metric M01: Sum([Units Received]) {~+} <MonthNotInList; @2; +>
4. Create Metric M02: Sum([Units Sold]) {~+} <MonthNotInList; @2; +>
5. Create a Report with Attribute item and Metric [Units Received], [Units Sold], M01, M02.
6. Go to Data -> Report Data Options -> Calculations -> Metric Join Type and set the Join type to Outer for all the four metrics
7. Go to Data -> VLDB Properties -> Show Advanced Settings and set SQL Global Optimization to Level 4 under Query Optimizations.
8. Go to SQL View, look at the red text in the query below. Notice that table #ZZOP03, #ZZOP04 and #ZZMD05 are unnecessary.
SQL Statements:
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)
Cause:
This is a known issue in Strategy.
Solution:
This issue has been addressed in Strategy 2020.
To pick up the fix, upgrade to Strategy 2020 or above and enable project level setting "Data Engine Version" to 2020 or above. For more details on enabling the setting, refer to Change Data Engine Version
With the fix, notice that no unnecessary SQL passes are generated.
SQL Statements:
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])
Strategy internal reference number for this technical note is DE129926.
