Explanation of Data Source Properties in Cognos Framework Manager

Query Processing
This property decides where query processing to be done when a report is executed. Query processing in Cognos is of ‘2’ types:

a.       Limited Local.

b.      Database Only.  

When Query Processing property set to “Limited Local”, most of the query processing and execution is done on the database server however, some queries or part of the queries are processed locally on the Cognos server. It means, data is extracted from the database into the Cognos server memory and all the operations like filtering, sorting, arithmetic operations, calculations and logical expressions are done locally on the Cognos server. Performing these operations locally does not effectively utilize database capabilities and may cause performance issues. In order to overcome this, always set the Query Processing property to “Database Only”.

When the Query Processing property is set to “Database Only”, all the query processing and execution is done on the underlying database with maximum utilization of indexes, partitions and materialized objects.

In some cases, though the Query Processing is set to “Database Only”, you may get an error message to apply “Limited Local” processing. It usually happens because of the following reasons:

-          When we use functions in the report that are not supported by the database.

-          When a report is built from different Databases/Schemas.

-          When multiple fact tables are used in a single report.

Note: “Limited Local” processing is recommended if you have cross database joins or unsupported SQL99 functions.  Also, some complex queries may need “limited Local” processing that generate “AT” clause to avoid double counting.

Note: For SQP BW, “Limited Local” processing is not supported in DQM.

Rollup Processing

This property determines whether aggregate rollups above the lowest level are computed locally (or) in the database. Rollup processing in Cognos is of ‘4’ types:

a.       Local Processing

b.      Database Processing.

c.       Extended Processing.

d.      Unspecified.

Rollup Processing when set to “Local Processing”, all the auto grouping and summarization happens locally on the Cognos Server using running aggregate (RSUM). Detail data is loaded from the database into the Cognos server and auto grouping and summarization happens locally in the Cognos server. This option is not recommended and may slow down the performance of a query.

Rollup Processing when set to “Database Processing”, all the auto grouping and summarization happens on the Database server. This option enhances query performance.

Rollup Processing when set to “Extended Processing”, all the auto grouping and summarization happens on the Database server using an extended aggregate (XSUM) but, this option is very effective especially to utilize the capabilities of materialized views.

Rollup Processing when set to “Unspecified”, Cognos may perform rollups either locally or in the database depending upon the report type.

Note: rollup processing is not applicable to SAP BW data sources.

Transaction Access Mode

This property decides whether the transaction access mode is set to “Read-Only” (or) “Read-Write” for a query subject that is created from a stored procedure. By default, this property is set to “Unspecified” but runs in “Read-Only” transaction mode. We can leverage this property to set either “Read-Only” (or) “Read-Write”.

In some cases, a query subject with data modification stored procedure requires “Read-Write” access. However, if you set the Transaction Access Mode property to “Read-Only” in Cognos Framework Manager, you may get following error message.

XQE-PLN-0309 The [Namespace].[data_Modification_Stored_ProcedureName] data modification query subject requires that the transaction access mode property is set to Read-Write on the [read_Only_Model_Data_Source] model data source.

For all other query subjects, JDBC driver’s default Transaction Access Mode is used.

Note: Transaction Access Mode is supported only for the query subjects that are created from stored procedure.

Transaction Statement Mode

This property specifies the action to be taken when a transaction ends. By default, this property is set to “Unspecified” but the default action happens is “Rollback”. If the database supports “Autocommit”, then the default action is “Commit”.

-              Unspecified - Specifies that the default action taken when a transaction ends.

-              Rollback - a transaction is rolled back when it ends.

-              Commit - a transaction is committed when it ends.

-              Autocommit - autocommit is carried out when a transaction ends.

Note: this property is ignored by DQM (Dynamic Query Mode).

Content Manager Data Source

The data source used to import all the metadata objects into the Cognos Framework Manager model.

Catalog

This property contains a name of the catalog from where the metadata objects are imported. A catalog is usually a collection of schemas. Each schema contains set of tables, views, procedures, functions etc.

Schema

This property contains the name of schema from where the metadata objects are imported. A schema is usually a collection of tables, views, procedures, functions etc. 

 






















Query Type

This property decides the type of query model that an underlying data source can understand. A SQL source will have a property set to “Relational”. An MDS source will have a property set to 'Multidimensional'. A SAP BW data source will also have a property set to 'Multidimensional'. Do not change this value.

Interface: this property is reserved for internal use. It is maintained by the application. Do not change this value.

Function Set ID

This property defines the function sets available to report authors. Usually, these are the same as those of the data sources used. You can also modify the function set list using the Project Function List command from the Project menu.