Question: 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
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
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts