Menu

The issue of Calculation Migration within Cognos EP Migration is that all planning products do most Calculations in similar ways. But there is one big difference – ‘Cross-Dimension Calculation’.

What is a Cross Dimensional Calculation’?

I would define a ‘Cross-Dimensional Calculation’ or ‘CDC’ as a calculation which alters its definition in the Measures Dimension according to its position in another Dimension.

An example might be:

{Sales} = {Units} x {Price}

Units might be keyed in for some Cost Centres but calculated for others.

In Excel this is not an issue as you just overtype one formula with another depending on which Cost Centre you are in. But in Cognos EP you have to attribute your Measures Calculation with a Flag and then add a conditional statement:

IF {Flag} = 1 THEN {Sales} = {Total Units} * {Share%} * {Price} ELSE {Sales} = {Units} * {Price}

The above expression converts a ‘CDC’ into a ‘Single Dimensional Calculation’ or ‘SDC’.

So What’s the Problem?

The problem is that Excel is fundamentally designed to support CDC’s and Cognos EP is fundamentally designed to oppose them. This puts you in a ‘Grand Old Duke of York’ situation. You went through all the pain of converting your ‘CDC’s into ‘SDC’s in the first place when you set up your EP model. If you have lost your original Excel specification, like many of my clients, then you have to go through the pain of converting your ‘SDC’s back into ‘CDC’s because all of your migration options need a specification in Excel format.

No!” I hear you say, “Let’s save time by using the self-documenting feature of Cognos EP.

Well here it is:

Tip 6.1

Don’t worry, I don’t understand it either and I have been doing it for 30 years.

Nobody in Anaplan or TM1 or Hyperion Planning would have the first idea how to implement this unless they were also Cognos EP experts. Even if they were Enterprise Planning experts they would probably tell you to redesign the whole thing from scratch, and that breaks the golden rule: ‘Don’t eat the whole elephant’.

Wouldn’t it be great if we had a simple template which could help us both document and streamline our Measures Calculations into bite-sized chunks ready for Migration?

Well it just happens I have a widget for that. Widget – ‘Measures’.

Widget – ‘Measures’

The purpose of Widget – ‘Measures’ is to pull together all the elements of a Measures Calculation in both Excel and Cognos EP formats at the same time.

This is a very rapid way of liberating Cognos EP formulae into a simple documentation standard. More importantly it also allows us to rapidly replace complex BiF functions that do not migrate with much simpler expressions like ‘IF THEN ELSE’ which migrate easily.

To make things even easier, the widget comes with a Translate function which automatically converts simple expressions like IF THEN ELSE

From this:

Tip 6.2

 

Into this:

Tip 6.3

That makes it much easier to confirm your document matches your model and if you are tidying up your EP formulae then these formulae will just paste straight in from the document.

That just leaves Options and Link information to add and you will have a definitive specification thus:

Tip 6.5

I am assuming you have already tidied up your links after reading my previous top tips for Cognos EP Migration. So this also acts as a double check that you have tidied up properly and don’t have multiple Links pointing at the same Measures.

Cognos EP Migration Journey with Triangle

I’m not saying that tidied-up ‘Single Dimensional Calculations’ are the perfect solution for your new software platform. There are many who swear by ‘Cross Dimensional Calculations’.

I am saying that tidied-up ‘Single Dimensional Calculations’ are guaranteed to work, maybe slower than the theoretically ‘perfect’ ones, but still a guarantee that you can eat the elephant in bite sized chunks and that your model will keep on working while you do it. No outages.

Look out for the next Cognos EP Migration Tip 7 when we will be looking at how this widget can be used to transform UAT.

 

Posted in Cognos

February 23, 2016