Sidebar: The Other What-If Analysis Options in Excel
NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services.
What-if analysis against Analysis Services is just one form of what-if analysis supported by Excel. Other forms focus on spreadsheet-based models and are exposed on the Data tab. More information on these alternatives is found here.
NOTE Just to make things a bit more confusing, Excel can support even more forms of what-if analysis using the Solver add-in.
The basic pattern of all these what-if analyses is the same. The analyst constructs a model, varies inputs, and evaluates the outputs. The fundamental difference is the type of model used: a spreadsheet or an Analysis Services cube.
The question then becomes, Which model should I use for my analyses? In my opinion (and I’m by no means authoritative on this), Analysis Services is the right choice when the model is based on sets of related data with complex calculations across these.
In addition, Analysis Services writeback supports:
- Data input from multiple analysts,
- An audit trail of data input,
- Preservation of data input between analyses,
- Complex security for controlling the reading and writing of data
Of course, the use of Analysis Services as the model behind the analysis requires a moderate level of cube development skill within your organization. Other techniques (such as writing to a relational database which employ a more ubiquitous skillset) can be employed to address many of the previously stated requirements. Still, Analysis Services (and OLAP in general) is unparalleled in its ability to express complex calculations which is why many planning, forecasting, and budgeting applications employ OLAP.