Optimizing Queries

Additional Final Pass Option

Determines whether the Engine calculates an aggregation function and a join in a single pass or in separate passes in the SQL.

(default) Final pass CAN do aggregation and join lookup tables in one pass

One additional final pass only to join lookup tables

Apply Filter Options

Indicates during which pass the report filter is applied.

Apply filter only to passes touching warehouse tables

Apply filter to passes touching warehouse tables and last join pass, if it does a downward join from the temp table level to the template level

Apply filter to passes touching warehouse tables and last join pass

Attribute Element Number Count Method

Controls how the total number of rows are calculated for incremental fetch.

Use Count(Attribute@ID) to calculate total element number (uses count distinct if necessary)

Use ODBC cursor to calculate total element number

Count Distinct with Partitions

Determines how distinct counts of values are retrieved from partitioned tables.

Do not select distinct elements for each partition

Select distinct elements for each partition

Custom Group Banding Count Method

Helps optimize custom group banding when using the Count Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.

Treat banding as normal calculation

Use standard case statement syntax

Insert band range to database and join with metric value

Custom Group Banding Points Method

Helps optimize custom group banding when using the Points Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.

Treat banding as normal calculation

Use standard case statement syntax

Insert band range to database and join with metric value

Custom Group Banding Size Method

Helps optimize custom group banding when using the Size Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.

Treat banding as normal calculation

Use standard case statement syntax

Insert band range to database and join with metric value

Data Population for Intelligent Cubes

Defines if and how Intelligent Cube data is normalized to save memory resources.

Do not normalize Intelligent Cube data

Normalize Intelligent Cube data in Intelligence Server

Normalize Intelligent Cube data in database using Intermediate Table Type

Normalize Intelligent Cube data in database using Fallback Type

Normalize Intelligent Cube data basing on dimensions with attribute lookup filtering

Normalize Intelligent Cube data basing on dimensions with no attribute lookup filtering

Data Population for Reports

Defines if and how report data is normalized to save memory resources.

Do not normalize report data

Normalize report data in Intelligence Server

Normalize report data in database using Intermediate Table Type

Normalize report data in database using Fallback Table Type

Normalize report data basing on dimensions with attribute lookup filtering

Default Sort Behavior for Attribute Elements in Reports

Determines whether the sort order of attribute elements on reports considers special sort order formatting defined for attributes.

Sort attribute elements based on the attribute ID form for each attribute

Sort attribute elements based on the defined 'Report Sort' setting of all attribute forms for each attribute

Dimensionality Model

Determines level (dimension) replacement for non parent-child related attributes in the same hierarchy.

Use relational model

Use dimensional model

Engine Attribute Role Options

Enable or disable the Analytical Engine's ability to treat attributes defined on the same column with the same expression as attribute roles.

Enable Engine Attribute Role feature

Disable Engine Attribute Role feature

Filter Tree Optimization for Metric Qualifications

Determines if metric qualifications that are included in separate passes of SQL are included in a single pass of SQL when possible.

Enable Filter tree optimization for metric qualifications

Disable Filter tree optimization for metric qualifications

Incremental Data Transfer

Determines whether data that is transferred between Intelligence Server and a database is performed using a single transfer of data or multiple, incremental transfers of data

Enable Incremental Data Transfer

Disable Incremental Data Transfer

Maximum Parallel Queries Per Report

Determines how many queries can be executed in parallel as part of parallel query execution support

User-defined

MD Partition Prequery Option

Allows you to choose how to handle prequerying the metadata partition.

Use count(*) in prequery

Use constant in prequery

Multiple Data Source Support

Defines which technique to use to support multiple data sources in a project.

Use MultiSource Option to access multiple data sources

Use database gateway support to access multiple data sources

OLAP Function Support

Defines whether OLAP functions support backwards compatibility or reflect enhancements to OLAP function logic.

Preserve backwards compatibility with 8.1.x and earlier

Recommended with 9.0 and later

Parallel Query Execution

Determines whether MicroStrategy attempts to execute multiple queries in parallel to return report results faster and publish Intelligent Cubes.

Disable parallel query execution

Enable parallel query execution for multiple data source reports only

Enable parallel query execution for all reports that support it

Parallel Query Execution Improvement Estimate in SQL View

Determines whether reports and Intelligent Cubes include an estimate in the percent of processing time that would be saved if parallel Query execution was used to run multiple queries in parallel.

Disable parallel query execution improvement estimate in SQL view

Enable parallel query execution improvement estimate in SQL view

Rank Method if DB Ranking Not Used

Determines how calculation ranking is performed.

Use ODBC ranking (MSTR 6 method)

Analytical engine performs rank

Remove Aggregation Method

Determines whether to keep or remove aggregations in SQL queries executed from MicroStrategy.

Remove aggregation according to key of FROM clause

Remove aggregation according to key of fact tables (old behavior)

Remove Group by Option

Determines whether Group By and aggregations are used for attributes with the same primary key.

Remove aggregation and Group By when Select level is identical to From level

Remove aggregation and Group By when Select level contains all attribute(s) in From level

Remove Repeated Tables for Outer Joins

Determines whether an optimization for outer join processing is enabled or disabled.

Disable optimization to remove repeated tables in full outer join and left outer join passes

Enable optimization to remove repeated tables in full outer join and left outer join passes

Set Operator Optimization

Allows you to use set operators in sub queries to combine multiple filter qualifications. Set operators are only supported by certain database platforms and with certain sub query types.

Disable Set Operator Optimization

Enable Set Operator Optimization (if supported by database and [Sub Query Type])

SQL Global Optimization

Determines the level by which SQL queries in reports are optimized.

Level 0: No optimization

Level 1: Remove Unused and Duplicate Passes

Level 2: Level 1 + Merge Passes with Different SELECT

Level 3: Level 2 + Merge Passes, which only hit DB Tables, with different WHERE

Level 4: Level 2 + Merge All Passes with Different WHERE

Sub Query Type

Allows you to determine the type of subquery used in engine-generated SQL.

WHERE EXISTS (SELECT * ...)

WHERE EXISTS (SELECT col1, col2...)

WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN

WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)

Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery

WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN

Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery

Transformation Formula Optimization

Defines whether to attempt to improve performance of reports that use expression-based transformations.

Always join with transformation table to perform transformation

Use transformation formula instead of join with transformation table when possible

Unrelated Filter Options

Determines whether the Analytical Engine should keep or remove the unrelated filter.

Remove unrelated filter

Keep unrelated filter

Keep unrelated filter and put condition from unrelated attributes in one subquery group

Unrelated Filter Options for Nested Metrics

Determines whether the Analytical Engine should keep or remove the unrelated filters when using nested metrics.

Use the 8.1.x behavior:

Use the 9.0.x behavior: