Data Warehouse Design Solutions

6

INVENTORY AND CAPACITY

Inventory plays a key role in many business decisions, and its management has direct impact on the bottom line. The amount of product on hand is a factor in purchasing, manufacturing, and pricing. The product levels must be carefully managed by manipulating quantity and pricing. Ineffective inventory control can lead to runaway costs or dissatisfied customers, while effective inventory management maximizes the profitability of the business.

People who look at inventory ask a wide variety of questions. The most basic inventory issue is “How much is on hand?” Other questions require more complex analysis, such as “How much should it cost?” and “How much should we have?” These questions come from all parts of the business, highlighting the critical role inventory plays. Clearly, the inventory data warehouse can be a valuable tool. Because the business questions can be profound, designing the inventory data warehouse is a challenge.

Closely linked to inventory is the concept of capacity. Common in service industries, businesses with a fixed capacity to service customers must pay close attention to its utilization. The rate at which capacity is used is referred to as utilization. Prices must be carefully managed to achieve desired utilization levels. Questions include “What is our capacity?” and “How did the price change impact use?”

In this chapter, we will prepare schema designs for two businesses. A supplier of medical products strives to maintain sufficient ...