What if analysis on multi dimensional cubes (OLAP)

Question

I have a multi dimensional OLAP cube with a number of dimensions. Some of these dimensions have hierarchies. The users would like to perform 'what-if' analysis on the measures in the cube by changing the hierarchies in the dimensions.

For example, they want to know the impact on departmental resource budgets by moving employees between departments or the movement in cost of manufacture if a product is moved from one factory to another.

Does anyone have a straight forward way to do this in a modern OLAP engine?

Answer 1

A colleague has pointed out this video - http://www.youtube.com/watch?v=CGBhSmi4euo - on YouTube which seems a little academic but is about the only material on the subject that has been found so far.

Answer 2

There may be tools that allow this sort of analysis, but I only have experience of writing MDX, which ought to be able to help you.

Typical 'what if' analysis is more about changing values in the OLAP cube (e.g. change net sales from 845.45 to 700.00 and see what happens to gross profit). Your case is a bit different as you want to move members within a hierarhy, but keep the same values.

I haven't worked through a full solution, but the way I would approach it would be to create a new 'calculated member' or set (on the fly) and use that to build up the new hierarchy that you want. Your query can then use that on one axis.

Look carefully into 'visual totals' as there may be potential pitfalls there!

Answer 3

Andy - It depends on the tool you're using. Some, for example, set the hierarchies at cube build time. Others have dynamic hierarchies. What tool are you working in?

Answer 4

have you taken a look here? http://office.microsoft.com/en-us/excel/HA011265551033.aspx if you are using sql server and excel, you want the "Excel Add-in for SQL Server Analysis Services" and you can perform writeback to the cubes. Might not be exactly what you want, but it is the closest I have come across.

"What-if analysis and writeback What-if analysis enables you to initiate a "what-if" scenario by updating data and analyzing the effects of changes on your data. You can save the scenario for future analysis. When you save the scenario, changes you made to data (known as writeback data) are written to the cube. Once you write back changes, the data is available for future analysis and can be viewed by and shared with others who have access to the cube."