What to do if multidimensional cubic analysis (OLAP)

Question

I have a multi-dimensional OLAP cube with a number of dimensions. Some of these dimensions have a hierarchy. Users would like to perform an “what-if” analysis of measures in the cube by changing the hierarchies in the dimensions.

For example, they want to know the effect on departmental resources budgets by moving employees between departments or the movement of production costs if a product moves from one factory to another.

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

Answer 1

Have you looked here? http://office.microsoft.com/en-us/excel/HA011265551033.aspx , if you are using SQL Server and excel, you need the Excel add-in for SQL Server Analysis Services, and you can write back into cubes. It may not be exactly what you want, but it is the closest that I came across.

What-if analysis and write-back analysis What-if analysis allows you to initiate a what-if scenario by updating the data and analyzing the effects of changes in your data. You can save the script for future analysis. Script, changes made by you into the data (known as writeback data), they are written into the cube. After you record the changes, the data is available for future analysis and can be viewed and transferred to other persons who have access to the cube. "

Answer 2

SSAS in SQL Server 2008 allows you to have multiple hierarchies. Although this will not allow your users to create and modify hierarchies on the fly, you can still assemble your requirements and rebuild the cube using these additional hierarchies.

Answer 3

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

Answer 4

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

A typical what-if analysis is more about changing the values ​​in the OLAP cube (for example, change your net sales from 845.45 to 700.00 and see what happens to the gross margin). Your case is a little different since you want to move elements within the hierarchy, but keep the same values.

I did not work with a complete solution, but the way I would like to do this is to create a new "calculated member" or install ("on the fly") and use it to create the new hierarchy that you want. Then your request can use this on one axis.

Take a close look at the “visual totals,” as there may be potential traps!

Answer 5

Andy - It depends on the tool you use. Some, for example, establish hierarchies at the time of cube assembly. Others have dynamic hierarchies. What tool do you work?