Before beginning this assignment, ensure that you've thoroughly read and understood Chapters 9 (pages 197 to 235) and 10 (pages 237 to 251) on Dimensional Modeling from the course textbook.
You are stepping into the shoes of a Junior BI developer involved in a data warehouse project. As part of the requirements gathering phase, you have a discussion with Jim Riner, the Sales Manager. Jim identifies a crucial need for deeper sales data analysis that encompasses the following dimensions:
- Products
- Customers
- Dates (Seasonality)
- Orders
- Sales Territory
- Product Dimension:
● Analyze sales based on categories, subcategories, product names, colors, and models.
● This will help in identifying top-selling items in various categories and attributes. - Customer Dimension:
● Explore sales data to determine which customers purchase which items, pinpoint top customers, and analyze sales by the customer's zip, territory, country, and city.
● This information can aid in tailoring promotional offers and understanding buying patterns of valued customers. - Date (Seasonality) Dimension:
● Analyze which products have high sales during specific seasons, days, weeks, or years.
● The granularity of this dimension should include: Date Surrogate Key, Date Value, Month, Year, IsHoliday, and Holiday Name. - Order Dimension:
● Sales analysis based on Order ID, Order Detail ID, and Customer ID. - Sales Territory Dimension:
● The analysis should cover territory name, territory group, country, or region codes.
● The objective is to determine the profitability of specific geographic locations, products sold there, and revenue comparison between regions.