KB20586: How do Global Optimization levels 3 and 4 handle inner and outer joins between metrics in MicroStrategy SQL Generation Engine?

With Strategy SQL Generation Engine, Global Optimization levels 3 and 4 can merge metrics into a single pass that otherwise would require separate passes only because of slightly different filtering qualifications.
This has an impact on metric join types (that is, inner vs. outer joins between metrics). When a pair of metrics with different conditions are computed in separate passes and subsequently inner joined, the final result contains only the rows that match in both intermediate tables. But, if the same pair of metrics are calculated in the same pass, using CASE syntax to handle the different filters, the resulting intermediate table includes rows where either metric has a value -- effectively, the same as an outer join. If the metric join types in the report are "inner," the Strategy SQL Generation Engine must compensate and remove the rows from the final result that would have been stripped in the case of an inner join between separate intermediate tables.
When one or more report metrics specify inner join and Global Optimization level 3 or 4 comes into play, the Strategy SQL Generation Engine produces additional columns in the result table. The "Global Optimization flag" is 0 if the metric result is NULL for that row, and 1 if the metric value exists in that row. Later passes can select rows from the intermediate table where the flags are 1.
The flags are produced according to the following rules:

• All report metrics use inner join: Every metric has a flag column, and the final pass preserves rows where all flags are 1.
• One report metric uses outer join, and the rest use inner join: The outer join metric has a flag column, and the final pass preserves rows where this flag is 1. Rows containing values for inner join metrics but not the outer join metric are dropped.
• Some report metrics use outer join, and others use inner join: This case is currently not subject to Global Optimization level 3 or 4.
• All report metrics use outer join: No flags are generated. All rows of the intermediate table are preserved.

Two metrics, both inner join
 
Both metrics have a Global Optimization join flag.

insert into ZZMD00
select a12.[REGION_ID] AS REGION_ID,
sum(iif(a12.[REGION_ID] in (2), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS1,
max(iif(a12.[REGION_ID] in (2), 1, 0)) AS GODWFLAG1_1,
sum(iif(a12.[REGION_ID] in (6, 7), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS2,
max(iif(a12.[REGION_ID] in (6, 7), 1, 0)) AS GODWFLAG2_1
from [CITY_CTR_SLS] a11,
[LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
and (a12.[REGION_ID] in (2)
or a12.[REGION_ID] in (6, 7))
group by a12.[REGION_ID]

select pa12.[REGION_ID] AS REGION_ID,
a13.[REGION_NAME] AS REGION_NAME0,
pa12.[WJXBFS1] AS WJXBFS1,
pa12.[WJXBFS2] AS WJXBFS2
from [ZZMD00] pa12,
[LU_REGION] a13
where pa12.[REGION_ID] = a13.[REGION_ID]
and (pa12.[GODWFLAG1_1] = 1
and pa12.[GODWFLAG2_1] = 1)

 
Two metrics, both outer join
By definition, outer join should preserve all rows for both metrics; therefore, no Global Optimization join flag is needed.

insert into ZZMD00
select a12.[REGION_ID] AS REGION_ID,
sum(iif(a12.[REGION_ID] in (2), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS1,
sum(iif(a12.[REGION_ID] in (6, 7), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS2
from [CITY_CTR_SLS] a11,
[LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
and (a12.[REGION_ID] in (2)
or a12.[REGION_ID] in (6, 7))
group by a12.[REGION_ID]

select pa13.[REGION_ID] AS REGION_ID,
a14.[REGION_NAME] AS REGION_NAME0,
pa13.[WJXBFS1] AS WJXBFS1,
pa13.[WJXBFS2] AS WJXBFS2
from [ZZMD00] pa13,
[LU_REGION] a14
where pa13.[REGION_ID] = a14.[REGION_ID]

 
Three metrics, one outer join, two inner join
 
All rows should be preserved for the single outer join metric, whose condition is REGION_ID in (6, 7), whether or not data exist for the other metrics on the same row. The only condition to keep an intermediate table row is the presence of data for the outer join metric, so there is only one Global Optimization join flag.

insert into ZZMD00
select a12.[REGION_ID] AS REGION_ID,
sum(iif(a12.[REGION_ID] in (2), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS1,
sum(iif(a12.[REGION_ID] in (6, 7), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS2,
max(iif(a12.[REGION_ID] in (6, 7), 1, 0)) AS GODWFLAG2_1,
sum(iif(a12.[REGION_ID] in (1, 2, 3), a11.[TOT_DOLLAR_SALES], NULL)) AS WJXBFS3
from [CITY_CTR_SLS] a11,
[LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
and (a12.[REGION_ID] in (2)
or a12.[REGION_ID] in (6, 7)
or a12.[REGION_ID] in (1, 2, 3))
group by a12.[REGION_ID]

select pa13.[REGION_ID] AS REGION_ID,
a14.[REGION_NAME] AS REGION_NAME0,
pa13.[WJXBFS1] AS WJXBFS1,
pa13.[WJXBFS2] AS WJXBFS2,
pa13.[WJXBFS3] AS WJXBFS3
from [ZZMD00] pa13,
[LU_REGION] a14
where pa13.[REGION_ID] = a14.[REGION_ID]
and pa13.[GODWFLAG2_1] = 1

 
Note: Considering the factors that determine whether metrics can or cannot be combined -- such as fact table selection, metric dimensionality and filter qualification type -- alongside metric inner or outer join reveals a large variety of permutations. Many of these are outside the scope of Global Optimization level 3 or 4. Placing more metrics on the report, with more distinct dimensionalities and conditions, and mixing inner joins with outer joins makes it less likely for Global Optimization to combine passes with different WHERE clauses.
 
Further reading
For general information on Global Optimization levels 3 and 4, consult the following Strategy Knowledgebase article.
KB20579: How does the Global Optimization feature merge SQL passes with different WHERE clauses in Strategy SQL Generation Engine?