Whether you’re considering moving to IBM Cognos Analytics on the Cloud, wanting to use JDBC drivers instead of a native database client, or just wanting greater visibility into performance-related characteristics of queries, adopting Dynamic Query Mode is a great idea.
When Performance Food Group, one of the most trusted leaders in the foodservice industry recently upgraded from Cognos 10.2.1 to 11.0.12 as part of their continuous improvement process, they decided they would update their packages from CQM to DQM as well. Sumit Kumar, PFG’s IT manager responsible for overseeing reporting, analysis, and consulting, was in charge of their package migration and tasked with overcoming any challenges they may encounter during this process.
Benefits Of Converting From CQM To DQM
Performance Food Group’s reasons for migrating were numerous. One of the main benefits of converting for Sumit and PFG as a whole was the ability to use reports that contain data from multiple packages within that same report. Dynamic Query Mode would allow Sumit to use reports that contained data from multiple subject areas such as sales, procurement, and inventory, even though they’re located in three completely different packages. Compatible Query Mode does not have this capability, so the choice was obvious.
Converting from Compatible Query Mode to Dynamic Query Mode would also afford them the ability to greatly reduce report execution time by leveraging the query execution on 64-bit architecture. By migrating, Sumit knew they were not only laying the infrastructure to make future upgrades easier but also giving Performance Food Group the ability to initiate predictive analytics in their automatization.
The benefits of converting were obvious, but what challenges lied ahead?
After choosing 13 Cognos packages to be converted, Sumit encountered his first obstacles during the project planning and execution phase.
Project Planning and Execution Challenges
The first roadblock Sumit faced was choosing between Waterfall or Agile Delivery. Sumit chose the latter for his CQM to DQM conversion because it allowed him to deploy each package independently. Packages were deployed when all the important reports ran successfully and if some low-priority reports had errors, they deployed the package anyway and fixed the reports later. This allowed them to deliver business value upfront without losing any time, but to play it safe they kept a one month buffer in case they needed additional help from the IBM product support team.
Now that Sumit and Performance Food Group had overcome the project planning and execution phase, it was time for them to tackle the next issue: technical and infrastructure challenges due to the behavior of the packages in Dynamic Query Mode.
“According to Sumit, converting from CQM to DQM was time and effort very well spent. After the conversion, report execution time was reduced by 60% on average!”
Technical and Infrastructure Challenges
Dynamic Query Mode enforces best practices that were optional in Compatible Query Mode. An example of this would be the use of hyphens and asterisks with a forward slash as a commented line, for example, ‘-‘ vs ‘/*’. CQM accepts all of these whereas DQM sometimes accepts it and sometimes doesn’t, depending on placement. These seemingly small issues can cause sporadic errors or even entire reports to fail. Comments in advanced filters, SQL query and custom calculations were also known to cause errors. A Compare sql query tool was considered to format placement and reduce the occurrence of errors from that area but the investigation went further in order to see all error occurrences. Inclusion of a sum function in the data model or package definition also created errors, but this can be remedied by replacing it with total function instead, or Sum() vs Total().
Dynamic Query Mode also makes certain assumptions that Compatible Query Mode does not, causing report outputs to differ. Running reports in CQM vs DQM may give you different results solely based on how they interpret the function. For example, the Total(Total(Sales)) in CQM will give you a result which is equivalent to total sales and ignores the duplicate total whereas in DQM it doesn’t ignore the duplicate total, giving you a different report output. Similarly, the way aggregation options are implemented in CQM vs DQM also varies. The filters on Calculated/Aggregate columns can lead to different results depending on aggregate property selection, such as “Before Aggregation” or “After Aggregation”.
Dynamic Query Mode may apply a different order of operations that can cause changes in the report output.
- Report level cardinality definition leads to changes in the report output.
- Report validation can still show critical errors even after the warning messages are resolved. All warning messages must be fixed before the report compiler can show you the actual error. If a report isn’t running and only shows a warning message, you have to fix the warning message first before the report will reveal the critical error and allow you to fix it.
- Rendering reports with huge amounts of data may fail with “Java out of memory” issues but can be fixed by disabling the local caching property in the report properties for those reports, and can also be aided by increasing the configured memory for the query service
- JVM configuration has to be fine-tuned according to best practices in order to prevent future problems.
According to Sumit, the migration process was time and effort very well spent. After the conversion, report execution time was reduced by 60% on average! He definitely recommends converting packages from CQM to DQM and your environment from 32-bit to 64-bit.
Considering converting from Compatible Query Mode to Dynamic Query Mode or recently converted? We’d love for you to share your experience with us or any tips and tricks you may have!