Governors

Use governors to reduce system resource requirements and improve performance. You set governors before you create packages to ensure the metadata in the package contains the specified limits. All packages that are subsequently published use the new settings.

The governor settings that take precedence are the ones that apply to the model that is currently open (whether it is a parent model or a child model).

In a new project the governors do not have values defined in the model. You must open the Governors window and change the settings if necessary. When you save the values in the Governors window by clicking OK, the values for the governors are set. You can also set governors in Reporting. The governor settings in Reporting override the governor settings in the model.

For information about the governors specific to dynamic query mode (DQM), see Dynamic query mode governors.

Maximum Number of Report Tables

You can control the number of tables that a user can retrieve in a query or report. When a table is retrieved, it is counted each time it displays in the query or report. The limit is not the number of unique tables. If the query or report exceeds the limit set for the number of tables, an error message displays and the query or report is shown with no data.

A setting of zero (0) means no limit is set.

Note: This governor is not used in dynamic query mode.

Maximum Number of Retrieved Rows

You can set data retrieval limits by controlling the number of rows that are returned in a query or report. Rows are counted as they are retrieved.

When you run a report and the data retrieval limit is exceeded, an error message displays and the query or report is shown with no data.

You can also use this governor to set limits to the data retrieved in a query subject test or the report design mode.

A setting of zero (0) means no limit is set.

If you externalize a query subject, this setting is ignored when you publish the model. For more information, seeExternalized query subjects and dimensions.

Query Execution Time Limit

You can limit the time that a query can take. An error message displays when the preset number of seconds is reached.

A setting of zero (0) means no limit is set.

Note: This governor is not used in dynamic query mode.

Large Text Item Limit

You can control the character length of BLOBs (binary large objects) that a user can retrieve in a query or report. When the character length of the BLOB exceeds the set limit, an error message displays, and the query or report is shown with no data.

A setting of zero (0) means no limit is set.

Outer Joins

You can control whether outer joins can be used in your query or report. An outer join retrieves all rows in one table, even if there is no matching row in another table. This type of join can produce very large, resource-intensive queries and reports.

Governors are set to deny outer joins by default. For example, outer joins are not automatically generated when you test a query item in Framework Manager.

SQL is generated automatically when you

  • Run a report.
  • Test a query item or relationship in Framework Manager.
  • Create a new model query subject based on other objects.

If you keep the setting as Deny, you are notified only if you create a relationship in the Diagram tab that includes outer joins. You are not notified if you create a relationship in a data source query subject that includes outer joins.

If you set the governor to Allow, dimension to fact relationships are changed from inner joins to outer joins.

The outer joins governor does not apply in these circumstances:

  • SQL that is generated by other means. If you set this governor to Deny, it does not apply to the permanent SQL found in a data source query subject, whether the SQL was generated on import, manually entered, or based on existing objects.
  • Framework Manager needs to generate an outer join to create a stitched query. A stitched query is a query that locally combines the results of two or more sub-queries by using a locally processed outer join.
Note: This governor is not applicable for SAP BW data sources.
Note: This governor is not used in dynamic query mode.

Cross-Product Joins

You can control whether cross-product joins can be used in your query or report. A cross-product join retrieves data from tables without joins. This type of join can take a long time to retrieve data.

The default value for this governor is Deny. Select Allow to allow cross-product joins.

Shortcut Processing

You can control how shortcuts are processed by IBM Cognos software.

When you open a model from a previous release, the Shortcut Processing governor is set to Automatic. Automatic is a shortcut that exists in the same folder as its target and behaves as an alias, or independent instance. However, a shortcut existing elsewhere in the model behaves as a reference to the original. When you create a new model, the Shortcut Processing governor is always set to Explicit.

If you set the governor to Explicit, the shortcut behavior is taken from the Treat As property. If the Shortcut Processing governor is set to Automatic, verify the model and, when repairing, change the governor to Explicit. This changes all shortcuts to the correct value from the Treat As property based on the rules followed by the Automatic setting.

The Shortcut Processing governor takes priority over the Treat As property. For example, if the governor is set to Automatic, the behavior of the shortcut is determined by the location of the shortcut relative to its target regardless of the setting of the Treat As property is.

SQL Join Syntax

You can control how SQL is generated for inner joins in a model by selecting one of the following settings:

  • If the governor is set to Server determined, the CQEConfig.xml file is used to determine the governor value. If there is no active CQEConfig.xml file or no parameter entry for the governor in the CQEConfig.xml file, then the Implicit setting is used.
  • The Implicit setting uses the where clause.

    For example,

    SELECT publishers.name, publishers.id,
books.title FROM publishers, books WHERE publishers.id
= books.publisher_id ORDER BY publishers.name, books.title;
  • The Explicit setting uses the from clause with the keywords inner join in an on predicate.

    For example,

    SELECT
publishers.name, publishers.id,
books.title FROM publishers INNER JOIN books ON publishers.id
= books.publisher_id ORDER BY publishers.name, books.title;

You can set the join type on the query property in Reporting to override the value of this governor.

Regardless of the setting you use for this governor, the Explicit setting is used for left outer joins, right outer joins, and full outer joins.

This governor has no impact on typed-in SQL.

Grouping of Measure Attributes (query items)

If the governor is set to Server determined, the CQEConfig.xml file is used to determine the governor value. If there is no active CQEConfig.xml file or no parameter entry for the governor in the CQEConfig.xml file, then the Disabled setting is used.

The Disabled setting prevents aggregation of the measure for the attributes. This is the default behavior. For example, 

select Product.Product_line_code as Product_line_code,
Order_method.Order_method_code as Order_method_code,
XSUM(Sales.Quantity for Product.Product_line_code) as Quantity from ...

The Enabled setting allows aggregation of the measure for the attributes. Note: This is the default behavior for IBM Cognos Framework Manager versions prior to 8.3. 

select Product.Product_line_code as Product_line_code,
Order_method.Order_method_code as Order_method_code,
XSUM(Sales.Quantity for Order_method.Order_method_code,
Product.Product_line_code)
as Quantity
from ...

In these examples, Order_method.Order_method_code as Order_method_code defines the measure attribute.

The XSUM syntax defines the aggregated measure.

SQL Generation for Level Attributes

You can control the use of the minimum aggregate in SQL generated for attributes of a level (member caption).

If the governor is set to Server determined, the CQEConfig.xml file is used to determine the governor value. If there is no active CQEConfig.xml file or no parameter entry for the governor in the CQEConfig.xml file, then the Minimum setting is used.

The Minimum setting generates the minimum aggregate for the attribute. This setting ensures data integrity if there is a possibility of duplicate records. For example, 

select XMIN(Product.Product_line
for Product.Product_line_code) as Product_line, //level attribute
Product.Product_line_code as Product_line_code
from
(...) Product

The Group By setting adds the attributes of the level in the group by clause with no aggregation for the attribute. The distinct clause indicates a group by on all items in the projection list. The Group By setting is used if the data has no duplicate records. It can enhance the use of materialized views and may result in improved performance. For example, 

select distinct
Product.Product_line as Product_line,//level attribute
,Product.Product_line_code
as Product_line_code
from(...) Product
Note: This governor is not used in dynamic query mode.

SQL Generation for Determinant Attributes

You can control the use of the minimum aggregate in SQL generated for attributes of a determinant with the group by property enabled.

If the governor is set to Server determined, the CQEConfig.xml file is used to determine the governor value. If there is no active CQEConfig.xml file or no parameter entry for the governor in the CQEConfig.xml file, then the Minimum setting is used.

The Minimum setting generates the minimum aggregate for the attribute. This setting ensures data integrity if there is a possibility of duplicate records. For example, 

select PRODUCT_LINE.PRODUCT_LINE_CODE 
as Product_line_code,
XMIN(PRODUCT_LINE.PRODUCT_LINE_EN 
for PRODUCT_LINE.PRODUCT_LINE_CODE)
as Product_line //attribute 
from
great_outdoors_sales..GOSALES.PRODUCT_LINE PRODUCT_LINE
group by
PRODUCT_LINE.PRODUCT_LINE_CODE  //key

The Group By setting adds the attributes of the determinants in the group by clause with no aggregation for the attribute. This setting is used if the data has no duplicate records. It can enhance the use of materialized views and may result in improved performance. For example, 

select
PRODUCT_LINE.PRODUCT_LINE_CODE as Product_line_code,
PRODUCT_LINE.PRODUCT_LINE_EN as Product_line //attribute
from
great_outdoors_sales..GOSALES.PRODUCT_LINE PRODUCT_LINE
group by 
PRODUCT_LINE.PRODUCT_LINE_CODE //key
PRODUCT_LINE.PRODUCT_LINE_EN //attribute

SQL Parameter Syntax

This governor specifies whether generated SQL uses parameter markers or literal values.

If the governor is set to Server determined, the CQEConfig.xml file is used to determine the governor value. If there is no active CQEConfig.xml file or no parameter entry for the governor in the CQEConfig.xml file, then the Marker setting is used.

You can override the value of this governor in Reporting.

Dynamic SQL applications have the ability to prepare statements which include markers in the text which denote that the value will be provided later. This is most efficient when the same query is used many times with different values. The technique reduces the number of times a database has to hard parse an SQL statement and it increases the re-use of cached statements. However, when queries navigate larger amounts of data with more complex statements, they have a lower chance of matching other queries. In this case, the use of literal values instead of markers may result in improved performance.

Note: This governor is not used in dynamic query mode.

Allow Enhanced Model Portability at Run Time

This governor is selected upon initial upgrade of a Cognos ReportNet 1.x model. It prevents rigid enforcement of data types so that an IBM Cognos model can function as a ReportNet 1.x model until you update the data types in the metadata. After you have verified that the model has been upgraded successfully, clear this governor.

Other than for initial upgrade, there are limited uses for this governor. For example, you have created a model for use with a data source and you want to run it against a different data source. The new data source must be structurally similar to the original data source, and the database schema must be the same between the two data sources. If you select this governor, IBM Cognos Analytics retrieves metadata from the data source and caches it instead of using the metadata already cached in the model. When you have completed modifying and testing the model against the new data source, clear this governor.

If you do not use this governor, you must ensure that the following metadata is the same in the original and new data sources:

  • Collation sequence name
  • Collation level
  • Character set
  • Nullability
  • Precision
  • Scale
  • Column length
  • Data type

Allow Usage of Local Cache

Select this governor to specify that all reports based on this model will use cached data. For a new model, this governor is enabled by default.

This setting affects all reports that use the model. Use Reporting if you want a report to use a different setting than the model. For more information, see Improving performance by reusing cached data when running a report.

Allow Dynamic Generation of Dimension Information

This governor is selected only upon initial upgrade of a ReportNet 1.x model. This governor allows consistent behavior with ReportNet 1.x by deriving a form of dimension information from the relationships, key information, and index information in the data source.

Use With Clause When Generating SQL

You can choose to use the With clause with IBM Cognos SQL if your data source supports the With clause.

The With clause is turned on for models created in IBM Cognos Analytics. For upgraded models, it is turned off unless it was explicitly turned on in the Cognos ReportNet model prior to upgrading.

Suppress Null Values for SAP BW Data Sources

You can control whether or not nulls are suppressed by any report or analysis that uses the published package. The governor is also applied to test results during the current Framework Manager session. It is supported for SAP BW data sources only.

Some queries can be very large because null values are not filtered out. Null suppression removes a row or column for which all of the values in the row or column are null (empty). Null suppression is performed by SAP BW. This reduces the amount of data transferred to the IBM Cognos client products and improves performance.

By default, nulls values are suppressed. If you clear this governor, null values are not suppressed.

There is a property called Suppress in Reporting that overrides this governor. If the Suppress property is set to None, null values are included in the result set even if the governor is set to suppress null values.

Note: This governor is not applied when creating CSV files; therefore, CSV files include null values if they exist in the data.

Process Entire Model When Publishing

A published package includes the model objects selected when the package was created. In addition, those model objects are analyzed in order to identify and include dependent objects in the package.

In a complex or very large model, the analysis can take considerable time. To shorten the publish time, set this governor to skip this analysis step and have the entire model written to the content store. The resulting package may be larger because the entire model is published instead of only required objects, however the time required to publish should be reduced.

Maximum external data sources that can be merged with a model

To use external data, report users import their data into an existing package. This governor controls the number of external data files that can be imported.

The default is 1.

For more information about external data sources, see the IBM Cognos Analytics - Reporting User Guide.

Maximum external data file size (KB)

To use external data, report users import their data into an existing package. This governor controls the size of each external data file.

By default, the maximum file size that report users can import is 2560 KB.

For more information about external data sources, see the IBM Cognos Analytics - Reporting User Guide.

Maximum external data row count

To use external data, report users import their data into an existing package. This governor controls the number of rows that can exist in each external data file.

By default, the maximum number of rows that report users can import is 20000.

For more information about external data sources, see the IBM Cognos Analytics - Reporting User Guide.