KB30257: Limitations of "derived table" intermediate table type VLDB setting in MicroStrategy SQL Engine

The Strategy SQL engine can be made to generate derived tables by setting the Very Large Database (VLDB) setting, 'Intermediate Table Type' to 'Derived Table'. This setting is located in the 'Tables' folder. This setting is available at both Database Instance and report levels.
 

 
In some situations against the warehouse, Strategy will not use derived tables, even if the VLDB property is set so. Reports that by their nature cannot be resolved using derived tables (subqueries) are reports that fall into one of the following categories:

1. Reports that use percentage ranking - Function on the database-side doesn't exist, select data from database, perform calculation in Analytical Engine, and write to temporary table. Examples are Percentage and Percentile.
2. Reports that query partitioned base tables - Non-double aggregatable function such as count(distinct) would select data from two separate partitioned tables, and would require a temp table to insert the results of each table into before calculating results correctly.
3. Reports that generate certain type of outer joins - One example is a simulated outer join, that pull data from two separate intermediate tables but require the results to be inserted into another table to perform the outer join logic.
4. Reports with custom groups - Custom groups that use advanced logic, such as ranking or banding. Basic custom groups can possibly avoid using temporary tables depending on their logic and database function support.
5. Reports involving certain types of functions not supported by the database but performed in the Strategy analytical engine - One example is OLAPRank function, which requires the function to be calculated in the Analytical Engine and not in SQL, then have a table written to the database which can only be done into a full table.
6. Reports that create datamarts - Only pertain to the creation of data mart table which is by definition a full table being created in a database. Derived tables can still be used for intermediate table passes.

In these cases, the Strategy SQL Engine uses the value set in the VLDB setting, 'Fallback Table Type'. This setting provides two options - 'Permanent table' or 'True temporary table'.
 
The following list of articles can be used for further reference regarding the 'derived table' behavior with the Strategy SQL engine. 

• KB18851: What are the differences between derived tables and true temporary tables in MicroStrategy?
• 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