Dynamic query mode governors

(DQM) Adjust SQL generation for exact numeric division

This governor controls how calculations with division are adjusted to ensure that the division results contain information that is significant for the reports.

The Cast to Double setting is used to cast the numerator of the division to double precision. For example:

• {_}[item1] / [item2]{_} becomes _cast([item1] as double precision) / [item2]_
• {_}cast ([item1] as decimal(9,2)) / [item2]{_} becomes _cast(cast([item1] as decimal(9,2)) as double precision) / [item2]_

The Cast to Double conditional setting is used to cast the numerator of the division to double precision, only if the numerator is not a cast operation. For example:

• {_}[item1] / [item2]{_} becomes _cast([item1] as double precision) / [item2]_
• {_}cast ([item1] as decimal(9,2)) / [item2]{_} will not be modified because the numerator is a cast operation.

The Do not adjust setting does not convert the calculation. No cast operation is added.

The default setting is Cast to Double.

(DQM) Cache is sensitive to connection command blocks

This governor specifies whether the key to the cache includes the expanded values of the connection command blocks. If the statements in the command blocks determine what data a query will return, this governor should be set.

For example, you create a connection command block that contains a macro with a reference to the session parameter for a user name. As a result, the expanded value of the command block is different for each user. This difference is not significant if the user name is used only for logging. In this case, the cache likely can be shared and you can turn off this governor. However, if the user name controls data retrieval, the cache likely cannot be shared and you must select this governor.

If this governor is selected, the cache is shared only by users that share the version of the expanded connection command blocks used to load data into the cache.

If this governor is not selected, differences in connection command blocks are ignored.

The governor is selected by default.

For more information, see the section about command blocks in the IBM Cognos Analytics Administration and Security Guide.

(DQM) Cache is sensitive to DB info

This governor controls the sensitivity of the cache that is associated with a Framework Manager package that is shared by users of the connection. It also specifies what database information is used to restrict sharing in that cache. The information is originally specified in IBM Content Manager as well as in Framework Manager and is provided on the request to the cache.

The DB + Connection + Signon setting specifies that the cache is shared only if users specify the same data sources, connection strings, and signon information. This is the most restrictive level of caching.

The DB + Connection setting specifies that the cache is shared only if users specify the same data sources and connection strings.

The DB setting specifies that the cache is shared only if users specify the same data sources.

The None setting specifies that none of the data sources, connection strings, or signon information is used by the cache. This is the least restrictive level of caching.

The default setting is DB + Connection + Signon.

(DQM) Cache is sensitive to model security

This governor controls the security that is used to access the cache.

The Automatic setting specifies that the Cognos user and user classes are used to confirm access to all the security filters in the model. The union of the security objects with the model security filters is used to identify the cache.

The User setting specifies that the Cognos user identity is used to identify the cache. The cache is reusable for the current user only. No sharing with other users takes place.

The UserClass setting specifies that the Cognos user classes are used to identify the cache.

The None setting disables checking for model security filters, even if there are such filters in the model.

The default setting is Automatic.

(DQM) Context Sensitive Join Selection

Use this governor to control the computation of join paths for a query in a star schema grouping that does not contain a fact.

Use star schema groupings to choose a join path.

The Disabled setting specifies that the star schema grouping is ignored when the join path of the main query is computed. If there are multiple join paths, the first alphabetically sorted one is chosen.

The Automatic setting specifies that the star schema grouping is used to influence the computation of the join path if all query subjects and shortcuts that are directly accessed in the query can unwind to their lowest database query subject and all intermediate query subjects are without relationships. The star schema grouping is ignored otherwise. This behavior is also how join paths are computed in the compatible query mode.

The Explicit setting specifies that the star schema grouping is used to influence the computation of the join path. If the (DQM) Use in Join Path property is set to True for a query subject or shortcut, then the tables that they were built on are used in the final join path in the SQL. If no query subject is marked to be used in the join path, then the context-sensitive join selection is not applied on the star schema grouping.

The default setting is Disabled.

For example, you have four query subjects: Store, Country_or_region, Camera_sales (a fact), and Returned_cameras (a fact). The following joins are specified: between Store and Camera_sales, between Store and Returned_cameras, between Country_or_region and Camera_sales, and between Country_or_region and Returned_cameras.

You want to create a report that displays stores and countries in which cameras were returned. The report includes the stores and countries only. The context of the report is provided only in the report title. This query is fact-less because the Returned_cameras query subject is not selected. To get the correct output, the join path must be from Store through Returned_cameras to Country_or_region. The join path must include Returned_cameras. If you do not create a star schema to group Store, Returned_cameras, and Country_or_region, the join path goes through Camera_sales. This result occurs because Camera_sales is the first fact query subject in alphabetical order. To solve this problem, create a star schema grouping. Create a folder that is named Returned_cameras and under that folder, create shortcuts to Store, Returned_cameras, and Country_or_region. Set the (DQM) Context Sensitive Join Selection governor to Automatic.

(DQM) Local cache policy

When query reuse is enabled, the dynamic query mode by default attempts to create caches for the lowest level of summarization of data in a query. Dynamic query does not cache data for a query that has the Auto Group and Summarize query property set to No or expressions that use a vendor instead of Cognos built-in function. Report authors can use the Validate feature in Cognos Analytics - Reporting for key transformations to obtain feedback from dynamic query mode to determine where query reuse can or cannot be applied.

In more complex reports, report authors might define several queries, which they use in other queries, that involve joins and set operations. In some situations, particularly with large data volumes, the report authors might want to disable query reuse to force a join or set operation to be pushed to the data source.

In other scenarios, report authors might determine that they want to control which queries in a report should be cached and reused instead of accepting the default strategy of creating caches for the lowest level of summarization of data in a query. Changing the reuse policy governor allows the report author to control which queries are used to cache data. This governor cannot be overridden from within a report and once active can also force dynamic query to cache non-summarized data sets. Potentially, a model might need to be published twice: once with the default strategy as for general report authoring and interactive analysis, and once with the policy set to Explicitly per query or Query referenced by the layout.

The reports that can be optimized by overriding the default strategy would use the second option. Report authors should cache the least possible number of queries in a report to avoid creating caches that are not frequently reused and might cause other caches to be removed when limits are reached.

(DQM) Cursor Mode

Use this governor to control how long the resources that are required by a query are retained before they are released.

The query engine loads data from a data source into a data set with a cursor. The cursor can be read completely or partially. As soon as the query engine reads the last record, the result set is complete and the database resources are released.

The Automatic setting specifies that the query engine stops reading data after the requested number of records. It leaves the resources active in anticipation of further requests for data. The stopped query retains the database connection and the cursor for future data retrieval requests. Stopped queries are released after a specified amount of idle time. As a result, database resources are released either after all data is rendered or after a certain amount of idle time or maximum age time. During this time, these resources cannot be used by other queries.

The Query Per Page setting specifies that the query engine releases resources as soon as the current report page is rendered to the user. Every subsequent page request, including those requests that previously loaded the complete result set, requires that the database connection and the cursor to be reestablished. This setting releases data source resources the fastest, but requires the most use of time and resources to re-execute a query.

The Load In Background setting specifies that the query engine returns the requested portion of the data and then starts a background thread to load the rest of the data into a cache. The background thread runs at a lesser priority. Further requests return the data that is loaded by the background thread from the cache. If more data is required before the background thread loads sufficient data, the new request takes priority. This setting provides a fast first page response and improved response time for subsequent pages. The resources are released as soon as all the data is loaded into the cache. However, more memory is used for the cached data than with the other settings.

The default setting is Automatic.

(DQM) Master-Detail Optimization

Business reports that contain page sets or master-detail relationships might issue a large number of requests to a data source. The data is temporarily cached and local queries are executed to retrieve new rows from the data source as the detail layout requires new data. These additional requests for data increase the load on the data sources and impact the report execution time.

For example, a list frame might have a master-detail relationship with another list frame. If the master list frame renders 1000 rows of data, it requires the detail list frame query to be executed 1000 times. In effect, 1 (master) + 1000 (detail) requests for data are issued. By using the (DQM) Master-Detail Optimization governor in the dynamic query mode you can enhance the product performance in such situations.

To apply this type of optimization for a report, the following conditions must be met:

• The (DQM) Master-Detail Optimization governor must be enabled on a model. By default, this governor is disabled.
• The dynamic query mode must be able to apply query reuse for the detail query.
• The master-detail relationship must define links between items projected from the master and detail queries. The detail query can also have additional filters using prompts.

A report author can confirm in Cognos Analytics - Reporting if the master-detail optimization can be applied by validating the report using the Key Transformation option. The information that is returned might indicate why the optimization feature could not be enabled for the report.

The Disabled setting specifies that no detail query caching occurs. This is the default setting.

The Cache Relational Detail Query setting specifies that detail query caching occurs.

(DQM) Maximum small level size

Use this governor to define the maximum size of small level.

If the (DQM) Order Of Magnitude property of a DMR level is larger than the order of magnitude of this governor setting N (logN), the DMR level is considered as large.

The following OLAP functions against a DMR level require loading all members of that level into the DMR cube for execution:

• TopCount
• TopSum
• Toppercent
• BottomCount
• BottomSum
• BottomPercent
• Filter
• Head
• Tail

If any of these OLAP functions are applied to a large DMR level, Dynamic Query will generate SQL requests which are sent to the database and reduce the amount of data which is retrieved by Dynamic Query.

The default setting is 0.

(DQM) Summary Query Join Operator

This governor controls the syntax for joining summary queries.

The setting that is used for this governor depends on the model denoting if null values are allowed for any of the joined columns in the query.

The Is Not Distinct From setting should be used only when any of the joined columns contain null values.

The Equal operator setting should be used only when you are certain that none of the joined columns contain null values.

When the Automatic setting is used, the query service automatically assigns the Is Not Distinct From or Equal operator setting to join summary queries, depending if null values are allowed in the joined columns. This is the default setting.

During query planning, the query service might need to construct derived tables (for example, when a query retrieves data from multiple fact tables), which it joins together. When this governor is set to Automatic, the query service behaves in one of the following ways:

• When the terms in the join condition are described by the model to disallow null values, the query service uses simple equality conditions.
• When either term allows null values, a more complex predicate is used which has to take into consideration if columns include null values.

When metadata is imported into Framework Manager, the data source might describe the columns as allowing null values. By default, a distinct predicate is used to account for the potential presence of null values. If the data in the columns or expressions that are used in joins doesn’t include null values, using the equality predicate can improve performance. If null values exist, using the equality predicate could result in the join discarding rows.

The following table shows the different SQL predicates that are used by the query service depending on the governor setting.

Some database systems might use a faster execution plan when simple equality conditions are used versus the more complex predicate. In such situations, you can manually override the governor setting, if required.

If you create a new data module, and then link it to a preexisting data module, only the governor values of the new module are used. The values in the preexisting data module aren’t accessible.

In Cognos Analytics 11.x data modules, you can use the Supports NULL values property on columns to allow or disallow null values. For more information, see Object properties.

(DQM) Multi Fact Join Operator

Use this governor to control the syntax of the full outer join in Cognos SQL that is used to join multi-fact queries.

The Is Not Distinct From setting specifies that Is Not Distinct From setting should be used to join multi-fact queries.

The Equal operator setting specifies that the Equal operator setting should be used to join multi-fact queries. You should not use this setting unless you are certain that none of the columns that you are joining contain null values.

When the Automatic setting is used, the query service automatically assigns the Is Not Distinct From or Equal operator setting to join multi-fact queries, depending if null values are allowed in the joined columns. This is the default setting. For more information, see the (DQM) Summary Query Join Operator governor.

In the following example, the join between FS1 and FS2 is applied to two columns, Item_Code (not nullable), and Customer_Number (nullable). The governor is set to Is Not Distinct From.

SELECT
    COALESCE(
        FS1.Item_Code, 
        FS2.Item_Code) AS Item_Code, 
    COALESCE(
        FS1.Customer_Number, 
        FS2.Customer_Number) AS Customer_Number, 
    FS1.Order_Quantity AS Order_Quantity, 
    FS2.Plan_Sales_Quantity AS Plan_Sales_Quantity
FROM
    FS1
        FULL OUTER JOIN FS2
        ON 
            FS1.Item_Code IS NOT DISTINCT FROM FS2.Item_Code AND
            (FS1.Customer_Number IS NOT DISTINCT FROM FS2.Customer_Number)

The following example shows the same join where the governor is set to Equal operator.

SELECT
    COALESCE(
        FS1.Item_Code, 
        FS2.Item_Code) AS Item_Code, 
    COALESCE(
        FS1.Customer_Number, 
        FS2.Customer_Number) AS Customer_Number, 
    FS1.Order_Quantity AS Order_Quantity, 
    FS2.Plan_Sales_Quantity AS Plan_Sales_Quantity
FROM
    FS1
        FULL OUTER JOIN FS2
        ON 
            FS1.Item_Code = FS2.Item_Code AND
            (FS1.Customer_Number = FS2.Customer_Number)

Because Customer_Number is nullable, the output can show results where the stitch is not applied properly if Customer_Number has null values.

In the following example, the governor is set to Automatic for the same join:

SELECT
    COALESCE(
        FS1.Item_Code, 
        FS2.Item_Code) AS Item_Code, 
    COALESCE(
        FS1.Customer_Number, 
        FS2.Customer_Number) AS Customer_Number, 
    FS1.Order_Quantity AS Order_Quantity, 
    FS2.Plan_Sales_Quantity AS Plan_Sales_Quantity
FROM
    FS1
        FULL OUTER JOIN FS2
        ON 
            FS1.Item_Code = FS2.Item_Code AND
            (FS1.Customer_Number IS NOT DISTINCT FROM FS2.Customer_Number)

In this instance, Is Not Distinct From is used only when a column is nullable. This provides correct results and better performance than the default governor setting.

(DQM) Nested Aggregate Handling

Use this governor to control the syntax that defines the scope for the top-most aggregate in a calculation with nested standard aggregates.

There is a difference in behavior between compatible query mode (CQM) and dynamic query mode (DQM) regarding calculations with nested standard aggregates when the top-most aggregate does not have a defined scope.

In the following example, the calculation has nested standard aggregates.

total(
                total( m1 for X ) - total( m2 for X)
              )

The Set to Report Scope setting specifies that the top-most aggregate has a FOR REPORT scope. This is the DQM behavior.

The Remove setting specifies that the top-most aggregate is removed from the calculation. This is the CQM behavior.

The Server determined setting specifies that the top-most aggregate has a FOR REPORT scope. A server-side configuration setting might be added to this setting in a future release.

The default setting is Sever determined.

(DQM) Format Query Items For Parameter Maps

Use this governor to control the syntax that defines whether key-value pairs that are populated into parameter maps are formatted. This governor applies to parameter maps based on query items.

In IBM Cognos Analytics 11.0.0 to 11.0.7, the key-value pairs are unformatted. In the following example, the key-value pairs are unformatted.

In Cognos Analytics 11.0.8 +, the key-value pairs are formatted based on the data types of the query items. In the following example, the key-value pairs are formatted before they are populated into a parameter map.

The ON setting specifies that the key-value pairs that are populated into a parameter map are formatted.

The OFF setting specifies that the key-value pairs that are populated into a parameter map are not formatted.

The default setting is OFF.

(DQM) Empty Member Unique Name part

This governor controls how to interpret the empty part of the member unique name (MUN).

A member unique name (MUN) consists of multiple parts. For example: [gosales].[staff names].[middle initial]->[all].[M] or [middle initial]->[]. With data modules, the metadata part of the MUN often doesn’t contain the [and] separators. For example: staff.middle_initial->[J]. The empty part of the MUN appears after the arrow (->).

This governor has the following settings:

Is null

The empty part of MUN matches only null values. This is the default setting.

Is empty or null

The empty part of MUN matches both empty values and null values. This setting is used by the Cognos Analytics 11.x dashboards, and normalizes the empty and null values as a single member.

(DQM) Data Item Label Behavior

Use this governor to control how data item labels are resolved when they are not explicitly defined in the report specification. The data item labels are used for column titles in report outputs.

The data item labels are determined differently in the dynamic query mode (DQM) and compatible query mode (CQM).

In DQM, the model query item name is used as a label only for data items that are direct model references. For example, a direct model reference is a data item with the expression [Sales (query)].[Products].[Product line]. If a data item is not a direct model reference, the data item name is used for the label of that data item.

In CQM, the model query item name is used for data items that are direct and indirect model references. Otherwise, like in DQM, the data item name is used for the label of that data item.

An indirect model reference is a data item expression that references a data item in another query, and that data item is a direct model reference. For example, in the expression [Query1].[Data item name], Query2 has a data item that references a data item in Query1, which is a direct model reference.

This governor has the following settings:

Direct model references only

Specifies the behavior from DQM.

Direct and indirect model references

Specifies the behavior from CQM.

Server determined

Specifies the same behavior as Direct model references only, which is DQM behavior.

This is the default setting.