Topic: Data Warehouse

You have been hired by Herbal Tea Company to assist them in developing a data warehouse. They want different view of data and feel data warehouse is the way to go. They are specifically interested in the total sales of herbal tea type by quarter in different geographical area over time.

Herbal tea company contains following data:

Month of sale

Product type

State

Sales in $

You have been hired by Herbal Tea Company to assist them in developing a data warehouse. They want different view of data and feel data warehouse is the way to go. They are specifically interested in the total sales of herbal tea type by quarter in different geographical area over time.

Herbal tea company contains following data:

Month of sale

Product type

State

Sales in $

See sample data below:

Month Product State Sales $

January Shining Seas California $880

January Purple Mountains. California $336

January Purple Mountains Oregon $2

January Huckleberry Heat Oregon $510

January Blackbear Berry California $158

January Raspberry Rocket California $23

January Blackbear Berry Oregon $886

January Amber Waves Oregon $218

February Amber Waves Washington $638

February Huckleberry Heat Washington $743

March Purple Mountains California $489

March Raspberry Rocket California $823

April Blackbear Berry California $995

April Shining Seas California $683

April Amber Waves Oregon $150

June Huckleberry Heat Oregon $502

June Purple Mountains Oregon $11

In addition, following information is available:

TEA (Tea Type)

• Amber waves
• Blackbear Berry
• Huckleberry Heat
• Purple Mountain
• Raspberry Rocket
• Shining Seas

They are currently looking for three western regions only:

• California
• Oregon
• Washington

Assume all transactions are completed on first of the month and only six months data is available.

Help design a data warehouse that will keep track of total monthly sales for each product for each state in each region.

Whats required:

a. Drawing the Multi dimensional model (STAR model) using erdplus.com or fatfreestar or WORD

• Make sure to show attribute of each table in your multi dimensional model
• clearly identify PK, FK of each dimension and fact table
• nature of relationship

b. Show contents (ONLY 10 rows ) of each table from data in the original herbal table

Hint: not all tables have 10 rows.

• identify dimensions of each table

• Describe the meaning of a row of fact table

c. Draw a CUBE with ONLY top side filled with data from part B