Big Data, Data Warehouses, and Business Intelligence Systems - Database Processing: Fundamentals, Design, and Implementation - page 578

G. Write an SQL query similar to the one shown in the text on page 547 that uses retail
price as the measure.
h. Write the SQL view equivalent of the SQL query you wrote to answer part G.
I. Create the SQL view you wrote to answer part H in your QACS-DW database.
J. Create a Microsoft Excel 2013 workbook named QACS-DW-BI-Exercises.xlsx.
K. Using either the results of your SQL query from part G (copy the results of the query
into a worksheet in the QACS-DW-BI.xlsx workbook and then format this range as
a worksheet table) or your SQL view from part I (create a Microsoft Excel data con-
nection to the view), create an OLAP report similar to the OLAP report shown in
Figure 12-21(d). ( Hint: If you need help with the needed Microsoft Excel actions, search
in the Microsoft Excel help system for more information.)
L. Describe how an RFM analysis could be useful to The Queen Anne Curiosity Shop.
If you have not already implemented the Morgan Importing database shown in Chapter
7 in a DBMS product, create and populate the QACS database now in the DBMS of
your choice (or as assigned by your instructor).
James Morgan wants to analyze shipper performance based on the difference
between a shipment's scheduled departure date and the actual departure date. This
value will be named DepartureDelay, with the values measured in days. The values
of Days can be positive (the shipment departed later than the scheduled departure
date), zero (the shipment departed on the scheduled departure date), or negative (the
shipment departed before the scheduled departure date).
Since Morgan Importing purchasing agents are responsible for contacting the
shippers and arranging the shipments, James also wants an analysis of purchasing
agents' performance based on the same measure.
A. You need about 30 SHIPMENT transactions in the database. Write the needed SQL
statements for any needed additional SHIPMENT transactions, and insert the data
into your database.
B. Design a data warehouse star schema for a dimensional database named MI-DW. The fact
table measure will be DepartureDelay (the difference between ScheduledDepartureDate
and ActualDepartureDate). Dimension tables will be TIMELINE, SHIPMENT, SHIPPER
and PURCHASING_AGENT (PURCHASING_AGENT is a subset of EMPLOYEE con-
taining data on only the employees who are purchasing agents).
C. Create the MI-DW database in a DBMS product.
D. What transformations of data will need to be made before the MI-DW database can be
loaded with data? List all the transformations, showing the original format of the MI
and how it appears in the MI-DW database.
e. Write the complete set of SQL statements necessary to load the transformed data into
the MI-DW database.
F. Populate the MI-DW database, using the appropriate MI data or transformations of
that data.
G. Write an SQL query similar to the one shown in the text on page 547 that uses
DepartureDelay as the measure.