Friday, December 14, 2012

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.
 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.