KB15052: Logical Views do not use temporary tables in MicroStrategy 9.x-10.x

SYMPTOM
Consider the following report SQL that gets generated while using logical views (rather than data warehouse tables) in Strategy 9.x-10.x:

select    a12.Month  Month,

                sum(a11.Quantity)  WJXBFS1

from      FactTable     a11

                join        (select Date, Date_Name, Week, Month from TimeTable)              a12

                  on         (a11.Date = a12. Date)

 
The SQL above uses derived tables rather than temporary (intermediate) tables.
 
The followed SQL may be desired by the user (but is not currently generated by Strategy Engine):

select Date, Date_Name, Week, Month

from TimeTable

into #ZZDBMO



select    a12.Month  Month,

                sum(a11.Quantity)  WJXBFS1

from      FactTable     a11

                join        #ZZDBMO           a12

                  on         (a11. Date = a12. Date)



drop table #ZZDBMO

 
CAUSE
This is working by design. A logical view is always translated into a derived table or a common table expression in Strategy Intelligence Server 9.x-10.x. Currently, there is no way to force the logical view to use a temporary table. The “Intermediate Table Type” VLDB setting is ignored here because SQL Engine does not treat logical view as an intermediate table.
 
WORKAROUND
Use database view (created on the data warehouse instead of in Strategy) to replace logical views. This may not be possible for all types of databases.
 
Article Reference Number: 

KB15052