Optimizing Queries

The table below summarizes the Query Optimizations VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table.

Property Description Possible Values Default Value

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

Final pass CAN do aggregation and join lookup tables in one pass

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

Apply filter only to passes touching warehouse tables

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

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

For Tandem databases, the default is Use ODBC Cursor.

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

Do not 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

Treat banding as normal calculation

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

Treat banding as normal calculation

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

Treat banding as normal calculation

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

Normalize Intelligent Cube data in Intelligence Server

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

Do not normalize report data

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

Sort attribute elements based on the attribute ID form 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

Use relational 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

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

Enable 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

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

2

MD Partition Prequery Option

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

Use count(*) in prequery
Use constant in prequery

Use count(*) 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

Use MultiSource Option to access multiple data source

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

Preserve backwards compatibility with 8.1.x and earlier

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

Disable parallel query execution

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

Disable 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

Use ODBC ranking (MSTR 6 method).

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 aggregation according to key of FROM clause

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 aggregation and Group By when Select level is identical to 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

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])

Disable Set Operator Optimization

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

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

Use Temporary Table, falling back to EXISTS (SELECT *...) 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

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

Remove unrelated filter

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:

Use the 8.1.x behavior

WHERE Clause Driving Table

Determines the table used for qualifications in the WHERE clause.

Use lookup table
Use fact table

Use fact table