02 Oct You are a Junior BI developer who is working on a data warehouse implementation project. You are visiting Jim Riner, the Sales Manager for the company who is also a major stakeholder of
You are a Junior BI developer who is working on a data warehouse implementation project. You are visiting Jim Riner, the Sales Manager for the company who is also a major stakeholder of this project to elicit requirements for the project. Jim informs you one of the goals the BI project will help him to achieve is deeper analysis of the sales data. His analysis of the sales data will include analyzing the sales based on the following dimensions: products, customers, dates, orders, and sales territory.
Note: Find the complete question in the attached document
ERD Assignment 2
Caution: please do not start on this assignment unless you have read Chapters 9 (pages 197 to 235) and 10 (pages 237 to 251) on Dimensional Modeling from the textbook. That’s i mportant!
Please read this entire document carefully before starting to work on the assignment.
You are a Junior BI developer who is working on a data warehouse implementation project. You are visiting Jim Riner, the Sales Manager for the company who is also a major stakeholder of this project to elicit requirements for the project. Jim informs you one of the goals the BI project will help him to achieve is deeper analysis of the sales data. His analysis of the sales data will include analyzing the sales based on the following dimensions: products, customers, dates, orders, and sales territory.
Jim informs you sales data analysis from the Product dimension (or context) will be based on the contexts like product categories, subcategories, product name, and product attributes like colors, models etc. By doing the sales analysis based on the Product dimension Jim can find out answers to questions like which items are selling more based on their categories, subcategories, colors etc.
Sales data analysis from the Customers dimension (or context) will help Jim to find out which customers are buying what, who are the top customers, analyze sales by the customers’ zip, territory, country, and city. Knowing these information will help Jim to reach out to the customers with promotional offers. He can also identify his top 10 selling products of his top 10 most valued customers.
Sales analysis from the Seasonality (or date) dimension would allow Jim to find out which products are selling more during what seasons and which products are not selling well during certain days, weeks, months, years. The Seasonality dimension is also known as Date dimension (DimDate or Dim_Date). The granularity (fields) of this dimension should be at least Date Surrogate Key, Date value, Month, Year, IsHoliday (Yes/No value which would indicate is the date a holiday date like July 4th etc.), Holiday name (like Christmas day, Independence day etc.). Some of the holidays like Christmas has a range of dates like from December 15th until December 26th and the IsHoliday value should be set to Yes for these dates and the Holiday Name should be set to Christmas.
Sales data analysis from the Order dimension would be analyzing from Oder ID, Order Detail ID, Customer ID perspectives.
As you have read in chapters 9 and 10, for these types of data analyses, dimensional data modeling is more appropriate. One type of dimensional modeling is Star Schema.
ERD Assignment 2:
In this assignment you will create an ERD diagram for a Star Schema that will accommodate the fact table and the dimensional tables for the proposed data warehouse. You can use the Figures 9.10 and 9.18 (for the date dimension) from the book as a model to do this assignment.
