KB39927: ‘Create only secondary index on intermediate table’ option in VLDB properties does not reflect on some intermediate join table passes in MicroStrategy 9.x

SYMPTOM:
 
In Strategy 9.x, ‘Create only secondary index on intermediate table’ option in VLDB properties does not reflect on some intermediate join table passes.  
 
 
STEPS TO REPRODUCE IN THE TUTORIAL PROJECT:
 
 
1.    Create one conditional metric called ‘M1’ with the formula of { Sum(Revenue) {~+} <; @2; ->}; a filter restricting Revenue to country in us as shown below:
 

 
 
2.    Create another conditional metric called ‘M2’ with the formula of { Sum(Cost) {~+} <; @2; ->}; a filter restricting Cost to year in 2010 as shown below:
 

 
3.    Create a compound metric called ‘M3’ containing metrics M1 and M2 as components and set the Formula outer join type to be Outer.
 

 
4.    Create a report called ‘R1’ with attribute ‘year’, ‘region’ in the row; metric ‘M3’ on the template
 

 
5.    Set the following VLDB properties at the report level:

• Indexing > Intermediate Table Index > Create only secondary index on intermediate table
• Query Optimization > SQL Global Optimization > Level 2 (or less) 
• Joins > Full Outer Join Support > Not supported
• Tables > Table Creation Type >Implicit Table

6.   Execute the report and following will be noticed about the SQL:

• Four temporary tables ZZTRGS83HJMMD000, ZZTTKX048GYMD001, ZZT8L0H2ZSIOJ002, and ZZT5X1D3UOYOD003 are created
• ZZTRGS83HJMMD000 is a dataset of year and country that meets requirement for M1
• ZZTTKX048GYMD001 is a dataset of year and country that meets requirement for M2
• ZZT8L0H2ZSIOJ002 is an intermediate table that involves combining data from ZZT8L0H2ZSIOJ002 and ZZTTKX048GYMD001
• Only three indexes ZZTRGS83HJMMD000_i on ZZTRGS83HJMMD000, ZZTTKX048GYMD001_i on ZZTTKX048GYMD001, and ZZT5X1D3UOYOD003_i on ZZT5X1D3UOYOD003 are created
• No index is created on the intermediate join table ZZT8L0H2ZSIOJ002  

  
Sql
SQL Statements:
 
Pass0 -   Execution Duration:                0:00:00.09
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.03
select      a13. AS YEAR_ID,
                a12. AS COUNTRY_ID,
                sum(a11.) AS WJXBFS1
into
from             a11,
                     a12,
                                a13
where      a11. = a12. and
                a11. = a13.
 and         a12. in (1)
group by  a13.,
                a12.
 
Pass1 -   Execution Duration:                0:00:00.01
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.03
create index ZZTRGS83HJMMD000_i on ZZTRGS83HJMMD000 (YEAR_ID, COUNTRY_ID)
 
Pass2 -   Execution Duration:                0:00:00.09
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.03
select      a13. AS YEAR_ID,
                a12. AS COUNTRY_ID,
                sum(a11.) AS WJXBFS1
into
from             a11,
                     a12,
                                a13
where      a11. = a12. and
                a11. = a13.
 and         a13. in (2010)
group by  a13.,
                a12.
 
Pass3 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
create index ZZTTKX048GYMD001_i on ZZTTKX048GYMD001 (YEAR_ID, COUNTRY_ID)
 
Pass4 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
select      pa11. AS YEAR_ID,
                pa11. AS COUNTRY_ID
into
from                 pa11
 
Pass5 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.03
insert into ZZT8L0H2ZSIOJ002
select      pa11. AS YEAR_ID,
                pa11. AS COUNTRY_ID
from                  pa11
 
Pass6 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.03
select      distinct pa11. AS YEAR_ID,
                pa11. AS COUNTRY_ID
into
from                     pa11
 
Pass7 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
create index ZZT5X1D3UOYOD003_i on ZZT5X1D3UOYOD003 (YEAR_ID, COUNTRY_ID)
 
Pass8 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
                Rows selected: 1
select      pa11. AS YEAR_ID,
                pa11. AS COUNTRY_ID,
                a14. AS CUST_COUNTRY_DESC0,
                pa12. AS WJXBFS1,
                pa13. AS WJXBFS2
from                 pa11,
                        pa12,
                         pa13,
                     a14
where      pa11. = pa12. and
                pa11. = pa12. and
                pa11. = pa13. and
                pa11. = pa13. and
                pa11. = a14.
 
Pass9 -   Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
 
 
Pass10 -                 Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
drop table ZZTRGS83HJMMD000
 
Pass11 -                 Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
drop table ZZTTKX048GYMD001
 
Pass12 -                 Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
drop table ZZT8L0H2ZSIOJ002
 
Pass13 -                 Execution Duration:                0:00:00.00
                Data Fetch Duration from Datasource(s):             0:00:00.00
                Total Data Fetch Duration:     0:00:00.00
                Other Processing Duration:   0:00:00.00
drop table ZZT5X1D3UOYOD003
 
 
 
 
 
CAUSE:
 
This is a known issue with Strategy 9.x.
 
 
ACTION:
 
Strategy will continue to investigate and post updates as soon as they are available.
 
 
WORKAROUND:
 
There are two possible workarounds for this issue:
 
1.    Set VLDB property Query Optimizations > SQL Global Optimization to Level 4. With this setting, only one intermediate table will be generated and the index is created.
 
2.    Set VLDB property Tables >Table Creation Type to option “Explicit”.  With this setting, different syntax will be used when creating intermediate tables, and index will be created for each intermediate table.