Planning, Budgeting & Forecasting Processes – “The Planning Data Mart”?
29th October 2007 Harnessing the benefits of modern planning technology depends on positioning it correctly within the overall performance management systems framework. A symbiotic relationship exists between the three main components of this solution, planning models, business intelligence tools, and the data mart that underpins both of these. In the second of a series of six articles, FSN contributing editor Steve Bows explores the role of a small and carefully-scoped data mart in delivering a robust environment for the planning process to occur.
The Ideal Performance Management Framework
Laying the Foundations - The Performance Management Data Mart
Data warehousing is an IT discipline which has arisen from the need to analyse the massive data volumes being generated by transactional systems within the modern business. Relational transactional records must be converted into a form that can be more readily analysed, and this involves identifying the “dimensions” (ie structures for analysis) embedded within the data and converting this data into a multi-dimensional grid of relationships. This can then be more easily interrogated by an analysis tool in a slice-and-dice manner (a bit like how an Excel Pivot Table works).
Multi-Dimensionality – an Example
The easiest way to imagine this multi-dimensional framework is by analogy to a standard monthly reporting pack – it may contain 50 pages of profit & loss reports, cycling through each business unit; here the Bristol office (page 1) represents the first business unit in the list. The rows down each page are the profit & loss accounts, and in the multi-dimensional world these items are a subset of the “Account” dimension. The columns may represent two dimensions – firstly the “Version” (Actual/Budget) and also “Time” (MTD and YTD for August 2007). These two dimensions are nested together, that is to say the Actual vs Budget analysis is repeated for both MTD and YTD. Finally, the 50 pages correspond to the items in the “Business Unit” dimension. In other words, we have a four-dimensional report.
For this example, the main benefit of using a data mart/warehouse (and associated metadata framework) as a translation layer between the data source (General Ledger) and report is to enable dynamic updates of both dimensional and fact data. When ABC Professional Services opens an office in Newcastle and decides that it wants to report Forecast data (as well as Actual and Budget) in the monthly pack for all of its business units, the data mart understands that these are new items in the Business Unit and Version dimension respectively. The definition of the report refers to these dimensions, and dynamically updates the report to add two more columns (MTD Forecast and YTD Forecast) and an extra page (the report for Newcastle ). Any transactions relating to these new dimensional items are thus automatically summarised and reported without the need for finance users to intervene.
Multi-Dimensional Planning
Extending this analogy to the Excel data entry templates used by most organisations for the budgeting process is straightforward – in many cases they look very similar to a report like the one above, the difference being that rather than reporting data FROM the multidimensional framework, they require data entry TO it. Of course, it is rare that an organisation provides a completely blank template for input – some reference data is provided to enable base-plus budgeting, so information is flowing both ways. Using data dumps straight from the General Ledger, and uploading back to it, without the benefit of the data mart to regulate and translate this data flow, it is easy to see how information can get lost and misconstrued, especially if there are changes to the dimensions during the process.
Dealing with Multiple Data Sources
If that was not enough, let's add another piece of complexity to the equation. As well as as the expense budget template, which tends to require input for a set number of (discretionary) expense lines, sourced from the General Ledger, most business units or cost centres are required to fill in a more detailed staff budget, outlining who they are planning to hire/fire/promote, and roughly when, during the year. This requires information about which employees belong to each business unit, and some key attributes relating to them – eg Full-Time/Part-Time, Permanent/Contractor, Grade – that will enable business unit managers to plan the impact on staff costs. In most cases, this information is stored in a payroll system distinct from the General Ledger, and is far less straightforward to extract and model in Excel (leaving aside for a moment the obvious data security issues of doing so). In particular, the Business Unit reference for each employee is often out of date (the General Ledger tends to be the sourse of truth for this) and so each employee's data must be painstakingly reviewed each time an extract is taken.
This is where the data mart comes into its own – by isolating a new dimension (Employee) and an overlap with an existing dimension (Business Unit) it can help clean and order the data before it reaches the planning models. Planners are presented with a one-stop shop for all their data requirements, structured in a consistent fashion, with the side-benefit of far greater data security than the spreadsheet stack.
Small is Beautiful
Whilst the benefits of a data mart or warehouse underpinning the performance management framework are clear, it is well to tread carefully when proposing such a project to experienced IT managers. Many bear the scars of previous attempts to design an all-encompassing enterprise-wide data warehouse, that have become bogged down in ever-changing business requirements, chewing up large project budgets and delivering very little perceived value.
The trick, as with most projects, is to start small and get a quick win before starting work on the next step. By all means start with a strategic review of the operational systems and potential business requirements, but only tackle one data source at a time once the development work starts. For a Performance Management data mart, the General Ledger is the best place to start, followed by (as highlighted above) the payroll system. The integration of other operational systems can follow if these two are successful, but is certainly not essential to the planning process of most businesses.
Fortuitously for our purposes, the General Ledger is often the best-maintained and most logically structured of all business systems, making it easy to isolate dimensions and related fact data. A good ETL (Extract, Transform, Load) consultant can develop this basic data mart in a few weeks, laying the foundations for a successful planning process with minimal cost and risk.
The Next Step – Performance Management Applications
Once the basic data mart is in place, you're ready to move to the next stage – using Business Intelligence tools to analyse and make sense of what your history is telling you, and then enterprise planning models to help develop your response. We'll consider the difference between these two types of application, and how best to use them, in the next article.
