June 1, 1999, Volume 2 - Number 8
Track your department budgets, commitments, and actuals with a dimensional model
Ralph Kimball
The data mart value chain is one of the most powerful
ways to use dimensional models. Most of us are familiar with the traditional, product value chain in which a product moves from the manufacturer’s finished goods inventory down through the distribution pipeline all the way to the retailer. There are usually operational systems at every point in this flow recording static inventory levels and dynamic product movement past particular points. Each of these operational systems can be the source of a data mart.
When we design the data marts for the successive steps of the value chain, we take great care to recognize the common dimensions shared by the various steps of the chain. In a product value chain, such as the one shown in Figure 1, page 62, the obvious common dimensions are product, time, and store. Notice that all the data marts share the product and time dimensions, but only some share the store dimension. When all the common dimensions are carefully prepared to be identical (or perfect subsets of each other) up and down the value chain, we say that the dimensions are conformed.
A properly designed value chain with conformed dimensions is immensely powerful. Not only can we implement the separate data marts at different times, but query tools and report writers can combine them at any time into integrated views of the whole value chain, as long as the dimensions have been carefully conformed.
FIGURE 1 This value chain represents a flow of product. Each process is a fact table.
I have used the example of the product value chain so many times that it may come as a surprise that there is a more common value chain lurking in all our businesses: the budgeting chain.
We are all familiar with the process of creating organizational budgets. Typically, prior to a fiscal year, each department manager creates a budget, broken down by line items. The budget is approved before the year starts, but there are usually adjustments to the budget as the year progresses, reflecting changes in business conditions or the realities of how the spending matches the original budget. Depart-ment managers want to see the current budget’s status, as well as how the budget has been altered since the first approved version. I will start the budgeting chain (see Figure 2, page 64) with a fact table called “budget.”
The budget records only what a department is allowed to spend. The second fact table in our budgeting chain, the “commitment” table, records the department’s budget spending commitments, such as purchase orders, work orders, and various forms of contracts. Department managers want to compare commitments to the budget frequently in order to manage their spending. In our design, we will accomplish this by drilling across the budget and commitments fact tables.
FIGURE 2 The budget chain showing the budget, commitments, and payments fact tables. The grain of all three tables is the net change in an account under a department line item in a particular month.
The third fact table in the budgeting value chain is called “payments.” A payment is the actual cash transfer to the party named in a commitment. Department managers are less concerned with these actual payments than accountants, but are concerned about spending within their budget constraints. From a practical point of view, the money is gone from the budget when the commitment is made. But the finance department is intensely interested in the relationship between commitments and payments because it must manage the company’s cash.
The Grain of the Budgeting Chain Fact Tables
To flesh out the details of our budgeting chain, we will follow a simple four-step methodology that I have demonstrated many times before:
1. Identify the business process.
2. Declare the grain of the fact table.
3. Choose the dimensions.
4. Choose the facts.
I have already identified the three business processes: budget, commitments, and payments. We now need to choose a common grain for all three fact tables. The grain choice will make query and reporting applications as simple as possible, appeal to the user’s conceptual model of the budgeting process, and remain cognizant of administrative complexity and storage overhead.
We could choose the grain to be every detailed transaction posted separately to the budget, commitment, and payment operational systems. If we were frequently interested in drilling down to specific transactions as an auditor would, we would choose this grain.
Alternatively, we could choose the grain to be the current status of each line item in each budget each month. Although this grain has a familiar ring to it (because it feels like a manager’s report) it is a poor choice as the grain of the database. The facts that we need to store in such a “status report” are all semiadditive balances, rather than fully additive facts. Also, such a grain makes it difficult to determine how much has changed since the previous month or the previous quarter, because we have to obtain the records from several time periods and then subtract them from each other. Finally, this grain choice requires the fact table to contain many duplicated records when nothing changes in successive months for a given line item.
The grain we will choose is the net change of the line item (of the budget, commitments, or payments) in a department that occurred solely during the current month. We break this down further to the specific general ledger account affected.
Budgeting Chain Fact Table Dimensions
The third step of our methodology is choosing the dimensions for each fact table. If we have made the grain statement carefully, choosing the dimensions is easy. Given the grain statement of the previous paragraph, we can state the dimensions of the budget fact table shown in Figure 2:
•Effective month
•Line item
•Department
•GL account.
The effective month is when the budget changes are posted to the system. The first entries for a given budget year would show effective months in the preceding year when the budget is first approved. If the budget is updated or modified as the budget year gets underway, the effective months would occur during the budget year. If we don’t adjust a budget at all throughout the year, then the only entries would be the first ones when the budget is first approved. This is the meaning of the net change requirement given in the grain definition. Make sure you understand this point, or you won’t understand what is in this database.
The line item identifies the purpose of the proposed spending, such as employee salaries, computer equipment, or office supplies. The line item also identifies the budget year by name.
The department is the name of the organization subdivision overseen by a particular manager.
The GL account is the general ledger account against which budgets are created, commitments are made, and payments are issued. In general, there may be more than one GL account affected by a budget line item. The data mart team either needs to allocate budgets, commitments, or payments into the individual GL accounts with a simple proration scheme, or it must be able to identify the specific GL account from the underlying operational systems.
We can state the commitments fact table dimensions easily: all the budget fact table dimensions, plus commitment. The commitment dimension identifies the type of commitment (purchase order, work order, or contract) together with the party to whom the commitment is owed. Similarly, the payments fact table dimensions are: all the commitments fact table dimensions, plus payment. The payment dimension identifies the type of the payment (such as a check) and the party to whom the payment was actually made. In the budgeting chain, we expand the list of dimensions as we move down the chain from budget to commitments to payments.
The Facts in the Budgeting Chain Fact Tables
Our grain statement makes choosing facts very simple. Each fact is a single fully additive amount, which in the United States is a simple dollar value. We need only one fact in each table. The budget fact is “budget amount,” the commitment fact is “commitment amount,” and the payment fact is “payment amount.”
Applications across the Budget Chain
With this design, we can create a numanalysis applications including all the obvious standard reports a manager needs. To look at the current budgeted amount by department and line item, we constrain on all times up to the present, adding the amounts by department and line item. Because the grain of all the tables is the net change of the line items, adding up all the entries over time does exactly the right thing. We end up with the current approved budget amount, and we get exactly those line items in the given departments that have a budget. Line items in the chart of accounts that have no budget in a department do not appear in the report nor do they occupy any records in the database.
To ask for all the changes to the budget for various line items, we simply constrain on a single month. We’ll report only those line items that experienced a change during the month.
To compare current commitments to the current budget, we separately sum the commitment amounts and budget amounts from the beginning of time to the current date (or any date of interest). We then combine the two answer sets on the row headers with a simple sort-merge process. This is a standard drill-across application using multipass SQL, and it is the way all serious report writers function.
To compare cash payments to commitments, we perform the same kind of drill-across application as the preceding, but this time we use the commitments and payments tables.
By extracting the original transactions from the budget, commitments, and payments operational systems and loading these monthly snapshot fact tables, we have constructed a powerful value chain of interest to the departmental manager as well as the finance department. Amazingly, we should be able to create this value chain in every imaginable organization.
To find out more about building and using the budgeting chain, read Mike Venerable and Chris Adamson’s book, Data Warehouse Design Solutions (Wiley, 1998). I have based much of this article on chapter 8, “Budgets and Spending.” Venerable and Adamson are serious students of the dimensional design approach, and in this book, they have made a real contribution to the practice of data warehouse design with their approach to what I have called “The Budgeting Chain.”
Ralph Kimball, Ph.D., co-inventor of the Xerox Star workstation and founder of Red Brick Systems, works as an independent consultant designing large data warehouses. He is the author of The Data Warehouse Toolkit (Wiley, 1996) and the newly published The Data Warehouse Lifecycle Toolkit (Wiley, 1998). You can reach him through his Web page at rkimball.com
