As many clients tell us, report performance can be a challenge in IBM Cognos Analytics environments. In most traditional IBM Cognos deployments, reports are developed using a relational data store as its primary source of data. The data is retrieved from the relational table structures using SQL scripting for reporting and analysis.
In many cases, the data stores are modeled using popular Datamart or Enterprise Data Warehouse (EDW) schemas like Star or Snowflake. This approach provides the flexibility of using SQL to provide data results, but is less than efficient when reports are displaying aggregates.
Deployments that are SQL-heavy need to manage the load of many simultaneous requests to the EDW during the day, as well as managing intra-day refreshes in order to provide near real time results. This may lead to performance issues depending on the size and nature of the reports and volume of data. Since operational reports are typically detailed in nature, they need to be developed in this fashion.
As an alternative, there is an opportunity to better leverage IBM Planning Analytics or other multi-dimensional technology to increase efficiency and improve performance of aggregate-based reports. In OLAP, the data is already aggregated and rolled up to the required summary points.
Typically, requests using MDX against an OLAP data source are significantly less resource intensive than SQL requests against a large database with tables that are joined, for two main reasons:
- MDX requests do not need to aggregate detailed rows, since the data is already aggregated
- There is no concept of a ‘join’ in dimensional reporting
This approach has many significant benefits:
Improved Performance and Overall User Experience
Performance in generating aggregate-based, dimensionally authored reports, is greatly superior to an equivalent SQL based approach. Dimensionally authored reports against an OLAP source do not have to aggregate the result set. One ACG client reduced the performance of a report from 90 minutes to under 1 minute.
Reduced EDW Load
Reports that are either scheduled or on demand, if successfully converted to dimensional-based reports, take a significant load off the EDW in the number and complexity of report requests. In fact, calculating aggregates is considered one of the most resource intensive types of SQL requests for the EDW. This will also increase the performance of the other relational based on demand reports.
Reduced Disruption to Existing Process and Environment
Converting reports from SQL based relational to dimensional is significantly less disruptive to the users during intra-day refreshes. Many OLAP technologies provide non-disruptive update technology which minimizes or eliminates disruption for the user. For example, dimensional reports developed in Cognos sourced by TM1 cubes is not only real time but minimally disruptive, reducing the need for a separate ‘reporting’ cube. Powerplay cubes have non- disruptive cube update technology, which is completely transparent to the user.
Better and more granular security
Typical multi-dimensional designs have security defined at much more granular level. This provides greater protection of the data than a relational design provides.
In the client example above, there was a set of reports that were pointing to IBM Planning Analytics as a data source but were not written properly and took over 90 minutes to run. After a brief revision and re-write of the core logic (which took less than a few days for that particular report) were able to reduce the run time to just 1 minute. The resulting benefit to the user but also the reduced workload on IBM Planning Analytics represented a huge benefit.