Microstrategy SQL Engine Framework!

MicroStrategy engine is the brain of the whole system, no exaggeration!. It consists of 3 individual engines that work in tandem to fulfill report requests submitted by MicroStrategy desktop, MicroStrategy Web and other MicroStrategy products

SQL Engine - Responsible for generating optimized SQL and producing results sets that                         can be resolved by pure SQL alone

• It generates sql based on information such as logical representations (schema attributes and facts) of the appropriate lookup columns and fact tables stored in the metadata which the SQL engine references to in its queries
• It also looks at the VLDB settings stored in the metadata before generating the sql. VLDB settings enable you to control how the SQL engine composes certain types of sql queries.

Query Engine – Responsible for executing the SQL generated by the SQL Engine

• It acts as an intersection between the SQL engine, the Analytical engine and the data warehouse (DW)
• It is responsible for establishing the connection and executing queries against the data warehouse. 
• If a report can be resolved using pure SQL, the Query Engine only needs to interact with SQL engine to process the report. For more complicated reports, the Query engine has to interact with Analytical engine to complete the SQL Engine logic. The Query engine takes the calculation from the Analytical engine and essentially plugs it into the SQL before passing it back to the data warehouse for processing.

Analytical Engine – Responsible for performing any calculation that cannot be resolved by                        SQL alone

• Analytical engine is responsible for advanced analytical processing, cross tabbing reports, and multidimensional storage
• Depending on the complexity of the report, the Analytical engine can be invoked in all three stages of the report execution process
• Query stage (retrieve data from the warehouse). During this stage, it takes over the SQL Engine whenever a calculation cannot be resolved by SQL alone. E.g. metric qualification using analytical functions, custom group banding, or the use of functions not natively supported by data warehouse. In these scenarios, if you look at the report SQL, you will likely notice a phrase “[An Analytical SQL]” in the report SQL. If the SQL engine needs to perform more tasks, after the calculation by the Analytical engine gets inserted in the sql, it creates a table to hold the results. It then uses INSERT statements to bring back the results and continue with the rest of the query. 
• Populate and evaluate stage (fill report data required for display). During this stage, the analytical engine is invoked to perform the calculation of smart compound metrics, consolidations, subtotals, report limits, threshold evaluation, and OLAP Services operations such as dynamic aggregation and view filter evaluation. Typically, the calculations that are performed after the data warehouse has returned the results set.
• Cross tab stage (pivot and display sorting and page by). During this stage, the Analytical engine performs formatting operations such as pivoting, sorting and page by
• In addition to performing complex calculations and formatting reports, the analytical engine handles multi-dimensional storage. This includes the storage of intermediate data sets that are passed from the sql engine to the analytical engine, so the sql engine can complete a calculation performed in memory

The SQL engine, Query engine and Analytical engine are physically stored on both client machine and the I-server machine. The use of these components is dictated by the configuration of the system. In a 2 – tier configuration, the client connects directly to the data warehouse and metadata database and the MicroStrategy engine components are invoked at the client side. The SQL engine can be loaded on demand on either the client side(2tier) or on Intelligence server (in 3 tier) or 4 tier.