Based on the discussion of the Heather Sweeney Designs operational database (HSD) and dimensional database (HSD_DW) in the text, answer the following questions.
A. Using the SQL statements shown in Figure 12-12, create the HSD_DW database in a DBMS.
B. What possible transformations of data were made before HSD_DW was loaded with data? List some possible transformations, showing the original format of the HSD data and how they appear in the HSD_DW database.
C. Write the complete set of SQL statements necessary to load the transformed data into the HSD_DW database.
D. Populate the HSD_DW database using the SQL statements you wrote to answer part C.
E. Figure 12-43 shows the SQL code to create the SALES_FOR_RFM fact table shown in Figure 12-17. Using that statement, add the SALES_FOR_RFM table to your HSD_DW database.
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 SQL-Query-CH12-02 that uses the total dollar amount of each day's product sales as the measure (instead of the number of products 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 2019 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 format 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-25. (Hint: If you need help with the necessary Microsoft Excel actions, 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. In the HSD_DW dimensional database, create a PAYMENT_TYPE dimension table.
2. Modify the HSD_DW database to add a PaymentTypeID column to the fact 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 into a query or view that can make use of PaymentType in an OLAP report.
6. Create a Microsoft Excel 2019 OLAP report to show the effect of payment type on product sales in dollars.
FIGURE 12 43 can't copy
The HSD_DW SALES_FOR_RFM SQL CREATE TABLE Statement