What-If analysis – Various ways to implement using MSBI

Presently I am working on the What-If analysis capability in MSBI. So I went ahead on researching on what are the various ways we could implement What-If analysis in MSBI.

Per my current research, there are three ways we could be able to achieve What-If analysis. (If there are more options, Please feel free to add in the comments of this blog)

1.       Using Excel – Allowing user to modify the Cells values in the Pivot Table.

2.       Using Excel – With Predefined Slicer Values.

3.       Using SSRS – With Predefined Slicer Values.

Using Excel – Allowing user to modify the Cells values in the Pivot Table

• We need to enable the Write back option in the cube partition in order to do the what-If analysis.
• User can directly change the values in the cells to see the impact
• User can change the value at the higher level at a hierarchy also. For example, What-if the sales from one quarter should be moved to other quarter.

 Using Excel – With Predefined Slicer Values

• We do not need the write back option to be enabled.
• We need Excel 2010 and above to use this. Use Insert Slicer to use a slicer as shown below.
• User can just select the Slicer dimension attributes to see the impact.

Using SSRS – With Predefined Slicer Values.

• Very similar to the previous one. But it will be a static report in SSRS
• The slicer will be the parameters. The parameter needs to be chosen and click on generate report will show the impact.

 

Also 2 weeks back power view for SSAS multidimensional CTP has been released.

http://blogs.msdn.com/b/sqlrsteamblog/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

 I will work on that also to understand the What-If capabilities in power view. I am pretty sure we can generate even better interactive analysis.