What is the difference between CQM and DQM
The original query engine used by Cognos was written in C++ is known as Universal Data Access (UDA). The modern query engine was written in Java and is powered by the eXtensible Query Engine (XQE). The report service (RSVP) is a rendering engine that uses a report specification and metadata (Package) to generate the desired output. RSVP will route data requests depending on the package setting to the appropriate query engine. A setting of Compatible Query Mode (CQM) will use UDA and Dynamic Query Mode (DQM) will use XQE.
UDA is a 32-bit application which limits its addressable memory and its architecture cannot easily adapt to new trends in data access methodologies. XQE was created to address these limitations and to address performance in data access.
UDA uses native database drivers or ODBC and XQE uses JDBC database drivers.
It is highly recommended to start all new projects with DQM.
Truths to consider
- There are no plans to deprecate CQM
- DQM is being actively enhanced for performance and support of new data sources
- DQM will enforce best practices
- DQM may or may not improve performance
- Changing from CQM to DQM may or may not require changes in the package or report
- DQM provides a more consistent query behavior
- DQM does not support many to many relationships without a bridge table
- DQM may apply a different join path in the generated query
Migrating from CQM to DQM
A compelling argument to consider a migration is to support modernization of technology. As containerization is adopted or cloud adoption is embraced, those technologies only support DQM. When considering which Cognos Analytics environment to perform the conversion it is recommended to use the latest version as known issues related to conversion will have been addressed in current releases.
Steps to consider include administration, metadata and testing the reports.
Administration includes tasks like increasing the memory allocation for the query service and confirming that the relevant data sources have the JDBC drivers, configuration added and tested. Additional information can be found in the Installing and Configuring documentation.
Metadata is the package as published from the model which is created using Framework Manager. Using Framework Manager, change the query mode and verify the model to confirm compatibility of the design. Example: many to many relationships are no longer supported without a bridge table. Additional information on model settings specific to DQM can be found in the FM documentation.
If you have reports referencing packages that have been developed in CQM they can be converted and tested using a few different approaches.
- Publish the package(s) as DQM and test all reports, this works well if the count of reports is low keeping the testing manageable
- Publish the package(s) as DQM in a new location and repoint the reports as you test them, this works well if you want to segregate the testing by groups
- Use one of the automated testing tools with the flag to enable testing as DQM. This will allow for the packages and reports in the environment to remain as they are, and the testing process will use DQM on the backend
- Lifecycle Manager, CA 11.1.4
- Motio CI version 3.2.7
Due to the complexity of the changes in the query engine and the ability to create complex models and reports with varying techniques it is impossible to predict the results or failure rate of this type of conversion. Customers have experienced a wide range from few to many failures after conversion. The good news is that typically there is a pattern discovered which allows bulk remediation. Most issues identified can be categorized as not following best practices. An example would be using database functions instead of an equivalent Cognos function or using a relational expression in a dimensional layer. Most remediation occurs in expressions or filters.