Case
Teaching Notes
Supplementary Resources
Abstract
Mr. Vishal Mehta, CIO at Acme Inc, just finished a meeting with Mr. Steve Barley, VP (Retail) and several other key regional and national heads of Acme. The team discussed the plans for the next year and the key decision support infrastructure woes faced by operational units in data access, integration, analysis, usage and interpretation. Vishal presented his ideas on how data warehouse investment could help address many such decision support infrastructure challenges. The management was quite receptive to his ideas and asked him to conduct a feasibility study, build a pilot and prepare a detailed implementation plan.
Vishal has more than 15 years of experience successfully building and managing several IT projects. However, he lacked sufficient expertise on Business Intelligence (BI) or Data Warehouse (DW) related projects. BI/DW projects were more challenging to execute than normal IT projects and require organization wide commitment and support. Vishal, therefore, was contemplating on how to build a data warehouse solution to meet his organization's business requirements
This case was prepared for inclusion in Sage Business Cases primarily as a basis for classroom discussion or self-study, and is not meant to illustrate either effective or ineffective management styles. Nothing herein shall be deemed to be an endorsement of any kind. This case is for scholarly, educational, or personal use only within your university, and cannot be forwarded outside the university or used for other commercial purposes.
2024 Sage Publications, Inc. All Rights Reserved
Resources
Exhibit 1: Sales Invoice
INVOICE
Acme Inc
345 Victoria Street
Kolkata 702 207
Bill To Address: Mr. Vaghela, Shankar Mint street, Kolkata | Ship To Address: Mr. Vaghela, Shankar Mint street, Kolkata | ||||
Invoice No | I10005 | Order No | O123456 | ||
Store No | S303 | Order Date | 06-Sep-14 | ||
Emp Id | E777 | Promo code | None | ||
Sno | Product Code |
| Qty | Unit Price (Rs) | Amount (Rs) |
1 | TV1001 | 24″ LCD TV | 1 | 35000 | 35000 |
2 | W1002 | Extended warranty | 1 | 3000 | 3000 |
3 | |||||
4 | |||||
5 | |||||
Sales Amount | 38000 | ||||
Sales Tax | 4750 | ||||
Freight | 300 | ||||
Discount | Nil | ||||
Total Amount | 43050 |
Exhibit 2: Entity-Relationship Diagram
Sample Table Description for the E-R Diagram | |
---|---|
Table Name | Representative Attributes |
CUSTOMER | Customer id, name, address, city, state, country, pincode |
ORDER | Order id, customer id, store id, sales person id, order date, sales tax, discount, freight, amount |
STORE | Store id, name, description, location |
SALES PERSON | Sales person id, name, designation, age, dob, address |
INVOICE | Invoice id, customer id, customer name, address, order id, amount |
LINE ITEM | Line item id, order id, name, description, price |
ITEM | Item id, product name, brand, category, department |
PROMOTION | Promotion id, promo name, description, start date, end date |
SUPPLIER | Supplier id, supplier name, description, location |
SHIPMENT | Shipment id, ship date, delivery date |
CATEGORY | Category id, category name, description |
CUSTOMER PROFILE | Customer id, demographic profile, psychographic profile |
Exhibit 3: Data Warehouse Business Matrix
Business Process | Date | Product | Store | Customer | Promotion | Geography | Warehouse | Transporter | Supplier |
Retail Product Sales | X | X | X | X | X | X | |||
Retail Inventory | X | X | X | X | |||||
Retail Product Receipts | X | X | X | X | X | ||||
Retail Promotions | X | X | X | X | X | ||||
Frequent Shopper | X | X | X | X | |||||
Retail Product Returns | X | X | X | X | X | ||||
Warehouse Orders | X | X | X | X | X | X | |||
Warehouse Inventory | X | X | X | X | |||||
Warehouse Receipts | X | X | X | X | X | X | |||
Warehouse Sales Forecast | X | X | X | ||||||
Purchase Order | X | X | X | X |
Exhibit 4: High Level Data Warehouse Architecture
Exhibit 5: OLAP Operations
Exhibit 6: Data Warehouse Architecture Alternatives
This case was prepared for inclusion in Sage Business Cases primarily as a basis for classroom discussion or self-study, and is not meant to illustrate either effective or ineffective management styles. Nothing herein shall be deemed to be an endorsement of any kind. This case is for scholarly, educational, or personal use only within your university, and cannot be forwarded outside the university or used for other commercial purposes.
2024 Sage Publications, Inc. All Rights Reserved