This document discusses some important considerations for using IBM Cognos TM1 as a data source to IBM Cognos Business Intelligence version 10.2.1 and higher
Resolving the problem
There are 3 different internal software architectures that may be employed by Cognos BI version 10.2.1 and higher for TM1 data sources
Architecture 1 - When using the compatible query mode (CQM)
The purpose of CQM is to maintain behavior consistent with that of Cognos BI 8.4.1 in order to give Cognos 8 customers an upgrade path to Cognos 10 that is as easy as possible. When you use CQM for TM1 data sources, the internal architecture employed by the Cognos BI server is the same as that used by Cognos BI 8.4 and TM1 9.5.1 whereby the MDX processing is performed by the C++ query engine in the BI server.
The official positioning guidance of CQM versus DQM is as follows:
New content should be created in DQM. Upgraded content can remain in CQM if you are satisfied with it, or you can opt to migrate it to DQM.
Architecture 2 - When using the dynamic query mode (DQM) with TM1 server 9.5.2 or 10.1
When using DQM with TM1 server versions 9.5.2 or 10.1, overall performance is better than CQM because the Cognos BI server employs the DQM Java query planner that makes use of optimizations which are unknown to the C++ CQM query planner, and the MDX processing is performed closer to the data by the TM1 server.
Architecture 3 - When using the dynamic query mode (DQM) with TM1 server 10.1.1 or higher
Out of the 3 internal software architectures mentioned in this document, overall performance is best when using DQM in Cognos BI 10.2.1 or higher with TM1 10.1.1 or higher. Performance is best through this architecture because the Cognos BI server will employ DQM's Java MDX engine which is also known as LOLAP (Local OLAP engine). In previous versions of Cognos BI, LOLAP was only engaged for Oracle Essbase, SAP BW, DMR and Dynamic Cubes.
Following are some reasons why using LOLAP for TM1 enables the best overall performance and user experience:
- LOLAP is generally faster than the MDX engine in TM1 and can perform certain calculations that the TM1 MDX engine cannot.
- LOLAP allows for considerably more BI side caching. Avoiding round trips to the TM1 server allows for major reductions in user wait times during interactive analysis. Note that there is no need to manage the LOLAP cache for TM1 data sources because LOLAP will automatically detect changes to TM1 cubes and will in turn automatically clear any stale data from its memory.
- LOLAP for TM1 always applies NULL suppression in its queries to TM1 (NON EMPTY clauses are submitted for all data queries). This enables only the minimized result sets to be transferred from the TM1 server to the Cognos BI server. Note that the Cognos BI report outputs will correctly reflect suppression options specified in the Cognos BI authoring interfaces - the Cognos BI server will fill in any missing null value intersections that are needed to produce the requested output. Also note that LOLAP automatically manages useProviderCrossJoinThreshold settings for large sparse result sets, therefore you should not manually change the useProviderCrossJoinThreshold setting for this architecture.
- LOLAP for TM1 makes use of the TM1 Java API which enables faster loading of members than previously used interfaces.
Following are some considerations to be mindful of for using LOLAP for TM1 (architecture 3) compared to architectures 1 and 2
- The version of your TM1 server is extremely important because LOLAP is only engaged if your TM1 server is version 10.1.1 or higher.
- More memory will be consumed by the Cognos BI query service's Java Virtual Machine (JVM) when LOLAP is used (architecture 3) than when LOLAP is not used (architecture 2). This is mainly because there will be considerably more BI side caching when LOLAP is engaged than otherwise. Refer to technote 1587457 for memory guidelines for the query service JVM.
- Problems associated with TM1 cubes that are under-fed are more noticeable when LOLAP is employed because of the increased usage of internal suppression. Please refer to the IBM Cognos TM1 FEEDERS proven practice document on DeveloperWorks for more information on feeders and the ramifications of under-feeding TM1 cubes. If you are unable to prevent a TM1 cube from being under-fed, you can avoid missing data in LOLAP queries by applying the following steps which will prevent LOLAP from optimizing performance by submitting the NON EMPTY clause in its queries to TM1:
1) Create a string cube attribute named "IsUnderFed" using TM1 Architect
2) Set the value of the attribute to 'T' for any cubes that require the workaround
3) Restart the Cognos BI query service