DQM

Dynamic Query Mode

New feature in 10.2:

When you create a new project in IBM® Cognos® Framework Manager, the new project now uses dynamic query mode by default in 10.2.

Overview of the IBM Cognos 10 Dynamic Query Mode

The Dynamic Query Mode is an enhanced Java-based query mode which offers the following key capabilities:

• Query optimizations to address query complexity, data volumes and timeliness expectations with improved query execution techniques

• Significant improvement for complex OLAP queries through intelligent combination of local and remote processing and better MDX generation

• Support for relational databases through JDBC connectivity

• OLAP functionality for relational data sources when using a dimensionally modeled relational (DMR) package

• Security-aware caching

• New data interfaces leveraging 64-bit processing

• Ease of maintenance with query visualization

Architecture of Dynamic Query Mode

The Dynamic Query Mode server accepts data and metadata requests (via the Report Service server) from BI studios (such as Report Studio), the Report Viewer and other clients. It returns the requested data and or messages in a structured response to the Report Service, which formats the result for the client.

Dynamic Query Mode (DQM) is an intelligent data caching mechanism which can dramatically improve the performance of OLAP style queries. With the prior version, DQM supported cubes built from TM1, Essbase and SAP BW. With version 10.1.1, IBM Cognos adds support for Microsoft SQL Server Analysis cubes, and the most common relational databases, specifically DB2, Netezza, SQL Server, Teradata and Oracle.

Prior to DQM, cube base queries, issuing MDX code, could suffer from poor, or uneven performance. This was especially noticeable in cases where suppression, such as null suppression, was turned on. DQM produces a sharable data caches, including plan caches, and also optimizes database queries. This resulting in quicker, more even queries, particularly noticeable in an ad-hoc query environment. It also retains user-based security restrictions.

DQM over relational data sources is only supported for Dynamically Modeled Relational (DMR) packages. Package not published with DQM turned on, will execute in the traditional manner, which is now called Compatibility Mode, to distinguish it from Dynamic Query Mode.

In order to enable DQM there are a few specific steps.

1. DQM requires JDBC drivers for relational data sources and required database clients for any OLAP data sources. The JDBC drivers must be copied to the Cognos environment. See the IBM Cognos Administration and Security Guide for details. You may need to restart the IBM Cognos services after installing the drivers.

1. For the Relational data sources, you must enable the JDBC data connection. In the data connection configuration, select
   Enable JDBC Connection as shown.

1. In Framework manager, when you publish the package, select the checkbox indicating Dynamic Query Mode, as shown. This enables DQM for that package.
2. 

As another option, when starting a new Framework Manager project which will be used only with DQM enabled data sources, you can enable the entire project to use DQM, as shown. All testing and publishing will use DQM by default.

Testing and Monitoring

IBM Cognos Lifecycle Manager is a great tool for comparing report results with and without DQM. In addition to highlighting any potential differences, it can also compare execution times

Dynamic Query Mode over relational data sources has the potential to be a game-changer. There are certainly some challenges, which are only touched on here. But in the right environment, there is the potential for huge performance gains.

What is the right environment? While it’s difficult to give hard-and-fast rules, I would look for a well-designed dimensional data warehouse as a data source. This lends itself to DMR style data modeling and reports. Also, what is the data refresh cycle? Frequently updated data will benefit less from data caching than data which is more static in nature. Also the number and type of queries issues is a consideration. Are there a large number of ad-hoc, analysis style queries? Repeated analysis is more likely to see benefits than simply running canned reports on a periodic basis.