Tip 1: Managing Big Cubes within Cognos EP

I have no idea what the specific issues of your model are but I can tell you from experience that most EP sites run into trouble over ‘Big Cubes’. Find out how to avoid key problems with ‘Big Cubes’ when using Enterprise Planning and what you can do to overcome the challenges.

The trouble with 5 dimensions in a cube is that you only need to increase each dimension by 20% to increase the cube size by 200%.  I’m not exaggerating – check the arithmetic:

Ep Migration Tip 1 Image 1

One moment you have a cube within the software safe limit (20m cells) the next moment you are looking at all your Excel reports taking an hour or more to refresh when they used to take minutes, I promise you, you don’t want to be in the office when that happens!

“What do you mean the cube’s too big? It’s only a few more cost centres. What on earth did I spend all this money on!”

For this reason I have made ‘Big Cubes’ the first of my ‘Top 10 Tips for Cognos EP Migration’.

Do I have ‘Big Cubes’?

The first thing you need to know about ‘Big Cubes’ is that they probably won’t be ‘big’ in any tool you migrate to. So the real question is whether you face an immediate crisis or one you can live with?

The most obvious pointer is cell-count. In Cognos EP Analyst when you open a Cube you see the cell-count before you click ‘OK’. If it’s over 20m you know you have an issue. Anything over 30m and you are lucky it hasn’t blown up already.

Cognos EP Contributor is different with ‘slice-size’ as the limiting factor. You get this figure on a splash screen when you kick off a GTP. 500k is great, 2m is ok, but 5m and there will be problems. If you’re not sure, check out other symptoms like the Website being slow to open or your Excel Addin being abnormally slow. The Addin refresh can be a nightmare – I have known it go from 2 mins to 2 hours after a slight increase in Cube size. It was like driving over a cliff with no reverse gear.

OK I have ‘Big Cubes’ – What do I do now?

Your Triangle EP Health Check will advise you of your best course of action.  But if you want to go it alone I recommend you look at cutting back on excessive Scenarios.

The problem of excessive Scenarios is associated with ‘Excel style’ design. Excel is a victim of its own flexibility. Because you can throw data, assumptions, calculations and reports randomly into the same worksheet, there is an enormous temptation to do so. When you move to a bigger tool like Cognos EP, it is necessary to think a bit more about process so you don’t, for example, recalculate last year’s budget every time you run an update macro process. This sounds very obvious but if you open a Cube and you are not sure whether it is a Report or a Calculation then you probably have a Scenario problem.

As a general rule, Reports need many Scenarios and Calculations need only one. If your cube is a hybrid then you might want to consider separating into separate cubes i.e. pushing your Calculation Measures into a Calculation Cube which can be slimmed down to just one Scenario.

Worked example

Ep Migration Tip 1 Image 2

Your ‘Before’ Cube (Report P&L) has 120 Measures, 12 Customer Groups, 12 Product Groups, 12 Markets, 24 Timescale (Months, Qtrs etc.), 12 Scenarios (Budget, Actuals etc.) and it is already a Cognos EP busting 60 million cells. The Financial Director wants to add a new Product Group and your Triangle EP Health Check has told you that the cube is about to blow.

You only need to report on 20 measures so your ‘After’ Cubes (Calculation P&L + Report P&L) only need to be 5m and 10m cells respectively. Even though you still have exactly the same 120 Measures in your Calculation you now only calculate scenarios once instead of 12 times. After you have done your analysis, this change might only take a few hours to implement and yet could buy you another 12 months of life for Cognos EP.

So what’s the catch?

The phrase ‘After you have done your analysis’. It is so much easier to make a change like this on a well house-kept and well documented model with a development box rather than blind on a live untidy system. If your Cubes go down on the live system then you will have no choice. Either way, you have to do your analysis or you might make matters worse.

If your Excel Addin is taking 2 hours to refresh it should not be hard to make the case for a few hours of analysis to work out exactly which cubes should be calculations and which ones should be reports. Hopefully you have already completed your Triangle EP Health Check so this will already have happened.

Migration Journey

Think of Migration as a journey, one step at a time.
You will get the most out of my ‘Top Ten Tips for Cognos EP Migration’ if your system documentation is already clear, concise, and up to date. If, as I do, you hate doing system documentation then make sure you use a proven documentation template or you will end up creating an elephant or missing key points, or both.

What Next?

In this series of Top 10 Tips for Cognos EP Migration, next time I will be showing you how to tackle ‘Sorcerer’s Apprentice Links’ . You know what I’m talking about – “why use 1 link when 10 will do!”  If you think that’s bad in Cognos EP – you don’t even want to think about what it will do to you in a migration!

For more information view our Triangle Cognos EP Migration Process

Posted in Analytics, Cognos, Planning

November 28, 2015