Fixing Slow DQM Reports in IBM Cognos
Recently we deployed an updated dynamic query mode (DQM) reporting package containing a new fact table called Fact_Credit_Transaction to a customer. We noticed that some active reports slowed down significantly as a result. The changes to the package were all additive, there were no alterations to the existing content so there should have been no impact on these reports.
Profiling the queries on the server showed that the reports were using the new fact table to join Dim_Location and Dim_Date despite there being no reference to the fact table in the report. Since Fact_Credit_Transaction contains millions of records this was adding a significant overhead to the report processing. Investigating further it became clear that Cognos was using the fact table as a result of a calculated join path for a fact-less query. In Cognos when using DQM any fact-less query (a query referencing two or more dimensions without explicitly including anything from a fact table) will have a fact table added to the query automatically in order to provide the join path between the dimensions.
Further investigation lead to the "(DQM) Context Sensitive Join Selection" governor which has 3 possible settings - Disabled, Automatic or Explicit. IBM explains how each setting affects the join path at https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg...
In our project we were using the Disabled setting which according to IBM means "that the star schema grouping is ignored when the join path of the main query is computed. If there are multiple join paths, the first alphabetically sorted one is chosen". In theory this sounds quite simple, the reality is slightly more complicated:
1) It's obvious once you think about it but the first point to note is that Cognos will only consider joining through fact tables with relationships defined to all the dimensions used in the query
2) The second point to note is that Cognos will translate the query to the lowest level of the model (the database layer in a standard Cognos framework) - it's these objects that Cognos sorts alphabetically for consideration
3) The final point is that it's not the query subjects that Cognos considers in alphabetical order when deciding which one to use, it's actually the relationships/joins that are considered alphabetically
Our problem stemmed from the relationships for Fact_Credit_Transaction being alphabetically ahead of the relationships for a significantly more efficient join path using Fact_Summary. By renaming the relationships for the Fact_Credit_Transaction query subject to appear at the end of an alphabetical list we were able to control the join path that Cognos calculated and return the active reports to the previous swift production times.
Call us on 01635 889222 if you have queries relating to IBM Cognos - we can't promise to solve them but we'll try to help!
