KB440394: How to force temp tabled to be created in primary database when description information is stored in primary database and fact is stored in secondary database in MicroStrategy 10.x

SUMMARY
A report is querying against two databases, and description information is stored in primary database while fact is stored in secondary database. Temporary table will be generated in secondary database.
STEPS TO REPRODUCE
Example Data
Primary Database
table_ASecondary Database
table_B

1. Create attribute A, attribute B from column no and column name in table_A.
2. Create fact, metric based on column percentage in table_B.
3. Put attribute A, attribute B from step 1 and metric from step 2 on a grid.
4. Check report SQL. Temporary table is generated in secondary database.

WORKAROUND

1. Create a fact based on column no in table_A.
2. Create a metric using expression max(fact);
3. Go to Project Configuration -> Report Definition -> SQL Generation -> Database Instance Ordering, choose option "Use project level database instance ordering" and ensure the Primary Database Instance is ahead of ;
4. Put the dummy metric in step2 into the grid.

Then temporary table will be generated in primary database.
Article Reference Number: KB440394 KB40394