KB19861: What are the criteria used by the MicroStrategy SQL Engine to break the report’s SQL into individual SQL passes in MicroStrategy Developer 10.x?

The Strategy SQL Engine identifies three main components when deciding how to generate the SQL required for every calculation of the report:
 

• Attributes (Dimensionality required): attributes present in the report's template and /or attributes present in the dimensionality of the metrics included in the report
  

• Filters (Conditionality required): attributes present in the report's filter or in the conditionality section of the metrics included in the report
  

• Metrics: what formula must be applied?
  

When evaluating the dataset (report)'s requirements, a unit must be identified as a single calculation with:
 

• A unique dimensionality. A unit must have one and only one dimensionality
• A unique conditionality. A unit must have one and only one conditionality
• A metric formula (if supplied)

If the report contains different sets of the above mentioned requirements, the SQL Engine will "break" these requirements into individual calculation units. A calculation unit is identified by an individual SQL pass.
 
Once the calculation units have been identified, the process of constructing the SQL for each one of them is performed:
 

1. Select the tables needed to perform the calculation.
   
• Base table: best logical table(s) for each metric
• Lookup tables: logical table(s) for attribute forms
• Connect the tables: How to join all the tables together
2. Translate each dataset into SQL.