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.
