Attribute and Attribute Form Selection Option for Intermediate Passes Properties

This section describes both the Attribute Selection Option for Intermediate Passes and the Attribute Form Selection Option for Intermediate Passes properties.

Normally, the MicroStrategy SQL Engine selects the minimum number of columns that are needed in each pass. For an intermediate pass, the SQL Engine usually selects only attribute ID forms. The SQL Engine also selects the attributes necessary to make the join, usually key attributes. Then in the final pass, additional attributes or attribute forms that are necessary for report display can be joined.

This algorithm is optimal in most cases, as it minimizes the size of intermediate tables. However, in certain schemas, especially denormalized ones, and schemas that use fact tables as both lookup tables and relationship tables, such an algorithm can cause additional joins in the final pass.

Example

A report contains Region, Store, Metric1, and Metric2. Metric1 uses FactTable1, which contains Store_ID, Store_Desc, Region_ID, Region_Desc, and F1. Metric2 uses FactTable2, which contains Store_ID, Store_Desc, Region_ID, Region_Desc, and F2.

With the normal SQL Engine algorithm, the intermediate pass that calculates Metric1 selects Store_ID and F1. The intermediate pass that calculates Metric2 selects Store_ID and F2. Then the final pass joins these two intermediate tables together. Since Region is in the report, it should join upward to the region level and find the Region_Desc form. This can be done by joining either fact table in the final pass. So either FactTable1 or FactTable2 is accessed twice. If these tables are big, which is usual, the performance can be very slow. On the other hand, if Store_ID, Store_Desc, Region_ID, and Region_Desc are picked up in the intermediate passes, neither FactTable1 or FactTable2 needs to be joined in the final pass, thus boosting performance.

The Attribute Selection Option for Intermediate Pass and Attribute Form Selection Option for Intermediate Pass settings allow the SQL Engine to select additional attributes or attribute forms in the intermediate pass, when they are directly available. When these settings are selected, the SQL Engine does not join additional tables to select more attributes or forms. So for intermediate passes, the number of tables to be joined is the same as turning the setting off.

Attribute Form Selection Option for Intermediate Pass

This property determines whether the SQL Engine selects the needed attribute forms in the intermediate passes if available. See the previous section, Attribute and Attribute Form Selection Option for Intermediate Passes Properties, for more detailed information and an example.

The options for this property are

• Select ID form only (default algorithm)
• Select ID and other forms if they are on template and available on existing join tree
• Use default inherited value

Attribute Selection Option for Intermediate Pass

This property determines whether the SQL Engine selects additional attributes needed on the report in the intermediate passes. These attributes are in addition to the needed join ID columns and are usually parent attributes. See the previous section, Attribute and Attribute Form Selection Option for Intermediate Passes Properties, for more detailed information and an example.

The options for this property are

• Select only the attributes needed (default algorithm)
• Select other attributes in current join tree if they are on template and their child attributes have already been selected
• Use default inherited value