The writer is very fast, professional and responded to the review request fast also. Thank you.
Presentation/PPT
27/08/2022
Fall 2023 Dr. Alam, CS 538 Data Mart Project
Title: Design and Implement a Data Mart
Part 1: Create a Data Model for a Data Mart using Dimensional Modeling Principles
Prerequisite:
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.
Project Context:
You are stepping into the shoes of a Junior BI developer involved in a data mart 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:
1. Products
2. Customers
3. Dates (Seasonality)
4. Orders
5. Sales Territory
Specific Dimension Requirements:
1.
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.
2.
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.
3.
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.
4.
Order Dimension:
· Sales analysis based on Order ID, Order Detail ID, and Customer ID.
5.
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.
Assignment Task:
Given the requirements and understanding from chapters 9 and 10 on Star Schema, your task is to:
1. Design an ERD diagram for a Star Schema that will integrate the central fact table with the required dimension tables.
2. Refer to Figures 9.10 and 9.18 (for the date dimension) in the textbook as guidance.
Submit your ERD diagram by 10/02/2023.
Good luck, and ensure your design captures the depth and granularity necessary for effective sales data analysis.
Title: Design and Implement a Data Mart
Part 2: Create a Data Model for a Data Mart using Dimensional Modeling Principles Top of Form
In this part of the project, you will be implementing the data mart from your data mart ERD. You will be performing Extraction, Transformation, Loading (ETL) tasks to transfer the required data into the fact table and dimensional tables. You will need to create SSIS packages to perform all the ETL tasks necessary. You have all the source data available in the transaction database AdventureWorks.
Instructions:
1.
Rename the AdventureWorks database inside your SQL Server to AdventureWorks2016CTP3 before starting to work on this project
2. Create a new database called AdventureWorksDM inside your SQL Server. This database will host the fact and dimensional tables
3. Create the packages in SSIS that will create the dimensional and fact tables
4. Here is the list of the tables that serve as the dimensions:
a. DimProduct
b. DimCustomer
c. DimDate
d. DimOrder
e. DimSalesTerritory
5. Here is the list of the fact table you will create:
a. FactSales
Important Notes:
· Create a surrogate key for the DimOrderDate table. The surrogate key value will be the same as the date value except you will not have any slashes. For example, if the date is 11/18/2017 the surrogate key value for that date will be 11182017.
· The DimOrderDate table should have dates from 1980 until 2050. The month and the year column will contain the month value and the year value of the date.
· IsHoliday and HolidayName could be one single date (such as 4th of July) or a range of dates (such as for Christmas, ranging between December 1st until December 25th). You decide which are the holiday dates based on National Holidays within the USA.
2
The writer is very fast, professional and responded to the review request fast also. Thank you.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more