You have been hired by the Herbal Tea Company to assist them in developing a data warehouse.
Question:
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:
- 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
- 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
- Draw a CUBE with an ONLY top side filled with data from part B
Auditing and Assurance Services A Systematic Approach
ISBN: 978-1259162343
9th edition
Authors: William Messier, Steven Glover, Douglas Prawitt