Cognos EP Migration Tip 7 ‘UAT Logic Test’
Over the years of sorting out Cognos EP Migration problems I would say by far the ‘lion’s share’ of the time taken has been in User Acceptance Testing (UAT). Even if you are just doing a bit of housekeeping, you will still need some sort of framework or you will end up spending far too much time in UAT.
Why does UAT always seem to take longer than expected?
I think the key word here is ‘expected’. The person doing the ‘expecting’ is usually a Senior Finance Manager who has been used to getting things in Excel. The Cognos EP Migration salesperson has no doubt promised that Cognos EP works just like Excel, but only better. Hence, expectation levels have already been set and that faster is implied as a result!
What’s the problem with UAT?
The problem is that in Excel you have one day for UAT after which you fudge the errors with an ‘adjustment’ column. A cynic might argue that you are more likely to progress your career with ever more plausible explanations for the ‘adjustment’ rather than getting to grips with the real cause of the error because this way you are more likely to make your deadlines.
Sadly Cognos EP does not enjoy this ‘benefit’. Any error in data or calculations stands out like a sore thumb and it takes hours to engineer ‘adjustment’ columns compared to the seconds it takes in Excel.
So this is not really a level playing pitch!
What’s the solution?
Forget the comparison with Excel and put in a proper professional 3 step UAT regime:
- Logic Test
- Data Test
- Stress Test
Expect that this will take longer than expected, manage expectations, and hit your deadlines. You may be thinking along the following lines …
“Wouldn’t it be great if there was an off-the-shelf template that could take me through the whole UAT process more quickly so I could hit my deadlines and keep my boss happy?”
Well, it just happens I have a widget for that, or at least the first part of it.
Widget – ‘Logic Test’
It might be useful to first define what a Logic Test is. Since 99% of financial planning calculations begin in Excel we could define Logic Test thus:
‘Prototype test cycle of EP calculations aimed at matching the output of a definition in Excel’
Widget – ‘UAT Logic Test’ is a spin-off from Widget – ‘Measures’. It shares the same first three columns in order to leverage the translation of Excel into Cognos EP format.
I am sure purists would argue that you should never define anything in Cognos EP terms of Excel but in this case, the purists would be wrong. Do it this way and you will make your deadline.
Columns 4, 5 & 6 are very different to reflect the specific needs of UAT.
I’m assuming you have already streamlined your Excel formulae to avoid the use of complex functions. It has been my experience that the vast majority of complex functions (which cannot be migrated) can be replaced by a combination of simple functions (which can be migrated).
For the avoidance of doubt, simple functions are:
- Plus Minus Times Divide + – * /
- IF THEN ELSE IF(<Condition>,<Output>,<Output>)
- CUMUL B2 = A2 + B1
- LAG B2 = A1
It is remarkable how many complex formulae can be expressed in terms of the above simple formulae with the enormous added advantage that UAT can then be reconciled line by line.
In the above example, column 4 uses the Excel Add-in to pull Cognos EP back into Excel. Because the formulae are all simple, they match line by line and it is immediately possible to identify the precise line where the UAT fails.
In this example we can see that Measure dV0 is calculated at -738 in Excel and -1.476 in Cognos. A clear error. Thereafter most of the calculations in Cognos are incorrect.
By simply pasting the contents of the Cognos EP column into Cognos EP, the calculation for dV0 is automatically corrected and the cube sails through UAT Logic Test.
Cognos Enterprise Planning Journey with Triangle
In my next blog we will be look deeper into UAT issues with a standard methodology for Data Test.
Sign up and never miss a blog post from Triangle