KB13509: How does the Global Optimization feature remove duplicate SQL passes in MicroStrategy SQL Generation Engine?

The Strategy SQL Generation Engine may produce duplicate SQL passes under a number of situations. Most commonly these scenarios involve set qualifications (metric qualifications, relationship filters, and report-as-filter) that are shared among multiple metrics or custom group elements.
 
Consider a report with the following structure:
 

• M1 is defined to return the 2004 revenue for regions whose total revenue, over all time, is greater than or equal to $1 million.
• M2 is defined like M1, but it returns revenue for 2005.
• The report template includes the Region attribute, M1 and M2.

 
NOTE: For the metric qualification to evaluate over all time, it is necessary to uncheck the advanced option to merge other qualifications.
 

 
The metric qualification 'Revenue >= 1000000' is needed independently by both metrics; therefore Strategy SQL Generation Engine with Global Optimization level 0  renders the metric qualification pass twice, even though the passes are functionally identical.
 
select   a12.REGION_ID REGION_ID
into #ZZMQ00
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
group by   a12.REGION_ID
having   sum(a11.TOT_DOLLAR_SALES) >= 1000000.0
 
-- identical to the previous pass
 
select   a12.REGION_ID REGION_ID
into #ZZMQ01
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
group by   a12.REGION_ID
having   sum(a11.TOT_DOLLAR_SALES) >= 1000000.0
 
select   a11.REGION_ID REGION_ID,
 
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD02
from   STATE_REGION_MNTH_SLS   a11
   
join   #ZZMQ00   pa12
 

    on    (a11.REGION_ID = pa12.REGION_ID)
   join   LU_MONTH   a13
    on    (a11.MONTH_ID = a13.MONTH_ID)
where   a13.YEAR_ID in (2004)
group by   a11.REGION_ID
 
select   a11.REGION_ID REGION_ID,
 
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD03
from   STATE_REGION_MNTH_SLS   a11
   
join   #ZZMQ01   pa12
 

    on    (a11.REGION_ID = pa12.REGION_ID)
   join   LU_MONTH   a13
    on    (a11.MONTH_ID = a13.MONTH_ID)
where   a13.YEAR_ID in (2005)
group by   a11.REGION_ID
 
select   pa11.REGION_ID REGION_ID,
 
   a13.REGION_NAME REGION_NAME,
   pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from   #ZZMD02   pa11
   join   #ZZMD03   pa12
    on    (pa11.REGION_ID = pa12.REGION_ID)
   join   LU_REGION   a13
    on    (pa11.REGION_ID = a13.REGION_ID)
 
drop table #ZZMQ00
 
drop table #ZZMQ01
drop table #ZZMD02
drop table #ZZMD03
Level 1 to level 4 Global Optimization will identify the two identical passes and eliminate one of them.
 
select   a12.REGION_ID REGION_ID
into #ZZMQ00
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
group by   a12.REGION_ID
having   sum(a11.TOT_DOLLAR_SALES) >= 1000000.0
 
select   a11.REGION_ID REGION_ID,
 
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD01
from   STATE_REGION_MNTH_SLS   a11
   
join   #ZZMQ00   pa12
 

    on    (a11.REGION_ID = pa12.REGION_ID)
   join   LU_MONTH   a13
    on    (a11.MONTH_ID = a13.MONTH_ID)
where   a13.YEAR_ID in (2004)
group by   a11.REGION_ID
 
select   a11.REGION_ID REGION_ID,
 
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD02
from   STATE_REGION_MNTH_SLS   a11
   
join   #ZZMQ00   pa12
 

    on    (a11.REGION_ID = pa12.REGION_ID)
   join   LU_MONTH   a13
    on    (a11.MONTH_ID = a13.MONTH_ID)
where   a13.YEAR_ID in (2005)
group by   a11.REGION_ID
 
select   pa11.REGION_ID REGION_ID,
 
   a13.REGION_NAME REGION_NAME,
   pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from   #ZZMD01   pa11
   join   #ZZMD02   pa12
    on    (pa11.REGION_ID = pa12.REGION_ID)
   join   LU_REGION   a13
    on    (pa11.REGION_ID = a13.REGION_ID)