Cognos EP Migration Tip 3 ‘Alternate Hierarchies’
As the migration journey continues, find out how you can prepare your Cognos model with our third top-tip for Cognos Enterprise Planning on how to handle ‘Alternate Hierarchies’.
Cognos Enterprise Planning has always had an achilles heel which is ‘alternate hierarchies’. For those unfamiliar with this jargon as part of their Cognos EP Migration, an ‘alternate hierarchy’ is any rollup from base data which consolidates into groupings different to, but parallel with, the standard hierarchy.
Do I have ‘Alternate Hierarchies’?
Probably yes. Typically this is an issue when a business is re-organising its cost centres, for example:
“Manager A gains an additional 6 cost centres, 3 from Manager B & 3 from Manager C”
If this re-organisation takes place at the beginning of a Budget then Cognos EP presents the user with a choice:
“Do we re-organise history for comparison to last year or do we leave the history alone?”
This is a very tough question as ‘out-of-the-box’ EP only supports one hierarchy. It is very difficult to leave the history alone as everything gets re-organised according to the new Hierarchy.
If this re-organisation takes place during a Budget or Forecast then Cognos EP tends to just ‘melt-down’.
I have ‘Alternate Hierarchies’ – What do I do now?
A lot of Administrators go down the route of creating parallel sets of Cost Centre dimensions but this can be very messy, especially when the time comes to migrate. All Enterprise Planning tools support alternate hierarchies, so it becomes something of a nightmare trying to pull together a disparate bunch of cost centre dimensions back into a single consolidated structure.
Fortunately I have a ‘widget’ for this.
Widget – ‘Hierarchies’
This widget is intended for use as an ODBC source in Microsoft Excel. It is strongly recommended that you duplicate this format in a proper enterprise tool such as SQL Server. It is simple to relocate an ODBC connection from Excel to SQL Server provided the SQL table matches the above specification.
As you can see, the Primary Hierarchy is rolling the Cost Centres up into Old and New groupings. The Alternate Hierarchy is an attribute, in this case colour, but this is not supported by the Dlist Builder which is only capable of automating subtotals for the Primary Hierarchy.
The Hierarchies widget allows for an Alternate Hierarchy to be automatically maintained on the Cost Centre Dlist through the use of a Dcube Map which is itself automated by the widget.
Please note that if you are not a Cognos Administrator just ignore this paragraph as it will all be “Greek”. (Incidentally, did you know that Greek for “It’s all Greek to me” is “It’s all Chinese”?)
Show Me How
- Setup ODBC for your widget e.g. EP Migration Widgets.xlsm
- Setup ODBC Import in your Dlist e.g. Dlist Hierarchy Widget
- Setup ODBC Import into your Dcube Map e.g. Dcube Map Colour
- Add an internal Dlink using your Dcube Map e.g. Dlink Hierarchy Widget
- Finally you need to refresh your report Dcube e.g. Dcube Hierarchy Widget
You can now see we have 2 Hierarchies in the same Cube and both add up to the Total of 8.
Cognos EP Migration Journey
I am not saying you should replace all your Standard Hierarchy Dlists with Alternate Hierarchy Dlists as this could be a loss of focus on the main task, which is to get your model streamlined and ready for migration.
What I am saying is that you should populate your Hierarchy documentation with all Alternate Hierarchies whether or not you implement these in EP as you will definitely want them to be implemented in your new tool.
Nobody wants to waste time during actual migration trying to work out what Alternate Hierarchies should or should not be included in what calculations & reports.
Sign up and never miss a blog post from Triangle