For each item below, write a Management Summary that describe the key concepts of the case study. Remember, management is not interested in reading the case study themselves, they want a concise, well-written, yet thorough review of the concepts. The following outline should be used as a guideline:
Data Warehouse Concepts – each case builds upon prior cases by introducing new Data Warehouse concepts.
Summary – how would you apply this information in a business scenario?
- Retail Sales
- Inventory Case
- Procurement Case
- Order Management Case
- Accounting Case
- Customer Relationship Management Case
- Human Resource Management Case
Retail Case Study
The case study is based on building a data warehouse for a large grocery chain that has 100 grocery stores spread across five states. Each store has several departments, including grocery, frozen foods, dairy, meat, produce, bakery, floral, and health/beauty aids. Each store has approximately 60,000 individual products, called stock keeping units (SKUs) on its shelves.
The business process identified to model is the point-of-sale (POS) retail sales transactions which will enable business users to analyze which products are selling and how sales vary by store, day, or promotional condition.
In the case study, the most granular data is an individual product on a POS transaction. The case study includes these dimensions: date, product, store, promotion, cashier, and method of payment. The product dimension describes every SKU in the grocery store. The store dimension describes every store in the grocery chain and is the primary geographic dimension. The promotion dimension describes the promotion conditions under which a product is sold. This is often called a causal dimension because it describes factors that may influence product sales. The promotion dimension also includes a row with a unique key such as 0 to identify no promotion because some products will be sold at full price. The cashier dimension describes the cashiers. The method of payment dimension describes available payment options.
The facts collected by the POS system include the sales quantity, per unit regular, discount, and paid prices, and extended discount and sales dollar amounts.
Data Warehouse Concepts
The case study discusses the four-step dimensional design process that includes these steps:
Step 1: Select the Business Process
o A business process is a low-level activity performed by an organization (e.g., taking order, receiving payment)
o A series of process results in a series of fact tables
o Need to decompose business initiatives into underlying processes
Step 2: Declare the Grain
o This means specifying exactly what an individual fact table row represents.
o The grain declarations are expressed in business terms.
o A careful grain statement determines the primary dimensionality of the fact table (i.e., primary key) and then you can add more dimensions.
o If learn grain statement is wrong in steps 3 and 4, should return to step 2 to restate the grain.
o Should develop dimensional models representing the most detailed, atomic information captured by a business process.
Step 3: Identify the Dimensions
o Describe the data resulting from the business process measurement events.
o Represents the “who, what, where, when, why, and how” associated with the
o Date dimension – in nearly every dimensional model and captures a time series of
performance metrics. May include flags and indicators such as holiday indicator.
Usually separated from time-of-day to avoid a large number of rows.
o Dimension table normalization is referred to as “snowflaking” which increases
complexity; recommend avoiding this for ease of use and performance. This
penalizes cross-attribute browsing and prohibits the use of bitmapped indexes.
o Some situations warrant using an outrigger dimension that attaches to a
dimension within the fact table’s immediate halo but should only use these on an
o A large number of dimensions (25+) may indicate that several dimensions are not
completely independent and should be combined into a single dimension.
o Advantages of Using Surrogate Keys (instead of natural keys)
Buffers the data warehouse from operational changes
Integrates multiple source scenarios
Improves performance – by using as small an integer as possible
Handles null or unknown conditions
Supports dimension attribute change tracking
Step 4: Identify the Facts
o Reflects what the business process measures
o Typical facts are numeric additive figures (e.g., quantity ordered)
o When making decisions during the 4-step process, consider business users’ requirements and the realities of source data.
o Derived facts – generally recommend storing a calculated derived fact in the database (e.g., gross profit=sales dollar amount minus cost).
o Non-additive facts – percentages and ratios (e.g., gross margin) are non-additive. Would store numerator and denominator in fact table in order to derive the nonadditive fact.
o Transaction fact tables – record a transactional event and are often sparsely populated and highly dimensional.
o Must avoid null keys in the fact table.
As mentioned above, a POS retails sales data warehouse would enable business users to analyze which products are selling, and then the company could make changes based on this analysis to improve retail sales and company profits.