ACG Business Analytics Blog

Improving IBM Cognos Analytics Report Performance by using Multi-Dimensional Data Sources (IBM Planning Analytics or Cognos OLAP)

Posted by Steve Moore on Sat, Dec, 15, 2018 @ 11:54 AM

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:

  1. MDX requests do not need to aggregate detailed rows, since the data is already aggregated
  2. 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.

Topics: IBM Planning Analytics, IBM Cognos Analytics

Working with Virtual Dimensions in IBM Planning Analytics 2.0.

Posted by Theo Chen on Mon, Apr, 23, 2018 @ 02:01 PM

Ability to create virtual dimensions from attribute-based hierarchies is a key update in IBM Planning Analytics 2.0 and a huge source of value. This is a major differentiation for IBM PA compared to other similar platforms. It provides even greater flexibility to the user to analyze data using user-defined parameters on the fly. With virtual hierarchies, companies have much greater flexibility in designing solutions that will provide greater efficiency yet maintain the flexibility to include attributes for thorough analysis.

What are Virtual Dimensions

Virtual dimensions are dimensions that are created in an existing IBM Planning Analytics system on demand by selected end users (system administrators). These dimensions are not part of the core system structure / design, but rather are created ad-hoc as needed based on attributes of individual existing dimension elements. Attributes can be created as and when needed while the system is in use and there are no practical limitations to how many attributes can be created for every single member.

Once a virtual dimension is created, it can be used just like any other dimension. It can be selected for reporting, it can be brought into a cross-tab view for analysis or used as a target for input for (plan, forecast) data.

What Virtual Dimensions are NOT

Virtual dimensions in IBM PA are NOT the same as the ability to create alternate rollups / hierarchies or sorting / filtering by attributes. We find that the concept is a bit hard to understand initially and people default to the capabilities they know. Unlike filtering and alternate hierarchies, virtual dimensions go deeper and provide a more thorough way to analyze the data.

Example:

We have a sales reporting application with Customer and Business Unit being the two key dimensions. Other dimensions include Account, Time etc. The Customer dimension is organized by Industry – so Customers rolling up to Sectors rolling up to Industries that roll up to Total Customer.

Let’s say I want to understand my sales by size of company (Enterprise vs Mid-Market vs Small and Medium Enterprise, or SME) in addition to the industry rollup. I do not have “Company Size” defined as a dimension so under previous rules I would have the following options:

  • Redesign the cube to include “Size” as a dimension – depending on the size of the overall model that could be a significant undertaking and could take a lot of time
  • Build an alternate rollup of Customer by size – in this case I would have to choose the Industry or Size rollup for reporting and analysis but I could not use both and would still not get the desired cross-view
  • Embed “Size” inside the existing “Industry” rollup – this would be extremely inefficient as I would have to include multiple rollups within each Industry / Segment for Size and repeat them for each Industry / Segment and deal with conflicting element values – not desirable from maintenance perspective and yet I would still not get the same flexibility for analysis

Enter “Virtual Hierarchies” – with IBM Planning Analytics 2.0 I can simply create a new “Attribute” and label each Customer an Enterprise, Mid-Market firm or SME. Once that is done, I would turn this Attribute into a Virtual Hierarchy and it would show up in my “Set” menu. Once there, I can simply drag the “Size” Hierarchy into the cross-tab view and see the breakdown of customer by size and industry in a simple cross tab view. I am able to drill down or pivot to analyze the data and even input into the virtual intersections to post adjustments or forecast sales. And of course element security still works at the leaf level. Extremely powerful…

Key Benefits

The key benefit implication from this capability include the following:

  • Savings in (RAM) memory due to less number of core dimensions required and thus the size of the core model
  • Better overall performance and usability of the model with less clutter / complexity
  • Much greater flexibility to adjust existing models and get deeper insight
  • Ability to conform with model standards with any customization done locally

What to Look Out For

Some points to be aware of when working with virtual hierarchies include the fact that they can currently only be built with 2 levels – TurboIntegrator scripting is required for deeper hierarchies with more levels. Also, elements in virtual hierarchy are not elements in main itself – each element in virtual has to be unique from every element in dimension. There is no security (yet) for 'virtual' elements, although that should be addressed soon.

The impact of Virtual Hierarchies will be different for existing vs net new applications. For existing models, it will add flexibility through the ability to expand structures without going through a potentially substantial application redesign. For new solutions, it creates an opportunity to design models with more simplicity and rely on Virtual Dimensions to provide scalability and flexibility in the future.

Give us a call to discuss how this great new capability can add value to your platform and review options to provide more insight and analytical power.

Topics: IBM Cognos TM1, TM1 Technology, Performance Management, Financial Planning and Analysis, IBM Planning Analytics

Contact ACG

Tel: (973) 898-0012

Latest Posts

Follow ACG