How to avoid local processing

Check list of all posts

Cognos engine is very powerful as it can handle all available functions. Some of these functions and structures could not be handled in database, such as Cognos can bring two different sets of data from two different database, one is from SQL server, the other is from SQL server. These queries must be processed locally on the Cognos server, which is called as local processing.

This local processing is not normally good for query performance for two main reasons:

1. Performing operations at the Cognos server means that you no longer have database indexes or other fancy things like materialization to help speed up the query processing.
2. In order to perform most operations with local processing you will have to retrieve a larger, more detailed, result set from the data source. Fetching large result sets will slow things down and processing of these large result sets at the Cognos server will impair performance.

There are two ways to identify local processing:

1. Comparing Cognos SQL and native SQL

Retrieve both Cognos SQL and native SQL from report studio

Retrieve both Cognos SQL and native SQL from Framework manager

If the logic between Cognos SQL and native SQL is different, then local processing is involved. More importantly, we can figure out what data is retrieved from database.

2. Change local limited to Database only

Change it from report studio

Change it from framework manage

r

Running report in report studio, or test query in framework manager, if we get error message as below, then local processing is involved.

<UDA-SQL-0219 the function <function name > is used for local processing but is not available as a built-in function, or at least one of its parameters is not supported>

• Using not supported functions or operations

There are two major Cognos functions: AT and PREFILTER, which are not supported by any database functions. We should avoid it unless we have to use it.

• Joining queries from separate databases

There is one article from best practice with detail explanation working with Multiple Relational Data Sources.

Two major cases are discussed below

Working with single instance of database vendor

In this case, we need to define content manager data source to point to instance, then define data source in framework manager to point to the same content manager data source. The data source name in framework manager can be different. The relationship chain is:  Data source in framework manager, note that scheme and category can be changed with the same connection to point to different sets of data.

Data source in Cognos connection, there are three levels of configurations here, and logic database connection under Cognos can have multiple physical connections as well, while each physical connection can have multiple sign-on. 

Working with multiple database instance or vendors

The main solution is to consider using ETL or VVM (Virtual view manager), in order to bring data together.

Another thought is to bring data at report level to mimic the stitched query concept in case when fact data is from two different areas. In other word, we can bring summarized data from each data sources, and then join it at Cognos server using local processing. The performance should be reasonable good, as the record of summarized data is very small.

Joining queries from same database with different logical data sources

From whatever reasons, there could be more than one database connections in Cognos. In other word, there are more than one content manager data sources that are point to the same database. This could end up a huge impact on performance, as BI server will consider each logic content manager data source as separated sources. Even two query items, or queries point to the same database, it will use local processing to get data separated into Cognos server, and then join them together.