Contact Us
Partner Portal
Help Desk

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

Transition to DQM

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.
Ready to streamline and automate your BI & analytics platforms?
Get A Demo
Solutions
Products
About Us
Resources
Contact
  • Tel:1 (972) 447-9595
  • Toll Free:1 (833) 855-3073
Follow Us
Youtube Linkedin Twitter
© 2024 Motio. All rights reserved
Scroll to Top
As the BI space evolves, organizations must take into account the bottom line of amassing analytics assets.
The more assets you have, the greater the cost to your business. There are the hard costs of keeping redundant assets, i.e., cloud or server capacity. Accumulating multiple versions of the same visualization not only takes up space, but BI vendors are moving to capacity pricing. Companies now pay more if you have more dashboards, apps, and reports. Earlier, we spoke about dependencies. Keeping redundant assets increases the number of dependencies and therefore the complexity. This comes with a price tag.
The implications of asset failures differ, and the business’s repercussions can be minimal or drastic.
Different industries have distinct regulatory requirements to meet. The impact may be minimal if a report for an end-of-year close has a mislabeled column that the sales or marketing department uses, On the other hand, if a healthcare or financial report does not meet the needs of a HIPPA or SOX compliance report, the company and its C-level suite may face severe penalties and reputational damage. Another example is a report that is shared externally. During an update of the report specs, the low-level security was incorrectly applied, which caused people to have access to personal information.
The complexity of assets influences their likelihood of encountering issues.
The last thing a business wants is for a report or app to fail at a crucial moment. If you know the report is complex and has a lot of dependencies, then the probability of failure caused by IT changes is high. That means a change request should be taken into account. Dependency graphs become important. If it is a straightforward sales report that tells notes by salesperson by account, any changes made do not have the same impact on the report, even if it fails. BI operations should treat these reports differently during change.
Not all reports and dashboards fail the same; some reports may lag, definitions might change, or data accuracy and relevance could wane. Understanding these variations aids in better risk anticipation.

Marketing uses several reports for its campaigns – standard analytic assets often delivered through marketing tools. Finance has very complex reports converted from Excel to BI tools while incorporating different consolidation rules. The marketing reports have a different failure mode than the financial reports. They, therefore, need to be managed differently.

It’s time for the company’s monthly business review. The marketing department proceeds to report on leads acquired per salesperson. Unfortunately, half the team has left the organization, and the data fails to load accurately. While this is an inconvenience for the marketing group, it isn’t detrimental to the business. However, a failure in financial reporting for a human resource consulting firm with 1000s contractors that contains critical and complex calculations about sickness, fees, hours, etc, has major implications and needs to be managed differently.

Acknowledging that assets transition through distinct phases allows for effective management decisions at each stage. As new visualizations are released, the information leads to broad use and adoption.
Think back to the start of the pandemic. COVID dashboards were quickly put together and released to the business, showing pertinent information: how the virus spreads, demographics affected the business and risks, etc. At the time, it was relevant and served its purpose. As we moved past the pandemic, COVID-specific information became obsolete, and reporting is integrated into regular HR reporting.
Reports and dashboards are crafted to deliver valuable insights for stakeholders. Over time, though, the worth of assets changes.
When a company opens its first store in a certain area, there are many elements it needs to understand – other stores in the area, traffic patterns, pricing of products, what products to sell, etc. Once the store is operational for some time, specifics are not as important, and it can adopt the standard reporting. The tailor-made analytic assets become irrelevant and no longer add value to the store manager.