Category: Cognos
Reporting on ‘% of base’ in Cognos10
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)
Example
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:
Summary
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
This 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:
In truth, each solution does has it’s own unique strengths and weaknesses:
It is also interesting to consider how each solution approaches roll ups and aggregation. Specifically:
PowerCube – data structures are pre-aggregated:
TM1 OLAP – sources aggregate on the fly:
Cubing Services – optimizes the most common paths or caches as you go:
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:
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:
Key takeaways….
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
The neural network model clearly demonstrated the strong correlation between cube build times and the number of categories in the PowerCube.
Connect
Connect with us on the following social media platforms.