|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Working with Models, 8 of 8
Instead of calculating a single set of figures for a month and division, you might want to calculate several sets of figures, each based on different assumptions.
You can define a scenario model that calculates and stores forecast or budget figures based on different sets of input figures. For example, you might want to calculate profit based on "optimistic," "pessimistic," and "best-guess" figures.
To build a scenario model, follow these steps.
Suppose, for example, you want to calculate profit figures based on optimistic, pessimistic, and best-guess revenue figures for each division. The steps for building this scenario model are explained in the following example.
You can call the scenario dimension
scenario and give it values that represent the scenarios you want to calculate.
These commands give
scenario the values
DEFINE scenario DIMENSION TEXT LD Names of scenarios MAINTAIN scenario ADD optimistic pessimistic bestguess
These commands create a variable named
plan dimensioned by three other dimensions (
division) in addition to the
For this example, you need to enter input data, such as revenue and cost of goods sold, into the
For the best-guess data, you can use the data in the
budget variable. Limit the
line dimension to the input line items, and then copy the
budget data into the
LIMIT scenario TO 'BESTGUESS' LIMIT line TO 'REVENUE' 'COGS' 'MARKETING' 'SELLING' 'R.D' plan = budget
You might want to base the optimistic and pessimistic data on the best-guess data. For example, optimistic data might be 15 percent higher than best-guess data, and pessimistic data might be 12 percent less than best-guess data. With
line still limited to the input line items, execute the following commands.
plan(scenario 'OPTIMISTIC') = 1.15 * plan(scenario 'BESTGUESS') plan(scenario 'PESSIMISTIC') = .88 * plan(scenario 'BESTGUESS')
The final step in building a scenario model is to write a model that calculates results based on input data. The model might contain calculations very similar to those in the
budget.calc model shown earlier in this chapter.
You can use the same equations for each scenario or you can use different equations. For example, you might want to calculate the cost of goods sold and use a different constant factor in the calculation for each scenario. To use a different constant factor for each scenario, you can define a variable dimensioned by
scenario and place the appropriate values in the variable. If the name of your variable is
cogsval, then your model might include the following equation for calculating the
cogs line item.
By using variables dimensioned by
scenario, you can introduce a great deal of flexibility into your scenario model.
Similarly, you might want to use a different constant factor for each division. You can define a variable dimensioned by
division to hold the values for each division. For example, if labor costs vary from division to division, then you might dimension
division as well as by
When you run your model, you specify
plan as the solution variable. For example, if your model is called
scenario.calc, then you solve the model with this command.
A loop is performed automatically over the current status list of each of the dimensions of
plan. Therefore, if the
scenario dimension is limited to
ALL when you run the
scenario.calc model, then the model is solved for all three scenarios: