What is the purpose of Multi-Pass SQL?

The MicroStrategy SQL Engine uses multiple SQL passes i.e. SELECT statements to retrieve report results from the data warehouse. This enables the users to create more complex reports, and have more flexibility in answering business questions on the reports.

Multipass SQL makes the following possible:-

• Reports defined with more than metric i.e. facts stored in separate tables
• Reports using metric qualifications
• Reports using custom groups
• Reports using level metrics
• Simulates outer join for databases that do not natively support them.

For example, a report is created with following report objects:

Attribute - Item
Metric1 - Units Sold (Inventory_Units table)
Metric 2 - Units Price (Price_Units table)

To retrieve the report results, MicroStrategy SQL Engine would need to create 3 different SQL passes (SQL statements) to the data warehouse, as the report consists of more than one metric and both of which reside in two different tables. The first SQL pass would be to retrieve Units Sold results from Inventory_Units table, while the second SQL pass would retrieve results from Price_Units table for each item on the report.

The SQL Engine uses aliases for each of the metrics and references these aliases in the final pass of SQL. This last pass is also called "Consolidation Pass" that combines the two metrics with the attribute (Item), to display on the final report.

Final pass only references the Intermediate tables that are created by the first and the second passes. SQL Engine would always avoid to use fact tables in the final pass as this would be very costly and time consuming to join large fact tables together. However, if the fact table is acting as a relationship table, then SQL engine might have no choice but to join the fact tables in the final pass.