KB439507: When using Google Big Query and other similar databases that do not support temporary tables, certain project schema functionalities are restricted due to database product limitation

SUMMARY:
When using Google Big Query and other similar databases, certain project schema functionalities and SQLexecutionis restricted due to database side product limitation. Google Big Query currently does not support the 'CREATE TABLE' statement. Furthermore, the creation of temporary tables is not possible due to this restriction. Due to this restriction Strategy will not be able to satisfy every report requirement or design if Google Big Query is used as a data warehouse. This extends to any database that does not support the creation of temporary tables.
ACTION:
For certain scenarios, a workaround can be applied to use subqueries instead of temporary tables. This can be defined by using the VLDB Property 'Intermediate Table Type'. Changing this setting to 'Derived Table' or 'Common Table Expression' will allow for subqueries to be used. Please note 'Derived Table' is the most supported among databases. This workaround will not work for all scenarios.
The following examples are where using 'Derived table' option will not work:

1. Reports that use percentage ranking
2. Reports that query partitioned base tables
3. Reports that generate certain type of outer joins
4. Reports with custom groups
5. Reports involving certain types of functions not supported by the database but performed in the Strategy analytical engine
6. Reports that create datamarts
7. Multi-sourcing*

When one of these situations are encountered we use the setting identified with the 'Fallback Table Type' VLDB Property which creates a true temporary table or a permanent table. If this happens, another database will need to be utilized to satisfy the requirement.
 
More information for this limitation can be found here KB30257: Limitations of "derived table" intermediate table type VLDB setting in MicroStrategy SQL Engine - Multi-Sourcing will not work with databases that do not support the creation of temporary tables as it is a core feature
Limitations of "derived table" intermediate table type VLDB setting in Strategy SQL Enginefor the functionality to work successfully. However, this can possibly be worked around if:
1. Project Primary database is set to a data warehouse that supports temporary tables
2. The majority of tables used in the multi-sourced report reside in the primary database instance and/or are specifically set to use the database instance that supports temporary tables.
Strategy uses logic to moveas few tables as possible. Thus, the database instance that supports temporary tables needs to have the majority of the tables used in a multi-source report for this workaround to work.
This will cause the temporary tables to be created in the database that supports temporary tables allowing you toworkaround this limitation of Google Big Query and other similar databases.
Overall, this is a databaseside limitation. This article is not specific to Google Big Query, and applies to any database that does not support the creation of temporary tables. For full functionality of the Strategy product, it is recommended to use a database that supports the 'CREATE TABLE' statement and creation of temporary tables.
  KB439507