The Role of Databases for Strategic Planning – Case Study in Palo/Jedox

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

The Role of Databases for Strategic Planning – Case Study in Qlikview/Oracle

In The Role of Databases for Strategic Planning – Some General Remarks, we have looked at the increasing role databases seem to have in strategic planning and the difficulties that arise in including future uncertainties in this kind of planning.

It appears quite impractical to get all the contributors to include possible future uncertainties in their input. First of all, they would have to generate massive amounts of data, costing them significant time beside their everyday work, which is often in marketing, market resarch or sales rather than planning. Second, they will probably have very different ideas and approaches to what might change in the future, making the results difficult to interpret. In addition, database structures tend to resist change, so adjusting them to possible new developments someone foresees can be a lengthy and resource-intensive process.

Uncertainty based planning can, however, be implemented building a simulation on existing, single-future planning from a database, making it possible to vary assumptions ex-post and allowing the user to build his different scenarios and strategies based on the information provided by all the different contibutors to the database. The simulation should build on the infrastructure and user interfaces generally used to get information from the database, so the implementation will be quite different depending on the given framework.

For the case study, let us look at a manufacturer of electronics components, integrated systems of these components and services around these components for different industries. 32 sales offices define the regional structure, which is grouped in 2nd level and top level regions. There are 21 product lines with individual planning, and market research gathers and forecasts basic data for 39 competitor product lines or competitor groups. Product lines are grouped into segments and fields of business. Strategically relevant figures in the database are units sold, net revenue and, available only for own product lines, direct cost and overhead cost. There are a few years of historical data besides forecasts for the coming four years, which is sufficient for the operative planning the database was intended for, but rather short for strategic decisions like the building of new plants or the development or acquisition of new products. The strategically relevant total data volume therefore is small compared to what controlling tends to generate, but rather typical for strategy databases.

In this case, we will look at a relational (e.g. Oracle – all brands mentioned are trademarks of their respective owners) database accessed through the Qlikview business intelligence tool. As Qlikview defines the only access to the data generally used by the planner, the actual database behind it, and to a certain extent even its data model, will mostly be interchangeable. The basic timeline view of our case study database looks as follows:

To effectively simulate the effects of the company’s decisions for different future developments, we have to extrapolate the data to a strategic timescale and calculate the effects of different external scenarios and own strategies. Qlikview, however, is a data mining tool, not a simulation tool. Recent versions have extended its interactive capabilities, introducing and expanding the flexibility of input fields and variables, but generally, Qlikview has not been developed to do complex interactive calculations in it. Future versions may move even more in that direction, but the additional complexity needed to include a full-fledged simulation tool would be immense. To do such calculations, one has to rely on macros to create the functionality, but Qlikview macros are notoriously slow, their Visual Basic Script functionality is limited compared to actual Visual Basic, and the macro editing and debugging infrastructure is rather… let’s say, pedestrian. Some experts actually consider it bad practice to use macros in Qlikview at all.

The only way around this limitation is to move the actual calculations out of Qlikview. We use Qlikview to select the data relevant for the simulation (which it does very efficiently), export this data plus the simulation parameters as straight tables to MS Excel or Access, run the simulation there and reimport the results.

Now, why would one want to export and reimport data to do a calculation as a macro in Excel, in essentially the same language? VBScript in Qlikview is an interpreter: One line of the macro is translated to machine code and executed, then the next line is translated and executed, using massive resouces for translation, especially if the macro involves nested loops, as most simulations do extensively. VB in MS Office is a compiler, which means at the time of execution, the whole macro has already been translated to machine code. That makes it orders of magnitude faster. In fact, in our case study, the pretty complex simulation calculations themselves consume the least amount of time. The slowest part of the tool functionality is the explort from Qlikview, which es even slower than the reimport of the larger, extrapolated data tables. In total, the extrapolation of the whole dataset (which only has to be done once after a database update) takes well under than a minute on a normal business notebook, which should be acceptable considering the database update itself from an external server may also take a moment. Changing extrapolations for single products or simulating a new set of strategies and scenarios is a matter of seconds, keeping calculation time well in a reasonable frame for interactive work.

In most cases, a strategic planner will want to work with the results of his interactive simulations locally. It is, however, also possible to write the simulation results back into equivalent structures in the Oracle database, another functionality Qlikview does not provide. In that case (as well as for very large amounts of data), the external MS Office tool invoked for the calculation is Access instead of Excel. Through the ODBC interface, Access (controlled in Visual Basic, started by Qlikview) can write data to the Oracle database, making simulation results accessible to the selected users.

The market model used for the extrapolation and simulation in the case study as rather generic. Units sold are modeled based on a product line’s peak sales potential and a standardized product life cycle characteristical for the market. A price level figure connects the units to revenue; direct cost is extrapolated as percent of revenue and overhead cost as absolute numbers. The assumptions for the extrapolation of the different parameters can be set interactively. Market shares and contibutions margins are calculated on the side, leading to the following view for the extrapolation:

Of course, depending on the market, different and much more complex market models may be necessary, but the main difference will be in the external calculations and not affecting the performance visible to the user. Distribution driven markets can include factors like sales force or brand recognition, whereas innovation driven markets can be segmented according to very specific product features. Generally, the market model should be coherent with the one marketing uses in shorter term planning, but it can be simplified for the extrapolation to strategic timescales and for interactive simulation.

If a strategic simulation is developed for a specific, single decision, scenarios with very specific effects including interferences between different scenarios and strategies can be developed in the project team and coded into the tool. In the present case study, a planning tool for long-term use in corporate strategy, both scenario and strategy effects are defined on the level of the market model drivers and can be set interactively. Ten non-exclusive scenarios for future developments can be defined, and for each scenario, ten sets of effects can be defined for selected groups of regions and products. Scenarios affect both own and competitor products and can have effects on sales potential, price, direct and overhead cost.

Strategy definitions are very similar to scenario definitions, but strategies can only affect sales potential and price of own product lines. To account for the development or acquisition of new products, they can also add a life cycle effect, which can either expand the market or take market shares from selected or all competitors in the respective segment.

As opposed to classical scenario theory, the scenarios in this case study are non-exclusive, so the impacts of different scenarios can come together. Strategies can also be combined, so a strategy of intrinsic growth could be followed individually or backed up with acquisitions. A predefined combination of scenarios and strategies can be stored under a business case name for future reference.

Various visualizations of the evaluated data are automatically generated for interactive product and region selections using the Qlikview tools. In this case study, the linear timeline graph displays the baseline planning in comparison to the timeline after the current scenario and strategy settings for a selected figure. For certain figures the maximum and minimum values over all scenarios for the selected strategy are also displayed.

Risk portfolios can display expected values vs. risk (variation all scenarios) for a selected strategy. Qlikview makes creating these portfolios for selected product and regions and aggregated over adjustable parts of the timeline very convenient. As desired, other types of portfolio views (e.g. market share vs. market size) can also be created.

In all these cases, 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.

Qlikview does not support these simulations directly, but using the workaround of external calculation, the user-friendly interface Qlikview provides allows convenient selection of values and assumptions as well as quick and appealing visualization of results.

In an upcoming case study, we will look at the same business background implemented in a very different database and interface environment.

Dr. Holm Gero Hümmler
Uncertainty Managers Consulting GmbH