This is a guest blog post from Revelwood’s Jay Apwah.
Did you know that you can access Planning Analytics Virtual Hierarchies in Cognos Analytics?
Prior to Planning Analytics 2.0 (PA) and Cognos Analytics 11.0.6 (CA) the limiting factor in reporting cube design was related to product limitations that existed in CA at the time. In order to use level-based or relative functions in CA, one had to name the levels of the reporting dimensions. This required the PA developer to limit each reporting dimension to one root consolidation and to maintain a balanced structure in each of those dimensions. Furthermore, in order to perform analysis by element attributes or alternate element roll-ups, additional attribute dimensions and/or additional dimensions that represented the alternate roll-ups needed to be added to the reporting cubes. Sometimes that led to reporting cubes with a seemingly unnecessarily large number of dimensions which led to messy and sometimes confusing ad-hoc reporting in CA.
Cognos Analytics 11.0.6 allows you to leverage PA virtual hierarchies. You no longer need to create and add additional dimensions to the reporting cube in order to report on attributes or roll-up dimension elements differently. In the following example, the time dimension called bpmPeriod has several alternate roll-ups. The number of levels in each alternate roll-up structure is different which means that we cannot properly name levels in the }HierarchyProperties cube.
We’re first going to isolate the node called “All Years” with a PA virtual hierarchy. In Planning Analytics Workspace (PAW), we will create a new hierarchy called “Calendar”. Note: there are many different ways to create hierarchies in PA. This examples uses the PAW interface.
The structure of the calendar hierarchy is as follows: All Years > Year > Quarter > Month:
When you create a hierarchy in PAW a new “Leaves” hierarchy is automatically created. You can see the existing the “Calendar” hierarchy and the new “Leaves” hierarchy in the }Dimensions control dimension:
The next step is to logically name the levels of the Calendar hierarchy using the }HierarchyProperties control cube:
Then create and execute a TI process with the “RefreshMdxHierarchy()” function in the prolog
Within Cognos Analytics, you now have access to the bpmPeriod dimension as it exists normally and also have access to all of the virtual hierarchies and their named levels for use with level-based, relative functions. This will make your ad-hoc reporting easier to create.
IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Installing Cognos Analytics Samples
IBM Planning Analytics Tips & Tricks: Writing Rules
IBM Planning Analytics Tips & Tricks: Duplicating Sheets
