Data Model

We are extending the data model that we created in Chapter 3 by adding support for budgeting. The first question that we normally ask about a new business area is "What is the grain of the fact data?." You might remember from Chapter 3 that the grain of the Shipments fact data was customer by product, by day, by plant, and by ship method. The grain is defined by the lowest level of each dimension used in the fact table.

Now we'll look at the grain of the budget facts. You will see that although some of the same dimensions are used, the level differs for budgets. Specifically, the Time dimension goes down to the level of day, but budget grain is only at the level of a quarter. You don't need to define a new Time dimension to handle this. You can always support less grain with the same dimension.

The Budget Data

In our budget, we want to predict what we are going to sell, where it will be sold, and over what period of time.

We want to support the planning process of the manufacturing plants by providing estimates for each product, so Product is one component of the grain of the budget. We also want to show the commitments made by each sales territory to support these sales, so Sales Territory is another component of the grain. Finally, in contrast to the Shipments data, which is at a day level, it would be unrealistic to produce a budget that tried to predict for each day of the upcoming year exactly what products we would ship to our customers. The budget numbers need to be at a much higher level, such as quarterly. Our grain then is by product, by territory, and by quarter.

Figure 4-2 shows a sample of a budget spreadsheet that we will use as the source for the budget facts. There will be one spreadsheet for each sales region.

Figure 4-2. Budget spreadsheet

Working with Extended Measures

The budget spreadsheets express everything in terms of number of units. This is convenient in some ways because unit is a nice, additive measure that is very simple to model. However, we also want to use the price and cost to compute expected revenue, cost, and profit. Because the budget numbers are at the Product level, these additional measures can be calculated by looking up the appropriate selling price, cost, and profit amount on the relevant product record and multiplying by the number of units. By having the ETL process look up these numbers and store them on the fact table, we reduce the chances of a transcription error in the spreadsheet.

Instead of physically storing the extra columns in the Budget fact table, you could instead create a view over the fact table that joins to the Product table and calculates the revenue, cost, and profit. A side effect of this approach is that the values on the Product dimension record, such as standard cost, can change over time. If we calculated the revenue, cost, and profit in a view, this information could change through the course of a fiscal year, and we won't be able to go back and see the historical values. Of course, if this is the behavior you are after, using a view is a good solution.

Figure 4-3 shows the data model we will use to support the budgets. We will store the extended revenue and cost in the fact table. This will give us better performance and will retain the revenue and costs current at the time of the budgeting.

Figure 4-3. Budget fact table

Practical Business Intelligence with SQL Server 2005
Practical Business Intelligence with SQL Server 2005
ISBN: 0321356985
EAN: 2147483647
Year: 2007
Pages: 132 © 2008-2017.
If you may any questions please contact us: