Understanding What-If Analysis

NOTE This is the first of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Business analysts frequently encounter questions focused on the impact of potential change:

• What if we raised our parts and materials inventory?  How might that impact costs?  How might that impact the reliability of product delivery?
• What if we ran this promotion on this product?  How might that impact revenue and profits?  What impact might this have on inventories?
• What if we were required to reduce our budgets?  Where might we make those cuts and what activities might be impacted?

The what-if question is focused on uncertainty.  This differs significantly from the what-was question addressed (with relative certainty) using historical data sets.  To answer the what-was question, the analyst turns to the data warehouse.  To answer the what-if question, the analyst turns to a model.

The model is a representation of the portion of the business the analyst needs to consider.  It accepts inputs and produces outputs based on these.  The analyst devises scenarios representing combinations of inputs to the model and studies the associated outputs.  This exercise clarifies the analyst’s understanding of the business and allows him or her to evaluate the range of outcomes likely to occur under differing scenarios.  Based on this, the analyst can then provide informed guidance on how best to deal with an uncertain situation.

The model used depends on the problem to be solved.  A simple model might be nothing more than a diagram jotted down on a sheet of paper or a whiteboard which provides a heuristic for the analyst.  A more complex model might be assembled in a spreadsheet or developed using custom coding or statistical techniques.  Still other models might be assembled in a database using sets of related data and calculation logic for deriving values from these.

This later kind of model fits neatly into the space occupied by OLAP technologies which is why most OLAP vendors provide writeback functionality with their products.  Writeback allows analysts to enter data (inputs) into a cube (model) which are then incorporated into the cube’s calculations (outputs).  Analysis Services, Microsoft’s OLAP technology, is no exception and has provided support for writeback since its first release (as OLAP Services) with SQL Server 7.0.

NOTE Cell writeback, the form of writeback we will focus on first, is supported with all editions of Microsoft SQL Server Analysis Services.  Dimension writeback, a special case for writeback, is only supported with SQL Server Enterprise, Developer and Evaluation editions.