Datawarehousing

Final Exam

Fall 2020

The due date for the Final Exam is 12/6/2020 11:59 pm. It must be submitted through the “Final Exam”

in Assignment Submission Folder in D2L. It is opened-book, opened-note, opened-Internet, but not

opened-friends or neighbor. It means that you have to do this exam by your own. You can use Internet

to check your syntax, but cannot ask anyone to do the exam for you. If the final exam is not done by

you, you will get 0 for the final exam. Partial credit might be given even if your answers are incorrect.

The attachment name should follow the following convention for Part I:

BUSA532_FINALEXAM FIRSTNAME_LASTNAME PART1.DTSX

The attachment name should follow the following convention for Part II:

BUSA532_ FINALEXAM_FIRSTNAME_LASTNAME PART2.ZIP (Make sure you zip the entire SSAS folder)

The attachment name should follow the following convention for Part III:

BUSA532_ FINALEXAM_FIRSTNAME_LASTNAME PART3.TWBX

PART I:

Use the OLTP logical schema below to build data warehouse consisted of two data marts. You will need

to import the final exam data code for the OLTP logical schema and develop ETL process in SSIS (70

points). Your tables should meet these requirements:

- DimDate table has DateKey values ranging from 19960704 to 19980603 (YYYYMMDD integer

data type). If you use Flat File or Excel Data Source to import datekey data, include the data

source file in the submission also (5 points).

- DimDate table should have EnglishZodiacSignMonthName (varchar data type), CalendarYear

(INT data type), Quarter (INT data type), LeapYear (BOOLEAN data type), and

IsMortherDayHoliday (BOOLEAN data type) attributes (15 points).

- DimProduct should contain product name, supplier name, category name, unit price, and

discontinued (10 points).

- AccumulatingOrderFact has 3 foreign keys – OrderDateKey, RequiredDateKey and ShipDateKey,

and one degenerate dimension attribute – OrderID. Note that the your 3 datekeys should allow

to have ‘Null’ value if the order does not have either order date, required date, or ship date (10

points).

- PeriodicSnapShotMonthlyOrder has 2 foreign keys – MonthKey and ProductKey (10 points).

- Correct ETL process to create 3 fact measures for AccumulatingOrderFact (10 points)

- Correct ETL process to create 3 fact measures for PeriodicSnapShotMonthlyOrder (10 points)

The following table and graph show you the data description of fact measures and ERD of the

data warehouse:

Fact Measure Data Description TotalOrderOnOrderDate Total number of orders that data warehouse received on the

order date. TotalPriceOrderOnReceivedDate Total amount of price order that data warehouse must fulfill

on the received date. Note that one order could have multiple products.

TotalProductShippedOnShipDate Total number of products that data warehouse shipped on the ship date. We assume that products of one order are always shipped together, and one order could have multiple products.

TotalMonthlyProductOrdered Total number of products ordered of one month TotalMonthlyAmountOfProductDiscount Total amount of product discount of one month MonthlyMostPopularProduct Display name of the most popular product(s) sold in that

month

DimDate

DimProduct

PeriodicSnapShotMonthlyOrder

AccumulatingOrderFact

DateKey intPK

EnglishZodiacSignMonthName varchar(50)

ProductKey intPK

ProductName varchar(25)

UnitPrice money

CalendarYear int

Quarter int

SupplierName varchar(25)

CategoryName varchar(25)

MonthKey intFK

ProductKey intFK

TotalMonthlyProductOrdered int

TotalMonthlyAmountOfProductDiscount money

MonthlyMostPopularProduct varchar(100)

IsMortherDayHoliday boolean

LeapYear boolean

OrderDateKey intFK

TotalOrdersOnOrderDate int

ShipDateKey intFK

Discontinued bit

TotalProductShippedOnShipDate int

TotalPriceofOrdersOnReceivedDate money

OrderID intDD

RequiredDateKey intFK

FactKey intPK

PART II:

Use Adventure Work data warehouse for Internet Sales to answer the following business analytic

questions. The figure below shows the data warehouse design. (You can find instructions to install the

data warehouse in Topic 2 if you haven’t installed yet). You will need to submit the zip file of your

entire SSAS folder for Part II questions:

1. Using SSAS, show what product has low sale amount (<3,000), medium sale amount (<=5,000),

and high sale amount (>5,000) (3 points).

2. Using SSAS, what product has the highest ‘Total Product Price’? Total product price is the sum of

sales amount, tax amount, and freight amount (3 points).

3. Using SSAS, show total number of orders by city and state. Note you must create hierarchy for

city and state to get credit (3 points).

PART III:

Use Part I - OLTP data that you downloaded from D2L to answer the following business analytic

questions by using Tableau, and submit your answers in TWBX (Tableau Packaged Workbook). For each

question, you will need to submit data visualization(s) and one short sentence to answer the question.

The figure above shows the data warehouse design. Note that you will lose half points if your short

sentence is not included for each question.

1. Create one appropriate graph to show state(s) where most employees live (3 points).

2. Create one appropriate graph to show month(s) which has/have the most orders (3 points).

3. Create one appropriate graph to compare product inventory value. Product inventory value is

‘High’ when UnitsInStock * UnitPrice is greater than 300. Product inventory value is ‘Mid’ when

UnitsInStock * UnitPrice is greater than 100 but lower than 300. Product inventory value is ‘Low’

when UnitsInStock * UnitPrice is lower than 101 (3 points).

4. Create only one bar chart to show total of product ordered and total of product quantity

ordered by each month (3 points).

5. Create one appropriate graph to show managers who manage the most employee (3 points).

6. Create one graph to show the total numbers of employees and suppliers who live in the same

region (3 points).

7. Create one dashboard for Q1 to Q6 (3 points).

The following criteria will be used to grade the final exam:

• Codes are detailed, accurate, SSIS and SSAS solution package are correct, and Tableau graphs

are appropriate.

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

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