You can improve performance of SAP BW models and queries by adjusting settings in Framework Manager and in the Cognos 8 studios.
For each dimension, check the settings for the Balanced and Ragged properties.
By default, Cognos 8 uses settings that will not fail for the hierarchy type.
For dimensions that represent SAP BW characteristics, the Balanced property is set to true and the Ragged property is set to false.
For dimensions that represent presentation hierarchies, the Balanced property is set to false and the Ragged property is set to true.
For information about the types of hierarchies, see Dimensions and SAP BW Hierarchies.
If a presentation hierarchy is balanced, then the Balanced property of its associated dimension should be set to true. By default, it is assigned a value of false for all presentation hierarchies. A hierarchy is balanced if all leaf characteristic values occur at the lowest level of the hierarchy. By setting this property to true as appropriate, the Cognos 8 server is able to generate more efficient MDX.
You can define whether a dimension represents a balanced hierarchy by modifying the Balanced property of a dimension. The value that you set depends on the type of object that the dimension represents, and whether the hierarchy is balanced.
When all paths are of the same depth, set the Balanced property to true, otherwise set it to false.
The default value for a dimension representing a presentation hierarchy is false.
For a dimension that represents a characteristic without a presentation hierarchy, this property is read-only and is assigned a value of true.
Dimension represents | Balanced property value |
characteristic | true |
presentation hierarchy that is balanced | true |
presentation hierarchy that is unbalanced | false |
presentation hierarchy whose structure is unknown | false |
If you assign a value of true to the Balanced property of an unbalanced hierarchy, queries that involve this dimension may return incorrect data.
If you assign a value of false to the Balanced property of a balanced hierarchy, performance may be slower.
You can define whether a dimension represents a ragged hierarchy by modifying the Ragged property of a dimension. The value that you set depends on the type of object that the dimension represents, and whether you know if the hierarchy is ragged.
By setting the Ragged property to false as appropriate, the Cognos 8 server is able to generate more efficient MDX.
If a presentation hierarchy is not ragged, set the Ragged property of its associated dimension to false. By default, set all presentation hierarchies to true.
The default value for a dimension representing a presentation hierarchy is true.
A dimension that represents a characteristic without a presentation hierarchy is read-only.
Dimension represents | Ragged property value |
characteristic | false |
presentation hierarchy that is not ragged | false |
presentation hierarchy that is ragged | true |
presentation hierarchy whose structure is unknown | true |
If you assign a value of true to the Ragged property of an unragged hierarchy, queries that involve this dimension may return incorrect data.
If you assign a value of false to the Ragged property of a ragged hierarchy, performance may be slower.
An unbalanced hierarchy can also be ragged. In a ragged-unbalanced hierarchy, there are gaps in the levels and the levels descend to different depths.
Ragged and unbalanced hierarchies can create gaps within individual paths of a hierarchy, as well as nodes at intermediate levels with no descendants at lower levels. If a report includes query items from a single dimension that are from consecutive or adjacent levels in a hierarchy, the fact values associated with the lower levels should always aggregate to the values associated with the higher levels of the dimension in the report.
However, if the query items are not from consecutive or adjacent levels, and the underlying SAP BW hierarchy is ragged or unbalanced, it is possible that the values of the higher levels may not reflect the aggregation of the fact values from the lower levels. The aggregated fact values associated with the higher levels reflect the aggregated values in the data source. This is typical behavior for OLAP data sources, but may be counterintuitive to those accustomed to reporting against relational data sources.
You can use SAP BW variables to reduce and filter the data underlying an SAP BW Query.
If improperly configured, variable prompts that use either a picklist or hierarchical picklist can perform poorly. Use the following properties to control the performance of these types of variable prompts:
Use this property to reduce the number of characteristic values that populate a hierarchical picklist. There is a limited number of levels of a hierarchy from which values are obtained.
If the value is zero (0), which is the default, characteristic values from all levels of a hierarchy (if applicable to the type of prompt) are used to populate the picklist. Otherwise, the property specifies a colon-separated range of levels from which values are obtained (the root level is zero).
For a ragged hierarchy, you must specify all levels that you may want to use even if some branches do not have that level.
Use this property to limit the number of values that initially populate a picklist. The default is 100. It is then under the control of your users to choose the begin and end ranges of values that populate the picklist.
If the value is set to zero (0), all values are used to populate the picklist.
Use this property to set the variable to a single value. Your users are not prompted for the value of a variable and consequently, the Cognos 8 server is not required to populate a picklist with values. However, this removes any flexibility on the part of your users to affect the value of a variable.
Use this property to specify the type of prompt.
Changing a picklist or hierarchical picklist prompt to a type-in prompt can dramatically improve performance because it does not require the application server to populate a picklist with values. Unfortunately, it also requires your users to be able to accurately enter characteristic values.
For information about other variables, see SAP BW Variables.