Data Warehousing and Multi-Dimensional Data Modeling

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

You are not authorized to view Teaching Notes. Please contact your librarian for instructor access or sign in to your existing instructor profile.

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

Figure
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

Figure

Exhibit 5: OLAP Operations

Figure

Exhibit 6: Data Warehouse Architecture Alternatives

Figure

Figure

Figure

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

locked icon

Sign in to access this content

Get a 30 day FREE TRIAL

  • Watch videos from a variety of sources bringing classroom topics to life
  • Read modern, diverse business cases
  • Explore hundreds of books and reference titles