Modeling hierarchies

The lowest level of the customer’s distribution hierarchy is the ship-tocustomer location. These are grouped under sold-to customers, which represent the customer’s purchasing representatives. These are further grouped into planning customers, which is the lowest level at which sales plans are estimated. Figure 7.9 shows the full hierarchy. Capacity planning is handled by another system, which allocates to plants and distribution centers the sales plan based upon previous sales history. The planners wish to use the data warehouse to support various levels of analysis. They wish to monitor the plan against actual sales in order to adjust both the sales and capacity plans to reflect recent experience. So that the comparisons are meaningful, the reports must reflect the hierarchies that were in effect at the time the plan was published. Both the product and customer are simple hierarchies. Each child has only one parent. Analysis There are a number of aspects to consider concerning how the hierarchies are to be used in this application. First, and most important, there is a need to compare values that are provided at different levels in the hierarchies: a sale, which is collected at the transaction level and is specific to a SKU, sold-to customer, and ship-to address; and the sales plan which is specified at a higher level in both the product and customer hierarchies. Second, the data warehouse will most likely be called on to provide sales history to the capacity-planning system. The feed would be required to tie the detailed history to the higher-level