Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
See the file: DBC-e08-MSSQL-HSD-DW-Create-SALES_FOR_RFM.sql.
F. What transformations of data are necessary to load the SALES_FOR_RFM
table? List any needed transformations, showing the original format of the HSD
data and how they appear in the HSD-DW database.
G. What data will be used to load the SALES_FOR_RFM fact table? Write the
complete set of SQL statements necessary to load this data.
To fill in the SALES_FOR_RFM table:
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
H. Populate the SALES_FOR_RFM fact table, using the SQL statements you wrote
to answer part G.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
I. Write an SQL query similar to the one shown on page 497 that uses the total
dollar amount of each day’s product sales as the measure (instead of the number
of products sold each day).
/* ******** Exercise 8.52.I Query ******************************************/
SELECT C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
SUM(PS.Total) AS TotalDollarSales
FROM CUSTOMER C, PRODUCT_SALES PS, PRODUCT P
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
J. Write the SQL view equivalent of the SQL query you wrote to answer part I.
See the file: DBC-e08-MSSQL-HSD-DW-CH08-Exercises- 8-52–J.sql.
K. Create the SQL view you wrote to answer part J in your HSD-DW database.
See the file: DBC-e07-MSSQL-HSD-DW–CH08-Exercises- 8-52-K.sql.
Results of:
L. Create a Microsoft Excel 2016 workbook named HSD-DW–BI-Exercises.xlsx.
See the file: DBC-e08-HSD-DW–BI-Exercises.xlsx.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
M. Using either the results of your SQL query from part K (copy the results of the
query into a worksheet in the HSD-DW-BI.xlsx workbook and then format this
range as a worksheet table). You can right-click anywhere in the results pane
and Select ALL, then Copy With Headers, then paste into cell A1 of an Excel
spreadsheet. Or use your SQL view from part L (create a Microsoft Excel data
connection to the view), create an OLAP report similar to the OLAP report shown
in Figure 8-17. (Hint: If you need help with the needed Microsoft Excel actions,
See the file: DBC-e08-HSD-DW–BI-Exercises.xlsx.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
N. 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.
See the file: DBC-e08-HSD-DW-Create-PAYMENT-TYPE.sql
The HSD database INVOICE table includes a PaymentType column. Current values are VISA and
MasterCard. We will also allow for American Express, Check, and Cash. We will add the following
PAYMENT_TYPE dimension table to the HSD-DW database:
2. Modify the HSD-DW database to include the PAYMENT_TYPE
dimension table.
We will also need to modify the PRODUCT_SALES fact table by adding a PaymentTypeID column
and a foreign key constraint. This column will first be set to NULL, but after it is populated, it will
have to be reset to NOT NULL
See the database diagram on the next page.
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.
See the file: DBC-e08-HSD-DW-Insert-Data-PAYMENT-TYPE.sql
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 27 of 62
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
4. Populate the PAYMENT_TYPE and PRODUCT_SALES tables, using the
SQL statements you wrote to answer part 3.
Make sure HSD-DW is the selected schema, then run the PAYMENT_TYPE inserts in the file DBC-
e08-MSSQL-HSD-DW-Insert-Data-PAYMENT_TYPE.sql Run the UPDATE statements in the file
DBC-e08-MSSQL-HSD-DW-Update-Data-PRODUCT-SALES.sql.
Here are the first two UPDATE statements relating to INVOICE 35000:
/***** Invoice 35000 ***************************************************/
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
WHERE TimeID = 42658
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
5. Create the SQL queries or SQL views needed to incorporate the
PaymentType attribute.
Views are covered in Appendix E. See the file: DBC-e08-MSSQL-HSD-DW-CH08-
Exercises- 8-52-N.sql.
CREATE VIEW HSDDWProductDollarSalesPaymentTypeView AS
SELECT C.CustomerID, C.CustomerName, C.City,
PT.PaymentType,
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
SELECT * FROM HSDDWProductDollarSalesPaymentTypeView;
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
6. Create a Microsoft Excel 2016 OLAP report to show the effect of payment
type on product sales in dollars.
The results are in the Microsoft Excel workbook DBC-e08-HSD-DW–BI-Exercises.xlsx
THE ACCESS WORKBENCH
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
ANSWERS MARCIA’S DRY CLEANING CASE QUESTIONS
Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry
cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out
from the competition by providing superior customer service. She wants to keep track of
each of her customers and their orders. Ultimately, she wants to notify them that their
clothes are ready via email.
Assume that Marcia has hired you as a database consultant to develop an operational
database named MDC that has the following four tables:
A Microsoft Access 2016 version of the MDC database and SQL scripts to create and
populate the MDC database are available for Microsoft SQL Server 2016, Oracle
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
A. Create a database in your DBMS named MDC, and use the MDC SQL scripts for your
DBMS to create and populate the database tables. Create a user named MDC-User with
the password MDC-User+password. Assign this user to database roles so that the user
can read, insert, delete, and modify data.
B. Create an appropriate ODBC data source for your database.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
screen shot below). The ODBC system data source name for this chapter would be MDC. Also
see the Chapter 7 Marcia’s Dry Cleaning Case Question solutions in the IM for Chapter 7.
C. You need about 20 INVOICE transactions with supporting INVOICE_ITEMs in the
database. Write the needed SQL statements for any needed additional INVOICE
transactions and insert the data into your database.
D. Design a data warehouse star schema for a dimensional database named MDC-DW.
The fact table measure will be ExtendedPrice.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 35 of 62
E. Create the MDC-DW database in your DBMS product.
This example was done in SQL Server 2016.
CREATE TABLE TIMELINE(
TimeID Int NOT NULL,
[Date] Date NOT NULL,
);
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL IDENTITY (100,5),
CustomerName Char(50) NOT NULL,
EmailDomain Char(50) NULL,
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 36 of 62
CREATE TABLE SERVICE(
ServiceID Int NOT NULL,
ServiceDescription Char(50) NOT NULL,
CONSTRAINT SERVICE_PK PRIMARY KEY(ServiceID)
);
);
F. What transformations of data will need to be made before the MDC-DW database can
be loaded with data? List all the transformations, showing the original format of the MDC
data and how it appears in the MDC-DW database.
G. Write the complete set of SQL statements necessary to load the transformed data into
the MDC-DW database.
/***** CUSTOMER Data *********************************************************/
INSERT INTO CUSTOMER VALUES(
‘Kaccaton, Nikki’, ‘somewhere.com’);
INSERT INTO CUSTOMER VALUES(
‘Catnazaro, Brenda’, ‘somewhere.com’);
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 37 of 62
/***** SERVICE Data **********************************************************/
INSERT INTO SERVICE VALUES(10, ‘Men”s Shirt’);
INSERT INTO SERVICE VALUES(11, ‘Dress Shirt’);
INSERT INTO SERVICE VALUES(15, ‘Women”s Shirt’);
INSERT INTO SERVICE VALUES(16, ‘Blouse’);
/***** INVOICE Data ************************************************************/
INSERT INTO INVOICE VALUES(
2017001,’04-Oct-17′, ’06–Oct-17′);
INSERT INTO INVOICE VALUES(
2017002,’04-Oct-17′, ’06–Oct-17′);
INSERT INTO INVOICE VALUES(
/***** TIMELINE Data ************************************************************/
— Note: the TimeID values were computed from a date string using Excel:
— =DATEVALUE(“04–Oct–2017”) is 43012
— Just need DateIn dates from Invoice
/***** INVOICE_ITEM Data ********************************************************/
— Oct 4 2017 = 43012
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 38 of 62
INSERT INTO INVOICE_ITEM VALUES(
43012, 100, 2017001, 50, 20.00);
INSERT INTO INVOICE_ITEM VALUES(
43012, 100, 2017001, 20, 50.00);
INSERT INTO INVOICE_ITEM VALUES(
INSERT INTO INVOICE_ITEM VALUES(
43014, 100, 2017003, 20, 25.00);
INSERT INTO INVOICE_ITEM VALUES(
43014, 100, 2017003, 25, 24.00);
INSERT INTO INVOICE_ITEM VALUES(
43014, 115, 2017004, 11, 7.50);
— Oct 07 2017 = 43015
— Oct 12 2017 = 43020
/*********************************************************************************/
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 39 of 62
H. Populate the MDC-DW database, using the SQL statements you wrote to answer part G.
This is self-explanatory, just run the script.
I. Write an SQL query similar to the one shown in the text on page 420 that uses the
ExtendedPrice as the measure.
J. Write the SQL view equivalent of the SQL query you wrote to answer part I.
Views are covered in Appendix E.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 40 of 62
K. Create the SQL view you wrote to answer part J in your MDC-DW database.
This is self-explanatory, just execute it.
L. Create the Microsoft Excel 2016 workbook named MDC-DW–BI-Exercises.xlsx.
This is self-explanatory.
M. Using either the results of your SQL query from part I (copy the results of the query into
a worksheet in the MDC-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 connection
to the view), create an OLAP report similar to the OLAP report shown in Figure 8-16.
(Hint: If you need help with the needed Microsoft Excel actions, search in the Microsoft