Create a data mart solution in SQL for the dimension model

Modeling Enterprise Data Warehouse Spring 2021

The due date for the assignment 4 is 3/10/2021 (midnight). It must be submitted through the “Assignment 4 – Modeling Enterprise Data Warehouse” in Assignment Submission Folder provided in the D2L. It is important that you specify your name in the SQL file. The attachment name should follow the following convention: BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.SQL

Don't use plagiarized sources. Get Your Custom Essay on
Create a data mart solution in SQL for the dimension model
Just from $13/Page
Order Essay

Overview:

In assignment 3, we have learned 4-step dimensional design to create the first 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. The dimension and fact tables in assignment 3 are quite simple because we care more about the high abstract level of our first draft model. In assignment 4, we are continuing to apply the 4-step dimensional design to further refining our fact tables and dimension tables.

Business scenario:

Please install 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 the sale orders’ performance. Specifically, he wants to know the total order quantity for each products, total order price, and total tax order percentage paid by customer (defined by total tax amount divided by total order price). Provide a 4-steps dimensional model in the SQL comment section (2 points) and create a data mart solution in SQL for the dimension model that you propose (4 points).

Q2:

The CEO of AdventureWorks Inc. wants to build a data mart to keep track of the sale orders’ performance. The figure below shows the current design of the data mart. However, the data mart designer(s) assume that one order can only be paid by one credit card. If one order can be paid by multiple credit cards, what changes should we make to keep track with total order amount and total tax amount for each credit card? Create data mart solution in SQL for the dimension model that you propose (4 points).

SaleOrderFact

FK CustomerKey

FK CreditCardKey

TotalOrderAmount

TotalTaxAmount

CreditCardDim CustomerDim

CreditCardKey CustomerKey
PK PK
CreditCardNumber CustomerID

CreditCardBillingAddress CustomerName

The following criteria will be used to grade the assignment:

•Codes are run with detail comment, accurate, and answers are correct.

***************************NOTE *************************************

Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!

Place Order
Grab A 14% Discount on This Paper
Pages (550 words)
Approximate price: -
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Try it now!

Grab A 14% Discount on This Paper

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.