After discussing the general capabilities and limitations of databases for strategic planning in The Role of Databases for Strategic Planning – Some General Remarks, we have looked at a case study in a relational database accessed through a special data mining tool: The Role of Databases for Strategic Planning – Case Study in Qlikview/Oracle. In this case, we will look at a case study very similar in the business perspective, but based on a different database concept. The database we will be looking at is Palo or Jedox (all product names mentiones are trademarks of the respective owners), accessed not through an external tool but through the database’s standard access mechanisms. Palo is an open source OLAP database, with an upgraded version under commercial license sold under the manufacturer’s name, Jedox. The case study was done using the latest open source version, Palo 3.1.
Again, we are addressing the issue that a planning database will generally contain input from very different people in different parts of the company, and possibly even from outside partners. Large planning databases may contain some replies to “what-if” questions, but discussing and testing the implications of a large number of future developments with so many contributors will usually be impractical. In most cases, information contributors from local marketing or external market research companies will not have enough time to contribute for extended strategic work. Therefore, it will be necessary to use the multitude of planning figures in the database as the basis for a calculation of scenarios and strategic options defined and evaluated later, at the corporate strategy level.
As in the Oracle/Qlikview example, the case study involves a manufacturer of electronic components, systems and services, operating in different regions with a number of product lines. The existing planning database contains basic sales and cost figures for own products and only sales data for the main competitor products or local competitor groups. Planning figures cover some years of back data plus four years of forecast. For the case study, we assume that all the data ist stored in one database cube. That is not the most efficient way of storing the data, as zeros will be stored for competitor cost data, but probably a realistic way such databases will have been set up for reasons of simplicity. Considering the limited data volume of 60 products in 32 regions for seven years, memory will hardly be a serious matter of concern in this case, anyway. The dimensions of the case study cube are product, region, year and figure (the figures being units sold, net revenue, direct cost and overhead cost).
As we will be using a standard Palo user interface, it will be fairly simple to write data back to the database. Therefore, calculated simulation results can be stored in the database as well, in a different cube to keep them separate from data accessed by other users. The new cube has the simulated business case as an additional dimension, the year dimension is extended by the extrapolation, and the figure dimension stores additional figures calculated in the course of the simulation. A plain database access screen, therefore, looks as follows (original forecast db on the top, simulation db on the bottom, click to enlarge):
The first question to answer in accessing data from a Palo/Jedox database is the interface platform to be used. There are two main data interfaces provided: One is Palo Web, a browser-based data access and manipulation tool that also allows calculations and macros, the other comes in the form of plugins for either MS Excel or OpenOffice Calc. The plugins allow simple access from both tables and macros to the data, which can then be manipulated using the full functionality of the respective program. As OpenOffice is less common in companies, the respective plugin was not tested for this case study.
In determining which solution works best for the simulation, we have to keep in mind which tasks will have to be performed by the tool. The simulation has to access relatively large amounts of data simultaneously, then perform complex calculations based on interactive assumptions. Most of the calculations will have to be done in macros.
With the Palo Excel plugin, a set of almost identical database access functions can be performed either in table cells when a table is recalculated or directly from a macro. As accessing many adjacent database entries from a table can be done simultaneously in an optimized way, this form of db access is much faster than from the macro. In fact, reading the whole data volume characterized above into a table is a matter of seconds. Table recalculation has to be set to manual and managed by macros after that to keep the tool performing at reasonable speed, but that can be done quickly and almost invisible to the user. Once the data is in Excel, the respective table can be copied to a Visual Basic array. The fast Visual Basic compiler with reasonable editing and debugging support allows the convenient development of all necessary macros. Running the extrapolation to the simulation timeframe or an interactive simulation in the described manner, writing the data back to Excel tables and updating the respective displays and graphs is a matter of less than five seconds for our example and thus easily fast enough for interactive work. Writing the calculated data back to the database takes a few minutes, as the writing, as opposed to reading data from the database, has to be done cell by cell. This step can therefore not be part of the regular interactive work, but should rather be offered as a possibility of storing the results at the end of an interactive session.
Palo Web provides a table calculation tool similar to Excel or OpenOffice Calc in a browser window:
Palo Web files are stored on the server rather than locally, which may be interesting if they are to be accessed by several users. Cell manipulation and data visualization capabilities are quite similar to Excel and relatively easy to adjust to, but have their peculiarities and restrictions in some details. The database access functions themselves are practically identical to the ones provided by the standard software plugins. An important difference is the macro engine. Palo Web offers macros in the web programming language php. With its C-like syntax, php is relatively easy for an experienced programmer to adjust to, and it is well documented online. Remarkably, when comparing calculation times with Excel’s rather fast Visual Basic compiler, no significant differences were found. A major drawback of Palo Web’s macro capability, however, is the developing environment. The editor provides at least some basic support like automatic indentation of passages in curly brackets, but debugging is extremely inconvenient. For a developer experienced in Excel, designing the tool surface will also take longer because of differences in the details. After the case study development ran into stability issues with the php macro engine when writing larger sets of data to either a table or a database, a clear preference was given to the Palo for Excel plugin.
The extrapolation of the forecast data read from the Palo database to the full simulation timeline allows selection of assumptions in a way similar to the one described in the Qlikview-Oracle example. The ability to recalculate the extrapolation for single products rather than the whole market is only needed if the extrapolation assumptions are to be varied for different products. If all products are to be extrapolated using the same assumptions, the calculation for the whole market is so fast that the user would have no time advantage by only recalculating only one product. To account for product lifecycles, the default extrapolation is not done in a linear way, but by fitting standardized life cycle curves to the data. Using linear extrapolation instead may be reasonable for competitor data that includes whole product portfolios.
Each simulation calculates the combined effects of a selection of possible future scenarios and the company’s own strategic options. The possibility of combining scenarios is a deviation from classical scenario theory, possible because scenarios are treated as deviations from a baseline plan (the extrapolated numbers from the original planning database). A simulation with its selection of strategies and scenarios can also be stored as a business case. A business case stores the simulation results in the large extrapolation/simulation data cube and the selected scenarios and strategies with their properties in smaller cubes:
The planning tool is designed for a continous strategy process, which is to be used for several years. Over the course of this time, the expectations for the future can change significantly. New scenarios can become thinkable; existing scenarios can be ruled out, and the expected results for scenarios can change. The scenarios and their effects are therefore variable and can be changed interactively. Scenario properties include a name, verbal description and effects on sales potential, price, direct and overhead cost to be set globally or for products, markets, countries or regions. Each scenario can store a combination of up to 10 effects.
The strategy definition is very similar to the scenarios. The difference is that strategies can only affect the company’s own products directly and will only have an indirect effect on competitor products through redistribution of market shares. Strategies can also include adding complete new lifecycles to account for product innovation or the acquisition of competitors.
Once a simulation has been calculated, various visualizations are possible and will be automatically generated in the tool. The simplest visualization is the timeline view for a selected figure in a selected product and region. This view allows a detailed look at all the information calculated in the simulation.
For strategic decisions, more aggregated views may be reasonable. Portfolios are such aggregated displays that decisionmakers will be familiar with. Risk portfolios display the expected value vs. the associated risk for a figure. In this case, the associated risk can for example be defined by the spread of possible results over all scenarios for the selected strategy.
As already mentioned in the Qlikview case study, it must be kept in mind that the purpose of the strategic simulation is not to provide exact numbers on what sales will be in the year 2022 given a certain scenario, but rather to make it clear to what extent that value can vary over all included scenarios. No simulation can eliminate uncertainty, but a good simulation will make the implications of uncertainty more transparent.
If the data to be the basis for such simulations is stored in a Palo/Jedox database, it is reasonable to make this database a part of the simulation. In this case, separate cubes in the same database can be used to store simulation results and assumptions. In the case study, both Palo Web and Palo’s MS Excel plugin have been found to be usable interfaces to integrate the simulation tool into, but the plugin has turned out to be the slightly faster and significantly more stable solution, with advantages in development effort, as well.
Dr. Holm Gero Hümmler
Uncertainty Managers Consulting GmbH