MicroStrategy ONE

From Clause Order

Some database platforms, such as Oracle and RedBrick, perform better depending on the order of the tables in the FROM clause. The FROM Clause Ordering property alters the order that the tables appear in the FROM clause. The MicroStrategy SQL Engine normally puts the fact table first in the FROM clause. When the property is set to switch the FROM clause order, the fact table is moved to the second table in the clause. However, if there are two fact tables in the FROM clause, it switches the order of the two tables.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Normal FROM clause order as generated by the engine 

select a12.CUSTOMER_ID CUSTOMER_ID,
 sum(a11.ORDER_AMT) WJXBFS1
from ORDER_FACT a11
 join LU_ORDER a12
 on (a11.ORDER_ID = a12.ORDER_ID)
group by a12.CUSTOMER_ID

Switch FROM clause order as generated by the engine 

select a12.CUSTOMER_ID CUSTOMER_ID,
 sum(a11.ORDER_AMT) WJXBFS1
from LU_ORDER a12
 join ORDER_FACT a11
 on (a11.ORDER_ID = a12.ORDER_ID)
group by a12.CUSTOMER_ID

Move MQ Table in normal FROM clause order to the last (for RedBrick)

This setting is added primarily for RedBrick users. The default order of table joins is as follows:

  1. Join the fact tables together.
  2. Join the metric qualification table.
  3. Join the relationship table.
  4. Join the lookup tables if needed.

This option changes the order to the following:

  1. Join the fact tables together.
  2. Join the relationship table.
  3. Join the lookup tables.
  4. Join the metric qualification table.