You are here

The role of the data warehouse in a Planning & Budgeting solution


As solutions for Planning & Budgeting further merge with solutions for "classical” Business Intelligence, Performance Management professionals increasingly need to reflect on an information technology architecture that delivers on the promise of integration of both historical & future data.
First, we will look into the most common reasons for deploying Planning & Budgeting solutions. Secondly, we will address the optimal architecture in which to fit these systems and what crucial role the data warehouse needs to play.

The problem called ‘Excel'

Even today, research shows that the standard tool used for budgeting in two thirds of the large corporations, is nothing more than Microsoft Excel. While this is a very useful personal productivity tool & calculation engine, it is arguably not a good enterprise-wide application for planning, budgeting and forecasting. It is beyond the scope of this article to go into the details on the disadvantages of a spreadsheet, but we will just highlight some of the main problems.

Flexibility. Excel's strength is one of its main weaknesses. The fact that it is a calculation engine rather than an application means that it is often too easy to change the layout, the data structure & the content of budget input-sheets.

Consolidation. Spreadsheets cannot be consolidated automatically, this is often a manual –including gathering attachments from email communication- and therefore error-prone process. Furthermore it is not real-time or on-line, but disconnected.

Workflow. In complex organizations, where budget responsibility is spread throughout the organization, it is essential to track the workflow and status of each of the individual budget items. This is a nightmare when using a combination of Excel and e-mails.

Security. Implementing functional and/or data security in Excel is not a standard feature of the tool and therefore difficult and cumbersome to realize.
Typically, Planning and Budgeting tools address these and other issues. They work through a web-interface based on a central repository. These tools are always based on the dimensional modeling paradigm and provide strong security and workflow features. Some of the tools try to combine the best of both worlds and provide an Excel front-end interface based on the more robust architecture.

These spreadsheet-related issues prevent us from realizing the true potential of Enterprise Planning & Budgeting. The main advantages these solutions offer include the following:

Reduce budgeting cycle. By allowing speedier planning and budgeting, the typical budgeting cycle can be reduced, allowing a more fine-tuned forecasting process.

Driver based budgeting. Business drivers, model definitions and assumptions can be centrally maintained and shared across the enterprise, giving the Financial department better control over the budgeting process. By moving to driver based budgeting, the process becomes more objective and transparent allowing organization to go "beyond budgeting”.

Support international & global scope. These applications have strong support for multi-currency budgets, various exchange rates and multiple views on for example, chart of accounts.

Unified view on financial data. When implemented right, a Planning & Budgeting solution can bring together past and future financial data, while ensuring business logic and respecting corporate data definitions.

Deployment of budgets and plans. Enterprise Planning & Budgeting applications allow for top-down breakdown of plans and budgets throughout the whole organization.

Finance versus IT

The above builds a strong case for Planning & Budgeting solutions. It would be fair to say that the first to benefit from such solutions typically will be the Finance department. Therefore the Finance department often acts as sponsor for this type of projects. In fact, a Finance department could even be so strongly convinced of the added value of this solution that it makes the mistake to implement this as quickly as possible, as a stand-alone solution.

While this would definitely address some of the issues mentioned, this would also represent a missed opportunity. By not making Planning & Budgeting an integral part of the Performance Management architecture, we create a separate and potentially different stream of financial data, something we obviously want to avoid in striving to build integrated Performance Management. This situation is represented in drawing A.

Drawing A. An unintegrated architecture

In such a setup, the accounting system will become the source of at least two data streams, one that leads to the data warehouse (for historic, regulatory and compliance reporting) and one that will feed the budgeting solution. There might be even more streams if one would opt for similar "stovepipe” approaches for setting up the consolidation or Activity Based Costing applications. While these different streams may initially be identical, there is an imminent threat that in time (because of the maintenance difficulties and the fact that this may involve different development groups), these two processing logics will begin to differ. And this will translate itself into discrepancies in actual numbers reported from the data warehouse and actual numbers reported from the budgeting solution.

Such a situation will ultimately result in a lack of confidence by the users in the Planning & Budgeting solution in general. So all the investments made in software, hardware and people designed to bring about all the benefits mentioned in the previous paragraph, will not have a long-term result if its place in the overall architecture is ill-conceived.

Integrated process and architecture

We propose an architecture in which the data warehouse plays a central and crucial role. This is represented in drawing B.

Drawing B. An integrated architecture

In this architecture, the process of extracting numbers from the accounting system occurs only once. This means no risk of duplicate logic and obviously also a reduction in development and maintenance efforts. The data warehouse is the one and only location where correct historical financial figures are stored. These can be used as the basis for the budgeting cycle by loading them into the Planning & Budgeting application. As these applications are also based on the dimensional modeling principle, a structure that can also be found in the data warehouse, this data loading process is usually quite straightforward. Furthermore, the data warehouse can provide the Planning & Budgeting tool with all the relevant dimensional information from the conformed dimensions that it meticulously maintains.

Data is not reported from the planning application, but the target values for planning or budgeting or the forecasts are written back to the data warehouse and reported / analyzed through the standard tools that have been deployed as part of the enterprise's overall Performant Management architecture.

This architecture gives the data warehouse the central role it deserves as the single source of the truth for both budget, plan & forecast and actual values. In summary, it provides the following advantages :

Consistent view on data. By reducing the number of places were financial data is stored to just one location, data consistency and therefore reliability is significantly increased. An additional advantage is that this architecture presents all financial information to end-users in the standard reporting interface that they are already used to lowering the need for end-user training.

Leverage data warehouse investments. By integrating all financial data in the data warehouse, we can reuse some features, such as existing reports, data quality checking procedures, ETL logic, Master Data management architecture and dimension maintenance.

Business driver analysis. The data warehouse often contains more than just financial data. By integrating past and future financial data with more operational data, this opens up the possibility to perform in-depth analysis on a combination of the two.

Shared security. Integration will substantially ease the burden of system administrators who need to maintain system and row-level data security.

Bottom Line

In summary, it is clear that a crucial aspect of a successful and lasting deployment of an Enterprise Planning & Budgeting solution finds it place in the BI architecture. While it is clear that Planning & Budgeting is mainly concerned with future data, whereas BI typically deals with current or historic data, there is no reason to treat the process or architecture differently. Integration through a central data warehouse that contains a unified view on all financial data is crucial to ensure long-term ROI for these initiatives.