Design an erd diagram for a star schema

Assignment Help Computer Engineering

Reference no: EM133529026

Homework: Business Intelligence and Data Mining- Data Warehouse ERD Creation

Prerequisite:

Before beginning this homework, 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.

Context:

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

Specific Dimension Requirements:

I. Product Dimension:

1. Analyze sales based on categories, subcategories, product names, colors, and models.

2. This will help in identifying top-selling items in various categories and attributes.

II. Customer Dimension:

1. 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.

2. This information can aid in tailoring promotional offers and understanding buying patterns of valued customers.

III. Date (Seasonality) Dimension:

1. Analyze which products have high sales during specific seasons, days, weeks, or years.

2. The granularity of this dimension should include: Date Surrogate Key, Date Value, Month, Year, IsHoliday, and Holiday Name.

IV. Order Dimension:

1. Sales analysis based on Order ID, Order Detail ID, and Customer ID.

V. Sales Territory Dimension:

1. The analysis should cover territory name, territory group, country, or region codes.

2. The objective is to determine the profitability of specific geographic locations, products sold there, and revenue comparison between regions.

Task:

Given the requirements and understanding from chapters 9 and 10 on Star Schema, your task is to:

A. Design an ERD diagram for a Star Schema that will integrate the central fact table with the required dimension tables.

B. Refer to Figures 9.10 and 9.18 (for the date dimension) in the textbook as guidance.

Reference no: EM133529026

Questions Cloud

With electronic addiction taking up so much of person life : With electronic addiction taking up so much of a person's life, they will need to determine ways to fill that time. Describe ways that this can be accomplished.

Explain what incidental learning is and where clients can be : Explain what incidental learning is and where clients can be encouraged to use these learning opportunities

Would there be benefits to no access to social media : Would there be benefits to no access to social media? Would there be harmful affects or consequences?

How employment law applies to volunteers versus employees : Compare and contrast how employment law applies to volunteers versus employees. Identify the potential risks and liabilities associated with working

Design an erd diagram for a star schema : CS 538- Design an ERD diagram for a Star Schema that will integrate the central fact table with the required dimension tables.

Identify the child protection responsibilities of an rn : Identify the child protection responsibilities of an RN in a clinical setting and discuss the collaborative processes with a multidisciplinary team in case

How does hilary and bernie use space to make meaning : How does Hilary and Bernie use space to make meaning? What does the space between them suggest?

Discuss the four essential elements of a clinical pathway : Discuss the four essential elements of a clinical pathway. How do clinical pathways differ from practice guidelines?

Analyze one strength and one limitation of feminist theory : Analyze one strength and one limitation of feminist theory in working with an adolescent female who is estranged from her family due to physical and emotional a

User Account

All Pages