KB38510: Intermediate Table Type "Common table expression" is not applied on reports that have training metrics in MicroStrategy

The Intermediate Table Type property "Common table expression" is not applied on reports that have training metrics in Strategy. Permanent tables are always used as intermediate tables in the report's SQL view.
 

Steps to reproduce

1. Create a training metric using Training Metric Wizard.
2. Create a report, add the training metric created in Step #1 onto the report.
3. Run the report in SQL View. Permanent tables are used as intermediate tables.
4. Go to Data > VLDB Properties > Tables > Intermediate Table Type, change it to "Common table expression".
5. Check the report SQL, and observe that permanent tables are still used as intermediate tables.
6. Remove the training metric from the report.
7. Check the report SQL again, observe that "with" clause (aka common table expression) is applied.

 
This is working as designed. With Intermediate Table Type set to "Common table expression", the report SQL is consolidated into one pass. But training metrics are calculated by the Analytical Engine, and intermediate results need to be written back to warehouse tables for further calculation. This results in multiple SQL passes and thus SQL Engine falls back to use the value set in the VLDB setting, 'Fallback Table Type'. This setting provides two options - 'Permanent table' or 'True temporary table'.
 
NOTE:
The same scenario is also expected to apply on Intermediate Table Type "Derived Table".
Refer to the article below on the limitations of "Derived Table"
KB30257: Limitations of "derived table" intermediate table type VLDB setting in MicroStrategy SQL Engine