Using DQM with TM1 Cubes
Dynamic Query Mode (DQM) is a feature available in Cognos 10 that can be turned on when publishing your Framework Manager (FM) packages. One question that is often asked is “When should we use DQM?”
First, lets talk about what DQM is. DQM is an enhanced java based query mode that offers some of the following benefits on TM1 OLAP cubes:
- Improvement of complex OLAP queries by combining local and remote processing for better MDX generation
- Enhanced data caching providing a higher cache hit ratio
- Security-aware caching
- Enhanced handling of null suppression in OLAP data sources
With the DQM testing we have completed we have yet to find a negative impact of having DQM on when using TM1 OLAP cubes as your data source in FM. Do to this we follow the practice of always having DQM turned on when reporting off of TM1 cubes. When using DQM you will notice the greatest advantage with large complex queries. For example, we had a client where a large query running off of a TM1 cube with 14 dimensions and a large amount of data in it was taking approximately one hour to complete. When we turned DQM on it dropped the run time down to 4-5 minutes.
A word of warning if you are contemplating turning DQM on. If you have standard reports built through Report Studio with DQM turned off, there is a chance that when turning DQM on it will invalidate your reports. It is suggested that DQM is turned on at the beginning of a project otherwise you will most likely need to rebuild a number of your reports. Ad-hoc queries built through workspace advanced and Analysis Studio typically continue to operate as designed and will not need to be rebuilt.
This blog entry focused specifically on using DQM on TM1 OLAP cubes. DQM can also be beneficial when reporting off of a relational datasource that is dimensionally modeled. We recommend you review the “IBM Cognos 10 Dynamic Query Cookbook” for additional information or contact BEM Partners if you need assistance of have questions.