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.