Chat with us, powered by LiveChat

Visit our new site! Click

Category: Cognos

Reporting on ‘% of base’ in Cognos10

Reporting on ‘% of base’ in Cognos10

February 11, 2013 | By | Add a Comment

A workaround for the lack of ‘% of base’ functionality in Business Insight Advanced

A common requirement is to be able to product % of total or ‘% of portfolio’ reports, for comparative analysis. Unfortunately, this capability is currently missing in Business Insight Advanced. A workaround exists, which is to use the ‘% of’ calculation provided in Business Insight Advanced to hard-code the total value. However this introduces excessive report maintenance implications.

An alternative approach is to use Analysis Studio, which does support % of base, to ‘author’ the basic report layout required, then finish the report in Business Insight Advanced. (Note, however, that it is currently not possible to open an Analysis Studio view in Business Insight Advanced – it is necessary to open the view in Report Studio first, save to Cognos Connection, from where it can then be opened in Business Insight Advanced)


In the following example, we are looking to create a sectioned report, showing % of total for each column in the report. Starting in Analysis Studio, we have a crosstab like this:
Since Analysis Studio does not report ‘Section’ functionality (as is available in Query Studio, Report Studio and BIA), this will have to be added later.

The first step in Analysis Studio will be to add the desired measure as a nested level within the crosstab view (this will simplify creation of the % calculations later):
Next, the ‘% of total’ calculation will be added:
Next, an additional dimension will be added, which will form the rows of each section in the final report:
This view can now opened directly into Report Studio, or saved to Cognos Connection:
From Report Studio, the report can now be saved back to Cognos Connection (under a new name), which will finally enable it to be opened in Business Insight Advanced:
(Note: it is hoped that Cognos will improve the inter-Studio workflow in future versions, to enable Analysis Studio view to be opened directly into Business Insight Advanced, either from within Analysis Studio or from Cognos Connection)

This view can now opened directly into Report Studio, or saved to Cognos Connection:
Note the automatic creation of subtotals for rows and columns:
In the context of report sections, this is entirely unnecessary. However, currently within BIA there is no easy way to add/remove nested subtotals. After manually cutting out each of the superfluous totals, and sectioning the report (using the Section/Unsection button), we end up with the required report layout:
It had been hoped that Cognos 10 would introduce some level of Studio ‘rationalization’ in order to simplify the workflow for Business Analysts in particular. Unfortunately, the introduction of Business Insight Advanced (BIA) and Business Insight has thus far only added 2 new Studios without rendering any of the existing Studios redundant. It is to be hoped that in future versions, it will be less necessary to jump around several Cognos interfaces in order to construct what are relatively simply reports.

Footnote: It turns out this is trivially easy to do in Tableau (around 9 mouse clicks) and, ironically, in PowerPlay Client (5 mouse clicks).

Cognos OLAP: Understanding performance differences

Cognos OLAP: Understanding performance differences

January 31, 2013 | By | Add a Comment

OLAPThis weekend, I was reading a very interesting discussion on LinkedIn, on the topic of OLAP (specifically Cognos OLAP). On this thread, ex-Cognoid Norman Bobo does an excellent job of describing the differences between Cognos PowerCubes and TM1.

In his comments, Norman describes in detail several differences between the two technologies. Here is a summary of his key points:

1) Purposes – PowerCubes are a read-only BI solution – TM1 is read/write
2) Dates – PowerCubes have the concept of date dimensions – TM1 does not
3) Levels – PowerCubes have the concept of dimension levels – TM1 does not
4) Attributes – TM1 has strong support for attributes – PowerCubes do not
5) Cube building – very different (TM1 is an in-memory solution, PowerCubes are not)
6) Data Scalability – PowerCubes do not manage large dimensions very well
7) User Scalability – TM1 is not so easily scaled
8) Cognos BI Integration – as a legacy Cognos technology, PowerCubes are more tightly integrated

(I strongly encourage you read the entire thread here)

From a User Experience perspective, I believe IBM would position their various offerings as specialized OLAP approaches to optimize for specific types of applications:

  • Cubing Services dynamic caching layer for large user communities and large data sets, with planned performance and optimization facilities. Central management and IT administration for these large OLAP applications
  • PowerCubes for large user communities and moderate data sets, with fast, consistent performance. Owned and operated by the business, mobile for broad deployment options
  • TM1 for user communities with larger highly dynamic data sets and read/write requirements. Owned and operated by the business.
  • In truth, each solution does has it’s own unique strengths and weaknesses:

    OLAP Differences
    It is also interesting to consider how each solution approaches roll ups and aggregation. Specifically:

    PowerCube – data structures are pre-aggregated:

  • Build time includes the aggregations so read times are faster
  • Calculations are more expensive at build time; not read time
  • User requests for data is retrieved with fast, consistent performance
  • No roll ups or aggregations need to be computed for user data request

  • TM1 OLAP – sources aggregate on the fly:

  • Data loads establish leaf cell values; on demand aggregations and calcs are cached
  • Large data or many calculations will affect 1st time data request performance
  • Pre-Cache ‘high-level’ views following data load for faster first time query
  • Users can alter views / hierachies or contribute data on the fly and recalculate

  • Cubing Services – optimizes the most common paths or caches as you go:

  • Pre-aggregated data using the Optimization Advisor
  • Fast first time query as optimizer routes data requests along optimized paths
  • Run time cache builds up on query results improving performance as cache builds

  • So, which is best for Read-only BI? Well, of course it depends on the customer requirements. As Norman points out in his article, data volumes and user volumes are key considerations. Some general guidelines are as follows:

    OLAP Differences
    It is certainly important to be aware of the frequency & type of reporting required by the user community, as well as the size and geographic location of the user community (e.g. centralised or decentralised). It also worth noting that Cognos internal testing clearly demonstrated that Powercubes scaled very well with increasing concurrent user volumes, whereas TM1 cubes did not.

    Other points of note:

  • TM1 may require additional hardware to scale
  • TM1 is a good option when complex calculations or roll ups are required
  • TM1 is also the engine of Cognos Metrics Studio and Cognos Express
  • PowerCubes can be built ‘in memory’ by setting up a ‘RAM disk’
  • Leverage Business Viewpoint & Cognos Framework Manager for synergy in shared dimensions (e.g. Transformer Dimension ‘re-use’) and common descriptions of data

  • Key takeaways….

  • No single OLAP style or product can meet all needs without losing either quality, performance or scalability
  • Cognos OLAP portfolio covers the full spectrum of performance management needs
  • Each is optimized for the targeted environment/applications/tasks

  • Footnote:
    Interestingly, many years ago I actually built a neural network model (using Cognos 4Thought) of PowerCube build statistics from Cognos customers around the world, in order to identify the factors which had the biggest impact on Cube build times.

    Transformer Cube Build Factors

    Transformer Cube Build Factors

    The neural network model clearly demonstrated the strong correlation between cube build times and the number of categories in the PowerCube.