In MicroStrategy System there are basically three engine involve while processing the user request.
Below are the explanations .
SQL Engine :
SQL Engine is responsible for generating the optimized SQL and producing the result sets that can be resolved by pure SQL alone. The SQL Engine manages the Query and Analytical engine. This is considered to be the Brain behind the entire MicroStrategy engine operation. It generates SQL based on the information stored in the metadata. It also looks into the VLDB settings stored in the metadata before generating the SQL. Most cases, the SQL engine itself resolves the calculations involved for the report. At some point of time - it might need to perform complex calculations / calculations that are beyond pure SQL will be handled by Analytical Engine.
Query Engine :
Query Engine is responsible for executing queries against the DWH. Acts as a messenger to pass the SQL that is generated by SQL Engine to DWH for processing. If a report can be resolved using pure SQL, then the query engine just interacts with SQL engine alone. When there are complicated reports, the query engine need to interact with Analytical engine to complete the SQL engine logic.
Analytical Engine :
Analytical engine is responsible for advanced analytical processing, cross tabbing reports and multidimensional storage. Depending on the complexity of the report - the analytical engine is invoked in all the stages of report execution process . It Performs complex calculations on a result set returned from the data warehouse, such as statistical and financial functions. Also, sorts raw results returned from the Query Engine into a cross-tabbed grid suitable for display to the user. In addition, it performs subtotal calculations on the result set. Depending on the metric definitions, the Analytical Engine will also perform metric calculations that were not or could not be performed using SQL, such as complex functions.
The SQL engine perform join based on parent child relationship defined on the objects. In case the joining condition are not correct then it might not give expecte
The MicroStrategy SQL Engine will join database tables together based on the lowest level attributes that MicroStrategy Architect assigns to each table. If an attribute is the lowest level attribute in a hierarchy, then the MicroStrategy SQL Engine will place a key beside this attribute in its logical table view. This means that an attribute with no children is considered to be the lowest level attribute and as a result, this attribute will have a key next to it in all of its source tables. However, even though an attribute may have children, it can still have a key next to it. This situation applies when an attribute is the lowest level attribute in that particular table.
The MicroStrategy SQL Engine will then use the lowest level column name in each table to join two tables together. Furthermore, if there are multiple common lowest level attributes between two tables, then the MicroStrategy SQL Engine will use all of these common keys to join the tables together.
For example, consider the situation where the “Month” attribute is the lowest level attribute in the LU_MONTH table and “Day” attribute is the lowest level attribute in the LU_DATE and the DAY_CTR_SLS tables. This means that if there exists a report with the attributes “Month” and “Day” and the metric “Revenue”, the MicroStrategy SQL Engine will use the ‘Month’ column to join the LU_MONTH and LU_DAY tables and it will use the ‘Day’ column to join the LU_DATE and DAY_CTR_SLS tables, since these two columns are the lowest level attributes. The SQL for this report would be the following:
Sql
select a12.MONTH_ID AS MONTH_ID,
max(a13.MONTH_DESC) AS MONTH_DESC,
a11.day_date AS DAY_DATE,
sum(a11.) AS WJXBFS1
from DAY_CTR_SLS a11,
LU_DAY a12,
LU_MONTH a13
where a11.day_date = a12.day_date
and a12.MONTH_ID = a13.MONTH_ID
group by a12.,MONTH_ID
a11.day_date
Below are the explanations .
SQL Engine :
SQL Engine is responsible for generating the optimized SQL and producing the result sets that can be resolved by pure SQL alone. The SQL Engine manages the Query and Analytical engine. This is considered to be the Brain behind the entire MicroStrategy engine operation. It generates SQL based on the information stored in the metadata. It also looks into the VLDB settings stored in the metadata before generating the SQL. Most cases, the SQL engine itself resolves the calculations involved for the report. At some point of time - it might need to perform complex calculations / calculations that are beyond pure SQL will be handled by Analytical Engine.
Query Engine :
Query Engine is responsible for executing queries against the DWH. Acts as a messenger to pass the SQL that is generated by SQL Engine to DWH for processing. If a report can be resolved using pure SQL, then the query engine just interacts with SQL engine alone. When there are complicated reports, the query engine need to interact with Analytical engine to complete the SQL engine logic.
Analytical Engine :
Analytical engine is responsible for advanced analytical processing, cross tabbing reports and multidimensional storage. Depending on the complexity of the report - the analytical engine is invoked in all the stages of report execution process . It Performs complex calculations on a result set returned from the data warehouse, such as statistical and financial functions. Also, sorts raw results returned from the Query Engine into a cross-tabbed grid suitable for display to the user. In addition, it performs subtotal calculations on the result set. Depending on the metric definitions, the Analytical Engine will also perform metric calculations that were not or could not be performed using SQL, such as complex functions.
Apart from this, there are some other engines.
o
Server Delivery Engine for Narrowcast
o
Security
Engine – manages user authentication and authorization
o
Object Browsing Engine – presents the folder
navigation of the BI applications.
o
Prompting Engine – manages the presentation and
resolution of prompts
o
Resolution Engine – interprets business
definitions for efficient execution
o
Command Engine – manages all engine capacities
and job queues.
o
Cache Management Engine – retrieves report data
from caches and history lists
o
Dynamic SQL Engine – generates optimized SQL for
relational data sources
o
Dynamic MDX Engine – generates optimized MDX for
multi-dimensional cubes
o
Freeform SQL Engine -- bridges business
definitions with relational data sources
o
Formatting Engine – adds rich formatting and
converts results to the required output format
o
Monitoring Engine – displays and logs current
Intelligence Server activity
o
Scheduling Engine – gathers and runs reports
attached to time and event schedules
The SQL engine perform join based on parent child relationship defined on the objects. In case the joining condition are not correct then it might not give expecte
The MicroStrategy SQL Engine will join database tables together based on the lowest level attributes that MicroStrategy Architect assigns to each table. If an attribute is the lowest level attribute in a hierarchy, then the MicroStrategy SQL Engine will place a key beside this attribute in its logical table view. This means that an attribute with no children is considered to be the lowest level attribute and as a result, this attribute will have a key next to it in all of its source tables. However, even though an attribute may have children, it can still have a key next to it. This situation applies when an attribute is the lowest level attribute in that particular table.
The MicroStrategy SQL Engine will then use the lowest level column name in each table to join two tables together. Furthermore, if there are multiple common lowest level attributes between two tables, then the MicroStrategy SQL Engine will use all of these common keys to join the tables together.
For example, consider the situation where the “Month” attribute is the lowest level attribute in the LU_MONTH table and “Day” attribute is the lowest level attribute in the LU_DATE and the DAY_CTR_SLS tables. This means that if there exists a report with the attributes “Month” and “Day” and the metric “Revenue”, the MicroStrategy SQL Engine will use the ‘Month’ column to join the LU_MONTH and LU_DAY tables and it will use the ‘Day’ column to join the LU_DATE and DAY_CTR_SLS tables, since these two columns are the lowest level attributes. The SQL for this report would be the following:
Sql
select a12.MONTH_ID AS MONTH_ID,
max(a13.MONTH_DESC) AS MONTH_DESC,
a11.day_date AS DAY_DATE,
sum(a11.) AS WJXBFS1
from DAY_CTR_SLS a11,
LU_DAY a12,
LU_MONTH a13
where a11.day_date = a12.day_date
and a12.MONTH_ID = a13.MONTH_ID
group by a12.,MONTH_ID
a11.day_date
Comments