Building Effective Data Marts: Applying 4-Step Dimensional Design for AdventureWorks Database Analysis
Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates
For This or a Similar Paper Click To Order Now
ASSIGNMENT INSTRUCTIONS:
Overview:
In this assignment, we apply the 4-step dimensional design to create the draft of your data warehouse models. Our models are preliminary works in which we continue to gather more requirements from sponsors to begin developing more cohesive final drafts.
Business scenario:
Please install the AdventureWorks database, read the AdventureWorks case study, and analyze the ERD
OLTP schema before starting this assignment.
Q1:
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of product production inventory. He is concerned with how effectively each product vendor provides the company with appropriate products. To measure the effectiveness of each product vendor, he wants to use the total number of work order quantities and the total number of work order scrapped quantities. Provide a 4-step dimensional model in the SQL comment section (1 point) and create a data mart solution in SQL for the dimension model that you propose (2 points).
Q2:
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the product production’s lifecycle. Specifically, he wants to know the total number of products when the company received the work order and bill of materials. Provide a 4-step dimensional model in the SQL comment section (1 point) and create a data mart solution in SQL for the dimension model that you propose (2 points).
Q3
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the store’s monthly sales performance. Specifically, he wants to keep track of monthly tax effectiveness (defined by Tax Amount divided by Sales SubTotal) and monthly sales amount. Provide a 4-step dimensional model in the SQL comment section (1 point) and create data mart solutions in SQL for the dimension model that you propose (3 points).
The following criteria will be used to grade the assignment:
Codes are run with a detailed comments, are accurate, and answers are correct.
HOW TO WORK ON THIS ASSIGNMENT (EXAMPLE ESSAY / DRAFT)
For AdventureWorks Inc.’s data warehouse models, we will employ the 4-step dimensional design process in this project. These models are used to monitor inventories, the lifespan of product manufacture, and store sales activity.
It is important to install AdventureWorks, read the case study, and examine the OLTP schema before beginning this task.
Q1: For the first scenario, AdventureWorks Inc.’s CEO wishes to monitor product production inventory and assess each vendor’s performance. We’ll measure our success using the sums of the work order quantity and the work order quantity that was scrapped. The four-step dimensional model for this situation is as follows:
Determine the business process in Step 1: Product Manufacturing Stock
Step 2: Determine the grain Order of Work Item
Determine the measurements in Step 3:
Vendor Product Date
Type of Work Order
Identify the metrics in Step 4: Work Order Quantity
Work Order Quantity Scrapped
We may utilize a fact table called “FactProductProductionInventory” that contains the measures (Work Order Quantity and Work Order Scrapped Quantity) and foreign keys from the dimension tables (Vendor, Product, Date, and Work Order Type) to develop a data mart solution in SQL for this dimension model.
Q2: In the second example, the CEO wishes to keep track of the lifecycle of the product production by knowing how many items were made at the time a work order and bill of materials were received by the business. The four-step dimensional model for this situation is as follows:
Determine the business process in Step 1: Lifecycle of Product Production
Step 2: Determine the grain Product
Determine the measurements in Step 3:
Work Order Material List
Date
Identify the measures in Step 4:
Number of products overall
We may utilize a fact table called “FactProductProductionLifecycle” that contains the measure (Total Number of Products) and foreign keys from the dimension tables (Work Order, Bill of Materials, and Date) to build a data mart solution in SQL for this dimension model.
Q3: In the third case, the CEO wishes to monitor the store’s monthly sales performance by monitoring the number of sales and monthly tax effectiveness. The four-step dimensional model for this situation is as follows:
Determine the business process in Step 1: Monthly Sales Performance of the Store
Step 2: Determine the grain Transaction Line for the Sales Item
Determine the measurements in Step 3:
Store Date
Determine the metrics in Step 4: Monthly Tax Effectiveness
Amount of monthly sales
We can utilize a fact table called “FactStoreMonthlySalesPerformance” that contains the measures (Monthly Tax Effectiveness and Monthly Sales Amount) and foreign keys from the dimension tables (Store and Date) to develop a data mart solution in SQL for this dimension model.
In conclusion, we were able to produce draft data warehouse models for AdventureWorks Inc. by using the 4-step dimensional design method. These models will be used to track inventories, the lifespan of product production, and store sales activity.
Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates