CQM to DQM – Why the Transition Isn’t as Hard as You Think

by Oct 28, 2019Blog, Business Analytics, Business Intelligence, Cognos Analytics, MotioCI0 comments

Transitioning from CQM to DQM. It’s a hot topic, and one we have yet to discuss.

Not dissimilar to a Cognos upgrade, you are looking into switching to Dynamic Query Mode because it supports new database types, enhances performance, and is required for Cognos on the Cloud! But of course, the newest, best technology is worthless if your reports don’t run and/or the data is inaccurate.

When you introduce a major update to your Cognos platform, such as migrating to DQM, things can go poorly just as easily as they can go well. This scale illustrates the range of  possibilities:

Well, you may have guessed it — Motio has a solution to make the transition smoother, and I’m here to tell you all about it. A special thank you to the MotioCI developers, who walked me through the process.

The hero of today’s story is MotioCI’s Force Query Mode, which allows you to see the results of converting a package to Dynamic Query Mode before you actually upgrade. This shares what will be affected by the DQM conversion and gives you the opportunity to make any necessary repairs before you go live. All of this is accomplished without impacting your live reports or republishing from Framework Manager.

In this example, we will be checking how this Human Resource package in our content store will perform in DQM before actually converting it.

To test the Human Resource model package in Dynamic Query mode, you will need to create a separate test project and call it “Human Resource DQM.” You will then set up your test cases and assertions in the project wizard.


  1. Go to the “Project Settings” tab and configure the package query mode as “Force Dynamic.” This setting will execute and test the reports from this project in the dynamic query mode.

One of the riskiest outcomes of moving to DQM is when your reports have inaccurate data after converting (remember our “Reward to Risk” scale above?) Let’s use the “Output Comparison” assertion in MotioCI on one of our reports to test and compare the report output in Dynamic Query Mode to the output in Compatible Query Mode.

Click on a report (we used “Employee Details” in this example) and select the “test case” icon.

Select “Add” under the assertions and choose “Data Validation” and then “Output Comparison.”

Then be sure and add the “Human Resource” project under the “Project Name Prompt – Value Prompt Step” to compare the outputs from the DQM of this report to the CQM of the same report.

Next, you will run the test case:
The test case results in a failure and shows a visual and textual difference in the outputs from CQM to DQM.


  1. As we can see, the data returned is not the same.
    We will have to look into what is causing this. It could be that a sort gets applied, the data returned might be get filtered differently, or something else. It’s really that simple! MotioCI will help you uncover whether your reports will behave differently in DQM than CQM. If something is not going to perform correctly, use the results from MotioCI as a jumping point to investigate deeper into your Cognos configuration without a sandbox, republishing the package, or interrupting access to live reports! Want to learn more? We hosted a webinar where we discussed the challenges faced by Performance Group in their migration to Dynamic Query Mode. Reply the webinar by clicking here.

 

BlogCognos AnalyticsCognos PerformanceCognos TroubleshootingReportCard
Cognos Analytics Auditing Blog
Cognos Auditing Blog – Tips and Tricks for Large & High Volume Environments

Cognos Auditing Blog – Tips and Tricks for Large & High Volume Environments

A blog by John Boyer and Mike Norris. Introduction It is important to have the Cognos Auditing capability working to know and understand how Cognos is being used by your user community and help answer questions like: Who is using the system? What reports are they...

Read More