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

Herbal tea company contains the following data:

Month of sale

Product type

State

Sales in $

See the 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, the 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 the first of the month and only six months' data is available.

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

To be submitted:

1. Draw the Multidimensional model (STAR model) using WORD
   • Make sure to show the attribute of each table in your multidimensional model
   • clearly identify PK, FK of each dimension and fact table
   • nature of the relationship

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

Hint: not all tables have 10 rows.

• identify the dimensions of each table

• Describe the meaning of a row of the fact table

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