Integrating Dynamic Simulations With An OLAP System
Fabian G onzalez
Powersim Corporation
1537 Webster St, Suite 200
(510) 628-3600
fgonzalez@ powersim.com
Abstract
This paper explains how a business simulation created in Powersim Studio can be linked to an
online analytical processing (OLAP) system in order to provide enhanced strategy planning,
forecasting and what-if scenario analysis.
OLAP systems and data warehouses (DW) allow corporations to collect and aggregate
operational data with the aim of performing analysis. The data in the data warehouse form a
blueprint for the company’s history and past performance, and by studying these data, a
corporation can streamline performance, correct its course, and in general be better prepared to
face an uncertain future.
Although OLAP systems are excellent analytical tools, they operate by analyzing historical data,
and can only offer limited forecasting and scenario planning capabilities. By using a business
simulation to perform the forecasting and scenario planning, an OLAP system can he extended
into a very powerful analytical planning tool.
Keywords: OLAP, Business Intelligence, Business Simulation, Strategic Planning, Decision
Support.
Using Simulations to Perform Data Analysis
The power of an OLAP system lies in its ability to analyze data - the A in OLAP. Both
comparative and projective calculations can be performed in order to determine variances, ratios
and trends. Also, time-series analysis, forecasting and what-if modeling functions can be used to
help project into the future. Overall, OLAP systems are very powerful Business Intelligence (BI)
tools that help analysts get a comprehensive overview of large and complex data. They are
excellent tools for identifying and analyzing trouble spots in the operations landscape, and for
determining their causes by inferring trends.
Traditional OLAP systems perform forecasting and what-if scenarios by extrapolating trends,
adjusting numbers and by performing statistical calculations. Although this kind of analysis is
useful, it fails to take into account the interactions and feedback mechanisms at work in any large
and complex system. Also, often lacking from the analysis are data traditionally not stored in the
data warehouse - data about the market (total market size, growth rate, demographic
information, customer trends and preferences, etc.) and soft variables (e.g. customer and
employee satisfaction, brand and product awareness, marketing effectiveness).
Enter business simulations: a dynamic simulation can capture the dynamic complexity of a
company and its departments, divisions, customers and markets to provide powerful analysis and
forecasting capabilities, including sophisticated what-if scenario planning that takes into account
soft variables as well as the relationships and feedback mechanisms making up the system. For
example, a dynamic simulation can take into account factors like market saturation, price
elasticity, competitor reaction to pricing and market cannibalization when performing analysis.
Even complex relationships like how employee satisfaction impacts customer satisfaction, which
again affects sales, price sensitivity and the effect of marketing campaigns can easily be
modeled.
Thus, while traditional OLAP systems are good at organizing, visualizing and analyzing
historical data to find current trouble spots and their causes, business simulations excel at
forecasting and what-if modeling, advising on future courses of action.
Business Intelligence Using Powersim Studio
Powersim Studio is a system dynamics tool specialized for building simulation models to
improve business performance. The software is easy to use, has powerful reporting capabilities
and can connect to a variety of external data sources, including SAP’s Strategic Enterprise
Management (SEM) solution. The software enables corporations to build and deploy business
simulation models for training, analysis, decision support, scenario planning and knowledge
management, among others.
Figure 1 shows the development, execution and maintenance cycle for a simulation model built
as part of an OLAP solution. First, a simulation model is built from information gathered about
the company and its operations. This step often includes interviews with subject matter experts,
managers and executives within the company in order to build a valuable, accurate simulation
model.
Build /Revise
es Model
Calibrate Model
a w/OLAP \
Assumptions
~ Scenarios
and Strategies
Analyze Business
Results w/OLAP
Run Simulation
w/Powersim Studio
Implement
Strategy Analyze Simulation
NN Results w/OLAP
Select ee
Strategy
Figure 1: The business simulation development, execution and maintenance cycle
This detailed simulation model is then linked to the OLAP system. This OLAP system can either
be an existing system within the company, or a separate hypercube built expressively for use
with the simulation model.
Once the model is connected to the historic data in the OLAP cube, the model is calibrated to
provide matching or near-matching results to the actual data. Another Powersim tool, Powersim
Solver, can be used to aid in this process. If model calibration is not possible, the assumptions
behind the simulation model are revisited and refined, and the business simulation updated, until
the desired results are achieved.
After calibrating the simulation model, analysis can be performed. Different assumptions and
decisions can be entered into the simulation model to test strategies and scenarios, to study the
complex environment that is modeled, or to do financial forecasting. Using Powersim Solver, it
is also possible to perform sensitivity and risk analysis, or to optimize the simulation model for a
set of goals.
Once a satisfying set of decisions has been determined, often in combination with the use of
other analysis tools, the strategy can be implemented by company management. For example, the
simulation model can be used as an aid in determining next month’s production numbers. These
figures are then sent to the plant managers, who set the plant’s operations in correspondence with
these numbers.
Finally, the business results from the implemented strategy are gathered and evaluated using the
OLAP system. It is also possible to compare the real life results with the simulated results from
before the strategy was put into effect. If the actual results from operations differ from the
simulated results, the contributing factors and causes can be determined, and the simulation
refined with renewed assumptions to provide more accurate results, if necessary. The same
applies if the execution of the chosen strategy leads to new insights about the company and its
business environment.
Optimizing Business Performance at Frank’s Foods: A Fictional C ase Study
The following fictional case study explains in practical details how a business simulation tool
can work together with an OLAP system to provide more powerful business analysis.
Frank’s Foods, a fictitious food and beverage company with three product lines (Ice Cream,
Carbonated Beverages and Chocolate) and four sales regions (West Cost, Midwest, Central and
East Coast) wants to forecast and track revenue growth over the next twelve months.
At the end of the year, the company examines it’s sales record for the last year, as well as the
overall company performance, makes assumptions about the market in the year ahead, and
combines this knowledge to create the official forecast for the next year. This forecast for the
beverage division is shown in Figure 2.
Revenue, Beverage Division
GHistoric Values
Gofficial Forecast
Revenue (Thousand $)
w
3
s
oF > © & ODM he © Oo P Ph ah
Month
Figure 2: Last year’s revenue and next year’s revenue forecast for product group A.
Extending OLAP Functionality with a Business Simulation
In addition to creating a traditional forecast, the company can use its knowledge about itself and
its products, customers and the market to create a dynamic simulation model using Powersim
Studio. This dynamic simulation model can then be calibrated using Powersim Solver to fit last
year’s sales data, stored in the data warehouse, and simulated to provide sales forecasts for the
next year. By running the simulation model repeatedly with inputs that represent varying market
assumptions, a set of scenarios can be created that reflect how the business will perform given
these market conditions. Figure 3 shows how two of these scenarios (High Growth and Low
Growth) can be superimposed on the historic values and the official forecast.
Revenue, Beverage Division
GBB Historic Values
official Forecast
~~ Low Growth Scenario
—&— High Growth Scenario
Revenue (Thousand $)
Figure 3: Simulated sales forecasts under varying market conditions (high growth and low growth).
As seen in Figure 3, both simulated scenarios forecast lower revenue than the official forecast. If
the company has high confidence in the official forecast, i.e. the official forecast has historically
been fairly accurate, the simulation model can be refined and extended until the official forecast
is more closely met.
Alternatively, if the company has less confidence in the official numbers, the first year the
simulation is used can be a test bed for how accurate the simulation model is. The simulated
results can be compared with the actual results at the end of the year, and the model extended to
improve accuracy, if necessary. Conversely, if the simulation model proves to be more accurate
than the officially forecasted numbers, it can be taken into account when creating the official
forecast in the future.
Analyzing Actual Results Using OLAP
Now let's assume that five months have passed into the new year, and that the actual sales results
look like those in Figure 4, below.
Revenue, Beverage Division
GBB historic Values
official Forecast
~~ Low Growth Scenario
—&— High Growth Scenario
—* Actual Values
Revenue (Thousand $)
Figure 4: Actual sales results five months into the new year.
The results now indicate a shortfall in revenue, both compared to the simulated scenarios, but
especially compared to the official forecast. A closer investigation is in order. Just like a
traditional OLAP system, the user has the option to drill down into the chart and search for the
region that is causing the shortfall (see Figure 5).
Revenue, Beverage Division, Midwest Region
GBB historic Values
official Forecast
~~ Low Growth Scenario
—&— High Growth Scenario
—* Actual Values
Revenue (Thousand $)
Figure 5: A drill-down into the Midwest region reveals the origin of the revenue shortfall.
As Figure 5 shows, the Midwest sales region is experiencing a significant shortfall in the sale of
beverages. The question is: why?
Once the problem area has been found, the company can start looking for causes. Maybe the
demand for Frank’s beverages was overestimated. There might be delivery problems caused by a
delay in production. Customer service in the Midwest region might not be up to par, or another
company might have launched a competing product, leading to lower product sales. This is
where the OLAP systems come in. Traditional OLAP systems are excellent tools to help
understand the reasons behind the numbers.
Analyzing Solutions Using Powersim Studio
When the causes have been identified, the company can start looking for solutions. If the
company has overestimated demand, strategies might include launching an advertisement
campaign, lowering prices, promoting a different product, or modifying the product line. If the
problem is internal, such as a delivery problem caused by an ineffective supply chain, a good
option might be to invest in the supply chain, change manufacturer or launch a promotional
campaign to repair customer loyalty.
This is where the real power of Powersim Studio’s dynamic simulation models comes into play.
Assuming that the affected subsystems (finance and pricing, marketing, supply chain, and/or
production) are included in the simulation model, the user can test out various strategies by
entering decisions into the simulation model. And if the desired subsystems are not yet included
in the simulation model, the simulation can be extended by adding new subsystems to the model.
Simulation models created with Powersim Studio are very flexible, and the model can easily be
refined as needed as new details about the business and the market emerge.
After the model has been reviewed and refined as necessary, the simulation is recalibrated to
match the new reality, as shown in Figure 6. At this point, the company might also conclude that
the low growth scenario more closely represents the current market conditions, and use this
scenario for strategy testing.
Revenue, Beverage Division, Midwest Region
BEB Historic Values
official Forecast
~—@ Low Growth Scenario
High Growth Scenario
—*¥ Actual Values
Revenue (Thousand $)
Figure 6: Recalibrating the simulation model to match the new actual results.
The company now has the option to test out different strategies to improve the lagging sales
figures. Figure 7 shows the results of three possible strategies, each representing a different set of
decisions entered into the simulation model, using the market conditions set by the lower growth
scenario.
Revenue, Beverage Division, Midwest Region
BEB Historic Values
official Forecast
~~ Reduced Price
—4— Promotional Campaign
‘© Improve Supply Chain
—~ Actual Values
Revenue (Thousand $)
Figure 7: Testing out various business strategies to respond to a revenue shortfall.
As seen in Figure 7, the first strategy looks like the best strategy for the company. However, the
decisions that make up a strategy do not necessarily only have local impact. For example, a price
break in the Midwest might affect sales in the Central region. Or an investment in the supply
chain might benefit all sales regions. To get the full picture, it is therefore necessary to drill back
up to the product level. Figure 8 illustrates this. According to Figure 8, the third strategy gives
the best results, and not the first, as indicated at the regional level, in Figure 7.
Revenue, Beverage Division
BEB jistoric Values
[official Forecast
~~ Reduced Price
—#— Promotional Campaign
© Improve Supply Chain
—E Actual Values
Revenue (Thousand $)
Figure 8: Strategy results at the product group level
None of the tested strategies succeed in reaching the officially forecasted revenue numbers. The
company may choose to test additional strategies, or opt to adjust the official forecast.
Managing Risk With Powersim Solver
Given that a set of strategies may play out differently given varying assumptions about the
market, it may be useful to perform some sort of sensitivity or risk/benefit analysis on the chosen
strategies under varying market conditions.
Using sensitivity analysis, it is possible to determine the significance or range of impact of
individual decisions on results, as opposed to the total impact of a given strategy, which typically
includes many variables.
Similarly, risk analysis can be used to evaluate how sensitive a strategy is to changes in the
underlying assumptions. Different strategies might have different sensitivity to changes in
market conditions. For example, some strategies might be very robust in the face of an uncertain
future, while other strategies fall apart when the underlying assumptions fail.
Both types of analysis can be performed using Powersim Solver. It is thus possible to build an
even more powerful analytical solution by using the full range of capabilities that a statistical
tool like Powersim Solver has to offer.
Conclusion
Both OLAP systems and business simulations are powerful tools on their own. Both fulfill a
specific purpose: OLAP systems analyze the past; business simulations evaluate the future.
By integrating business simulations with an OLAP system, and by hamessing the full power of
Powersim’s Studio and Solver products, it is possible to build a very powerful analytical solution
with excellent forecasting and scenario planning capabilities far exceeding those of traditional
business intelligence products.
