Parallel Query Execution

Parallel Query Execution is an advanced property that is hidden by default. See Viewing and Changing Advanced VLDB Properties for more information on how to display this property.

The Parallel Query Execution property determines whether MicroStrategy attempts to execute multiple queries in parallel to return report results faster and publish Intelligent Cubes. This VLDB property has the following options:

Disable parallel query execution (default): All queries for MicroStrategy reports and Intelligent Cubes are processed sequentially.
Disabling parallel query execution by default allows you to first verify that your reports and Intelligent Cubes are executing correctly prior to any parallel query optimization. If you enable parallel query execution and errors are encountered or data is not being returned as expected, disabling parallel query execution can help to troubleshoot the report or Intelligent Cube.
Enable parallel query execution for multiple data source reports only: MicroStrategy attempts to execute multiple queries in parallel for MicroStrategy reports and Intelligent Cubes that access multiple data sources. You can access multiple data sources using either MicroStrategy MultiSource Option, or database gateway support. To enable one of these options, see Multiple Data Source Support.
For reports and Intelligent Cubes that do not use MultiSource Option or database gateway support to access multiple data sources, all queries are processed sequentially.
Enable parallel query execution for all reports that support it: MicroStrategy attempts to execute multiple queries in parallel for all MicroStrategy reports and Intelligent Cubes. This option is automatically used for data that you integrate into MicroStrategy using Data Import.

How Parallel Query Execution is Supported

To support parallel query execution, MicroStrategy analyzes the query logic that will be run for a report or Intelligent Cube for potential multiple queries. Multiple queries are used for tasks that require:

The creation of tables to store intermediate results, which are then used later in the same query.
These intermediate results must be stored as permanent tables to be considered for parallel query execution. These permanent tables are required to ensure that the parallel query execution results are available for separate database sessions and connections. If database features including derived tables or common table expressions are used, parallel query execution cannot be used because these techniques are considered to be a single query, which cannot be divided into separate pieces. Therefore, data sources that use permanent tables to store intermediate results are good candidates for parallel query execution.
MicroStrategy uses derived tables and common table expressions by default for databases that are well-suited to use these features to store intermediate results. These databases can often perform their own query optimizations using either derived tables or common table expressions, and therefore may be better suited to using these techniques rather than using MicroStrategy's parallel query execution.
Selecting independent lookup, relationship, or fact data using SQL normalization or direct data loading methods. For information on using these techniques with Intelligent Cubes and reports, see Data Population for Intelligent Cubes and Data Population for Reports respectively.
Loading multiple tables imported using Data Import, to publish a dataset. The option Enable parallel query execution for all reports that support it is automatically used for data that you integrate into MicroStrategy using Data Import.

Candidates for Parallel Query Execution

Simple reports in MicroStrategy may not require multiple queries to return the required results, so even if parallel query execution is enabled, there may be no performance benefit. However, there are various MicroStrategy features and techniques that often require multiple queries and therefore can benefit the most from parallel query execution, which include:

Consolidations and custom groups.
Level metrics and transformation metrics.
Accessing multiple data sources using MultiSource Option or database gateway support.
Accessing data sources that use temporary tables or permanent tables to store intermediate results.
Accessing data in multiple tables through the use of Data Import.

If your report or Intelligent Cube uses any of the features listed above, it may be a good candidate for using parallel query execution. Additionally, using parallel query execution can be a good option for Intelligent Cubes that are published during off-peak hours when the system is not in heavy use by the reporting community. Using parallel query execution to publish these Intelligent Cubes can speed up the publication process, while not affecting the reporting community for your system.

There are additional scenarios in MicroStrategy that can require multiple queries. To help analyze which reports and Intelligent Cubes may benefit from the use of parallel query execution, you can use the parallel query execution improvement estimate provided in the SQL view of a report or Intelligent Cube. For more information on this estimate and disabling or enabling the inclusion of this estimate, see Parallel Query Execution Improvement Estimate in SQL View.

There are some scenarios where parallel query execution cannot be used. These are described below:

When reports contain user-defined data mart SQL, parallel query execution cannot be used to execute multiple queries in parallel. For information on data mart Pre/Post Statement VLDB properties, including at what levels these VLDB properties can be defined, see Customizing SQL Statements: Pre/Post Statements.
Both MultiSource Option and warehouse partition mapping are used to return results for a report or Intelligent Cube from multiple data sources. While the use of MultiSource Option alone can be a good candidate for parallel query execution, when MultiSource Option is combined with warehouse partition mapping to return results from multiple data sources, parallel query execution cannot be used to execute multiple queries in parallel. For information on using warehouse partition mapping for a project, see the Project Design Help.
Microsoft Access databases support parallel query execution for Intelligent Cubes. However, reports and Intelligent Cubes that require the creation of temporary tables or insertion of values as part of parallel query execution are instead processed sequentially for Access databases.

When to Disable Parallel Query Execution

While performing multiple queries in parallel can improve the performance of query execution in MicroStrategy, it will not provide the best performance or results in all scenarios.

Parallel query execution is disabled by default to allow you to first verify that your reports and Intelligent Cubes are executing correctly prior to any parallel query optimization. If you enable parallel query execution and errors are encountered or data is not being returned as expected, disabling parallel query execution can help to troubleshoot the report or Intelligent Cube.

When multiple queries are performed in parallel, the actual processing of the multiple queries is performed in parallel on the database. If a database is required to do too many tasks at the same time this can cause the response time of the database to slow down, and thus degrade the overall performance. You should take into account the databases used to retrieve data and their available resources when deciding whether to enable parallel query execution.

Disabling parallel query execution can be a good option for reports and Intelligent Cubes that are not used often or ones that do not have strict performance requirements. If you can disable parallel query execution for these reports and Intelligent Cubes that do not have a great need for enhanced performance, that can save database resources to handle other potentially more important requests.

Additionally, you can limit the number of queries that can be executed in parallel for a given report or Intelligent Cube. This can allow you to enable parallel query execution, but restrict how much processing can be done in parallel on the database. To define the number of passes of SQL that can be executed in parallel, see Maximum Parallel Queries Per Report.

Levels at Which You Can Set This

Project, report, and template