Managing Your Parents

Be mindful of reporting needs when designing parent and child fact tables

By Ralph Kimball

Continued from Page 1

Now your facts for this child fact table include:

• Number of units of product (additive fact)
• Gross extended product price (additive fact)
• Net extended product price (additive fact)
• Allocated invoice-level promotional discounts (additive fact)
• Allocated freight charges (additive fact)
• Allocated tax (additive fact).

You don't include the unit prices or discounts as physical facts because you can always divide the extended amounts by the number of units in your reporting application to get these nonadditive quantities.

You can instantly recover the exact invoice-level amounts by adding up all the line items under a specific invoice number. You don't need the separate invoice parent fact table because now it is only a simple aggregation of the more granular line-item child fact table. You have in no way compromised the invoice totals by performing the allocations down to the line item.

And, best of all, you can now smoothly roll up your business to the highest levels of geography, time, and products, including the allocated amounts, to get a complete picture of your revenue.

Conflicting Allocation Theories

Sometimes an organization will agree that allocating invoice-level costs is necessary but still can't agree on which allocation method to use. A few years ago I designed a data warehouse for a large fulfillment house that shipped all sorts of household items. A single shipment could contain an inexpensive pillow and a heavy, expensive small object like a silver candleholder. The disagreement was whether to allocate the shipment cost by volume, weight, or value.

Allocating by volume assigned most of the shipment cost to the pillow, but allocating by either weight or value assigned most of the cost to the candleholder. This became a political argument, of course, because the product divisions looked more profitable if they could avoid responsibility for the shipment charges!

Since, as a data warehouse architect, I couldn't resolve this political conflict, I proposed that we include three different shipment costs at the line-item level:

• Volume-allocated shipment cost
• Weight-allocated shipment cost
• Value-allocated shipment cost.

This solution lets anyone roll up the business using any one of the three allocation methods. When there is no constraint or grouping by product line, the results are the same. But any analysis by product reflects the allocation method chosen.

Tough Allocation Environments

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address:

Product shipment invoices are good candidates for this allocation scheme. Many of the costs at the invoice level and many other business costs are "activity based." Although you have to make some compromises and estimates when deciding the allocations, everyone usually agrees that the allocations should be done. In this kind of business, activity based costing (ABC) is a methodology often used as a foundation for exactly the kind of analysis this management of parents makes possible.

But in other businesses, the allocation process is excruciating and maybe impossible. Typically, a business with large infrastructure costs that are not directly related to the products or services sold has trouble agreeing on allocations and ABC. For example, a large telco - with billions of dollars of infrastructure costs in its equipment, employees, and real estate - has great difficulty allocating these costs down to individual phone calls. For the same reason, a large bank has trouble allocating its infrastructure costs down to individual checking accounts. If you are told to allocate costs in one of these environments in order to calculate "product profitability," I suggest you ask the finance department to actually perform the allocations, which you will be glad to store in your data warehouse. Stay off the hot seat!

But for most businesses, the technique of descending to the line-item level and building a single granular fact table is at the heart of data warehouse modeling. It is our way of making good on the promise that we can "slice and dice the enterprise data every which way."

Ralph Kimball co-invented the Star Workstation at Xerox and founded Red Brick Systems. He has three best-selling data warehousing books in print, including The Data Webhouse Toolkit (Wiley, 2000). You can reach him through his Web site, www.rkimball.com.