KB439678: Incorrect join columns are used with level metrics using outer joins

Principal Product Specialist • Strategy

This article covers a situation where level metrics set to outer join can produce invalid join clause

SYMPTOM

Invalid SQL is generated when level metrics are outer joined to non level metrics in MicroStrategy 10.x. Specifically if a metric at a higher level is set to outer join while a lower level metric is set to inner join while both include an attribute the lower level metrics version of the attribute will be used to join to lookup tables despite being less complete. This only occurs when using downward outer join set to the fourth and fifth option. An example is seen below with a level metric of revenue and a report level metric of profit.

STEPS TO REPRODUCE:

Metric Definitions:
Cost = Sum<UseLookupForAttributes=False>(Cost) {Quarter%, Country+}

Profit = Sum<UseLookupForAttributes=False>(Profit) {~+}

Report Definition:

Report Result and SQL:

create table ZZMD00 as
select   a12.QUARTER_ID QUARTER_ID_2,
   sum(a11.TOT_COST) WJXBFS1
from   DAY_CTR_SLS   a11
   join   LU_DAY   a12
   on    (a11.DAY_DATE = a12.DAY_DATE)
group by   a12.QUARTER_ID

create table ZZMD01 as
select   a13.QUARTER_ID QUARTER_ID_2,
   a12.COUNTRY_ID COUNTRY_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
from   DAY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
   on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
   join   LU_DAY   a13
   on    (a11.DAY_DATE = a13.DAY_DATE)
where   a13.QUARTER_ID in (20081, 20084, 20093, 20094)
group by   a13.QUARTER_ID,
   a12.COUNTRY_ID

select   pa12.QUARTER_ID_2 QUARTER_ID_2,
   a13.QUARTER_DESC QUARTER_DESC,
   pa11.COUNTRY_ID COUNTRY_ID,
   pa12.WJXBFS1 WJXBFS1,
   pa11.WJXBFS1 WJXBFS2
from   ZZMD01   pa11
   right outer join   ZZMD00   pa12
   on    (pa11.QUARTER_ID_2 = pa12.QUARTER_ID_2)
   join   LU_QUARTER   a13
   on    (pa11.QUARTER_ID_2 = a13.QUARTER_ID)

drop table ZZMD00 purge
drop table ZZMD01 purge

Notice how pa11 (in red above) is used to join to LU_QUARTER instead of pa12, losing all the additional rows for the outer joined metric Cost

CAUSE

This is a known issue in MicroStrategy 10.x.

ACTION

This issue has been addressed in MicroStrategy 10.9.

If you are upgrading to MicroStrategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation. For more details on enabling the setting, refer to Change Data Engine Version.

If you are upgrading to MicroStrategy 10.9 / 10.10 / 10.11: To pick up the fix, enable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults. For more details on enabling Feature Flags refer to What are the Feature Flag options and what do they accomplish in MicroStrategy Web 10.8 and up. Starting MicroStrategy 11.0, this Feature Flag is removed and replaced with project level setting called "Data Engine Version".

Product logic has been corrected to handle the join key different between left outer join (was correct) and right outer join (was not correct). After upgrade the below Report Result and SQL will be seen.

SQL:
create table ZZMD00 as
select   a12.QUARTER_ID QUARTER_ID_2,
   sum(a11.TOT_COST) WJXBFS1
from   DAY_CTR_SLS   a11
   join   LU_DAY   a12
   on    (a11.DAY_DATE = a12.DAY_DATE)
group by   a12.QUARTER_ID
create table ZZMD01 as
select   a13.QUARTER_ID QUARTER_ID_2,
   a12.COUNTRY_ID COUNTRY_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
from   DAY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
   on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
   join   LU_DAY   a13
   on    (a11.DAY_DATE = a13.DAY_DATE)
where   a13.QUARTER_ID in (20081, 20084, 20093, 20094)
group by   a13.QUARTER_ID,
   a12.COUNTRY_ID
select   pa12.QUARTER_ID_2 QUARTER_ID_2,
   a13.QUARTER_DESC QUARTER_DESC,
   pa11.COUNTRY_ID COUNTRY_ID,
   pa12.WJXBFS1 WJXBFS1,
   pa11.WJXBFS1 WJXBFS2
from   ZZMD01   pa11
   right outer join   ZZMD00   pa12
   on    (pa11.QUARTER_ID_2 = pa12.QUARTER_ID_2)
   join   LU_QUARTER   a13
   on    (pa12.QUARTER_ID_2 = a13.QUARTER_ID)
drop table ZZMD00 purge
drop table ZZMD01 purge
Notice now pa12 (in blue above) is used to join to LU_QUARTER instead of pa11, preserving the outer join for Profit metric.
WORKAROUND
Change downward outer join to the second or third option to generate SQL which properly handles the level metric's outer joins.
The MicroStrategy Internal Reference Number for the issue discussed in this technical note is KB439678 and DE55671.

Comment

0 comments

Details

Knowledge Article

Published:

June 27, 2023

Last Updated:

January 31, 2024