What-if analysis is scenario building capability of OLAP. You can draw a straight parallel of what-if analysis in MS Excel. This is what you do in what-if analysis:
Create a what-if calculation model:
This is the calculation model on which you are going to apply different scenarios. Profit and loss projection for next 5 years is one example of a calculation model. A Calculation model takes a set of input values, to give the set of out-put values. Each different set of these 'input and output value combinations' is called a what-if analysis scenario.
Creating different set of what-if scenarios:
Depending upon your needs you can build different scenarios of input values, and you can apply those scenarios on the calculation model, and generate the output values. Here is a simplistic example:
Let's say that you have Profit and Loss projections for next 5 years as the calculation model. Following can be some of the inputs values to the model:
- Revenue on year 0
- Expected revenue rate of growth CAGR (cumulative average growth rate)
- Gross profit margin %
- Non-operating expenses.
- Income tax rate
- Rate of Dividend.Etc..
The output values can include:
- Revenue
- Gross margin
- Operating margin
- Profit before Tax
- Profit after tax
- Allocation to reserves.
How what-if analysis works in OLAP
An OLAP tool will have an end-user tool sitting on top of the OLAP server (like MS excel OR a business modeling tool). You will create a calculation model in the modeling tool, and the input and output data for each scenario is stored in OLAP. This is what you call 'write-back', and it is a key value add from OLAP tools. This is one key area where OLAP differs from Data Warehouse (which is read-only for good reasons).
Using combination of a good end-user tool and OLAP server, you can do the following: - All Input as well as output data can lie entirely in OLAP.
- Input and output data can lie partially in OLAP and partially in the end-user tool. The reason for partial OLAP storage is - you may like to keep only certain select scenarios in OLAP, and the rest of the scenarios could be more temporary OR non-serious scenarios, which you may not like to store in OLAP (but do it in the end-user tools)
- There is another cut for partial storage. You may like to keep only some part of input value-set in OLAP and rest in the analysis tool. This is because- some input values may not be aligned to the multi-dimensional design of OLAP. For example- in P&L calculation model, you may not have tax rate as a measure in OLAP dimensional model. To write back tax-rate, you may have to change the dimensional model, which may OR may not be worth the effort.
- Tagging of scenarios in term of how you want to present. For example-- 'most probable' to 'least probable' probable scenarios.
|
