KB18265: The "Derived table" setting for the Intermediate Table Type VLDB property is not respected when a MicroStrategy-partitioned fact table is in use in MicroStrategy SQL Generation Engine

SYMPTOM:
 
A report, or project's warehouse database instance, is configured in the Intermediate Table Type VLDB property to use derived tables for intermediate passes. (Derived tables are SELECT statements enclosed in parentheses in the FROM clause of a SQL query.) In most reports, derived tables are seen in Strategy report SQL, as highlighted below:
 
select pa11.QUARTER_ID QUARTER_ID,
   a13.QUARTER_DESC QUARTER_DESC,
   pa11.Revenue Revenue,
   pa12.WJXBFS1 WJXBFS1
from (select a11.QUARTER_ID QUARTER_ID,
      sum(a11.TOT_DOLLAR_SALES) Revenue
   from QTR_CATEGORY_SLS a11
   group by a11.QUARTER_ID
   ) pa11
   join (select a12.QUARTER_ID QUARTER_ID,
      sum(a11.TOT_DOLLAR_SALES) WJXBFS1
   from QTR_CATEGORY_SLS a11
      join LU_QUARTER a12
         on (a11.QUARTER_ID = a12.PREV_QUARTER_ID)
   group by a12.QUARTER_ID
   ) pa12
      on (pa11.QUARTER_ID = pa12.QUARTER_ID)
   join LU_QUARTER a13
      on (pa11.QUARTER_ID = a13.QUARTER_ID)
 
Certain reports, however, create temporary tables in the warehouse, despite the Intermediate Table Type configuration.
select distinct a11.PBTNAME PBTNAME
from PMT_INVENTORY a11
where a11.QUARTER_ID in (20051, 20052)
select a12.QUARTER_ID QUARTER_ID,
   a11.MONTH_ID MONTH_ID,
   sum(a11.EOH_QTY) WJXBFS1
into ZZTU30400FOPO000
from INVENTORY_Q1_2005 a11
   join LU_MONTH a12
      on (a11.MONTH_ID = a12.MONTH_ID)
where a12.QUARTER_ID in (20051, 20052)
group by a12.QUARTER_ID,
   a11.MONTH_ID
union all
select a12.QUARTER_ID QUARTER_ID,
   a11.MONTH_ID MONTH_ID,
   sum(a11.EOH_QTY) WJXBFS1
from INVENTORY_Q2_2005 a11
   join LU_MONTH a12
      on (a11.MONTH_ID = a12.MONTH_ID)
where a12.QUARTER_ID in (20051, 20052)
group by a12.QUARTER_ID,
   a11.MONTH_ID
select pc11.QUARTER_ID QUARTER_ID,
   max(pc11.MONTH_ID) WJXBFS1
into ZZTU30400FOMB001
from ZZTU30400FOPO000 pc11
group by pc11.QUARTER_ID
select distinct pa11.QUARTER_ID QUARTER_ID,
   a13.QUARTER_DESC QUARTER_DESC,
   pa11.WJXBFS1 WJXBFS1
from ZZTU30400FOPO000 pa11
   join ZZTU30400FOMB001 pa12
      on (pa11.MONTH_ID = pa12.WJXBFS1 and
      pa11.QUARTER_ID = pa12.QUARTER_ID)
   join LU_QUARTER a13
      on (pa11.QUARTER_ID = a13.QUARTER_ID)
drop table ZZTU30400FOPO000
drop table ZZTU30400FOMB001
The same may be observed if the Intermediate Table Type VLDB property is set to 'Common table expression.'
 
CAUSE:
 
If any metrics on the report use partitioning in the Strategy Metadata, intermediate passes will fall back to global temporary tables instead of using derived tables. This applies to both metadata partitioning (using filters to define data slices) and warehouse partitioning (using a partition mapping table).
When a fact table is partitioned in the Strategy Metadata, a pre-query must be issued before evaluating the metrics. The pre-query determines which partitions to access. As such, the results of the pre-query determine how SQL will be generated for the rest of the report.
For reports with partitioned fact tables, the SQL Generation Engine produces an abstract query structure that allows as many of the physical partitions as needed to be inserted by the Query Engine component when executing the query. The workflow is as follows:

1. The SQL Generation Engine produces the query flow, with three branches for partition substitution. The three branches are: no matching partitions, only one matching partition, multiple partitions.
2. The Query Engine executes the pre-query to determine the partition(s) needed.
3. The Query Engine chooses the appropriate SQL branch, fills in the physical partition name(s), and continues to execute subsequent passes.

Derived tables and common table expressions combine all the intermediate passes for the entire report into one query to submit to the database en masse. (Certain database optimizers perform better when they can evaluate all of the required operations at one time, which is not possible when multiple passes are submitted.) To generate a single query containing all calculations, however, the SQL Generation Engine must know the pre-query results prior to submitting SQL to the Query Engine. Since it is the Query Engine that executes the pre-query, it is not possible to obtain those results at the SQL generation stage.
 
ACTION:
 
The SQL Generation Engine is behaving as designed.
In some environments, derived tables or common table expressions may have been chosen to avoid issues with catalog locking or transactional performance when tables are frequently created and dropped. If partitioned fact tables exist in the project, it will be necessary to work on the database configuration to allow global temporary tables to be created for reports using the partitioned tables.
 
Note: This behavior does not apply to server-level partitioning, in which tables are partitioned natively within the database. In that case, Strategy accesses the partitioned data transparently by referring to a single table name. No pre-query is required and Strategy can produce derived table or common table expression syntax. Thus, if global temporary tables are not a viable option in a given environment and fact data must be partitioned, it is recommended to consider native database partitioning options.