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

F. What possible transformations of data are necessary to load the SALES_FOR_
RFM table? List some possible transformations, showing the original format of the
HSD data and how they appear in the HSD-DW database.

G. Write an SQL query similar to the one shown on page 547 that uses the total dollar
amount of each day's product sales as the measure (instead of the number of prod-
ucts sold each day).

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 HSD-DW database.

J. Create a Microsoft Excel 2013 workbook named HSD-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 HSD-DW-BI-Exercises.xlsx workbook and then for-
mat this range as a worksheet table) or your SQL view from part I (create an Excel
data connection to the view), create an OLAP report similar to the OLAP report
shown in Figure 12-32. ( Hint: If you need help with the needed Microsoft Excel ac-
tions, search in the Microsoft Excel help system for more information.)

L. Heather Sweeney is interested in the effects of payment type on sales in dollars.

1. Modify the design of the HSD-DW dimensional database to include a
PAYMENT_TYPE dimension table.

2. Modify the HSD-DW database to include the PAYMENT_TYPE dimension
table.

3. What data will be used to load the PAYMENT_TYPE dimension table? What
data will be used to load foreign key data into the PRODUCT_SALES fact table?
Write the complete set of SQL statements necessary to load these data.

4. Populate the PAYMENT_TYPE and PRODUCT_SALES tables, using the SQL
statements you wrote to answer part 3.

5. Create the SQL queries or SQL views needed to incorporate the PaymentType
attribute.

6. Create a Microsoft Excel 2013 OLAP report to show the effect of payment type
on product sales in dollars.

Case Questions

Marcia's Dry Cleaning Case Questions
If you have not already done so, create and populate the Marcia's Dry Cleaning (MDC) data-
base for the DBMS you are using as described in:

Chapter 10A for Microsoft SQL Server 2012

●

Chapter 10B for Oracle Database 11 g Release 2 ●

Chapter 10C Oracle MySQL 5.6 ●