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 quantity 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 detailed comments, are accurate, and answers are correct.
HOW TO WORK ON THIS ASSIGNMENT (EXAMPLE ESSAY / DRAFT)
When creating a data warehouse model, the 4-step dimensional design application is crucial. The primary goal of this project is to provide data mart solutions for AdventureWorks Inc., a business that manufactures and distributes bicycles and related goods. This essay will go over the four-step dimensional design process and how AdventureWorks Inc. can use it to construct data mart solutions.
The four steps of the dimensional design process are: choosing the business process, choosing the dimensions, choosing the facts, and choosing the granularity. We choose the business process we wish to examine in the first phase. We are entrusted with developing data mart solutions for inventory, production lifecycle, and sales performance for AdventureWorks Inc.
We determine the dimensions that are pertinent to the business process in the second stage. We will use the dimensions of Product Vendor, Work Order Quantity, and Work Order Scrapped Quantity for the inventory data mart. We will use the dimensions Product, Work Order, and Bill of Materials for the manufacturing lifecycle data mart. We will use the dimensions Store, Time, and Sales for the sales performance data mart.
Selecting the facts that apply to the business process is the third step. The CEO of AdventureWorks Inc. wishes to assess each product vendor’s effectiveness for the inventory data mart; for this, we will utilize the total quantities of work orders and the total quantities of work orders that were scrapped as facts. The CEO wants to know how many goods were produced at the time the business received work orders and bills of materials for the product lifecycle data mart. The total number of goods is the information we will use as a result. The CEO also wants to monitor monthly tax effectiveness and sales volume for the sales performance data mart. Therefore, Tax Amount and Sales SubTotal will be the relevant data.
Finding the granularity is the fourth and last stage. The granularity for the inventory data mart will be at the level of specific products and work orders. Individual work orders and bills of materials will be the degree of granularity for the product lifecycle data mart. The granularity for the sales performance data mart will finally be at the level of specific stores and time frames.
The 4-step dimensional design approach must be used on its whole to produce successful data mart solutions for AdventureWorks Inc. We can provide data mart solutions that offer valuable insights and aid the CEO in making defensible decisions by choosing the pertinent business process, dimensions, facts, and granularity.
hen creating a data warehouse model, the 4-step dimensional design application is crucial. The primary goal of this project is to provide data mart solutions for AdventureWorks Inc., a business that manufactures and distributes bicycles and related goods. This essay will go over the four-step dimensional design process and how AdventureWorks Inc. can use it to construct data mart solutions.
The four steps of the dimensional design process are: choosing the business process, choosing the dimensions, choosing the facts, and choosing the granularity. We choose the business process we wish to examine in the first phase. We are entrusted with developing data mart solutions for inventory, production lifecycle, and sales performance for AdventureWorks Inc.
We determine the dimensions that are pertinent to the business process in the second stage. We will use the dimensions of Product Vendor, Work Order Quantity, and Work Order Scrapped Quantity for the inventory data mart. We will use the dimensions Product, Work Order, and Bill of Materials for the manufacturing lifecycle data mart. We will use the dimensions Store, Time, and Sales for the sales performance data mart.
Selecting the facts that apply to the business process is the third step. The CEO of AdventureWorks Inc. wishes to assess each product vendor’s effectiveness for the inventory data mart; for this, we will utilize the total quantities of work orders and the total quantities of work orders that were scrapped as facts. The CEO wants to know how many goods were produced at the time the business received work orders and bills of materials for the product lifecycle data mart. The total number of goods is the information we will use as a result. The CEO also wants to monitor monthly tax effectiveness and sales volume for the sales performance data mart. Therefore, Tax Amount and Sales SubTotal will be the relevant data.
Finding the granularity is the fourth and last stage. The granularity for the inventory data mart will be at the level of specific products and work orders. Individual work orders and bills of materials will be the degree of granularity for the product lifecycle data mart. The granularity for the sales performance data mart will finally be at the level of specific stores and time frames.
The 4-step dimensional design approach must be used on its whole to produce successful data mart solutions for AdventureWorks Inc. We can provide data mart solutions that offer valuable insights and aid the CEO in making defensible decisions by choosing the pertinent business process, dimensions, facts, and granularity.
Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount