KB16103: Query processing for report execution in the MicroStrategy Intelligence Server 9.x and 10.x

Reports are perhaps the most common requests made of Strategy Intelligence Server 9.x and 10.x. All report requests have the following pieces:
 

• Report Instance - a container for all objects and information needed and produced during report execution including templates, filters, prompt answers, generated SQL, report results, and so on.
• Task List - a list of tasks that must be accomplished to complete a job. All jobs have a task list associated with them. Strategy Intelligence Server 8.x coordinates the report instance being passed from one internal Strategy Intelligence Server 8.x component to another as a report is executed.

 
Below is a typical scenario of a report's execution within Strategy Intelligence Server (see the above diagram):
 

1. Strategy Intelligence Server receives the request.
2. The Resolution Server checks for prompts. If the report has one or more prompts, the user must answer them.
3. The Report Server checks the internal cache, if the caching feature is turned on, to see whether the report results already exist. If the report exists in the cache, Strategy Intelligence Server skips directly to the last step and delivers the report to the client. If no valid cache exists for the report, Strategy Intelligence Server creates the task list necessary to execute the report.
    
   Prompts are resolved before the Server checks for caches. Users can retrieve results from cache even if they have personalized the report with their own prompt answers. See the System Administration Guide for more information on these steps.
    
   
4. The Resolution Server obtains the report definition and any other required application objects from the Object Server. The Object Server retrieves these objects from the object cache, if possible, or reads them from the metadata via the Metadata Server. Objects retrieved from metadata are stored in the object cache.
5. The SQL Generation Engine creates the optimized SQL specific to the RDBMS being used in the data warehouse. The SQL is generated based on the definition of the report and associated application objects retrieved in the previous step.
6. The Query Engine runs the SQL against the data warehouse. The report results are returned to the Strategy Intelligence Server.
7. The Analytical Engine performs additional calculations as necessary. For most reports, this includes cross-tabbing the raw data and calculating subtotals. Some reports may require additional calculations that cannot be performed in the database via SQL.
8. Depending on the analytical complexity of the report, the results might be passed back to the Query Engine for further processing by the database until the final report is ready (in this case, steps 5 - 7 are repeated).
9. Strategy Intelligence Server's Report Server saves or updates the report in the cache, if the caching feature is turned on, and passes the formatted report back to the client, which displays the results to the user.