Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 52 of 62
D. What transformations of data will need to be made before the JRJ-DW database can be
loaded with data? List all the transformations, showing the original format of the
JAMES_RIVER_JEWELRY data and how it appears in the JRJ-DW database.
E. Write the complete set of SQL statements necessary to load the transformed data into
the JRJ-DW database.
See the file DBC-e08-MSSQL-JRJ-DW–Insert-Data.sql.
/***** TIMELINE *************************************************************/
— use DATEVALUE(“05-MAY-2017″) etc to get TimeID values
INSERT INTO TIMELINE VALUES(
42860, ’05-May-17′, 5, ‘May’, 2, ‘Qtr2’, 2017);
INSERT INTO TIMELINE VALUES(
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 53 of 62
INSERT INTO PURCHASE_PRICE VALUES(42860, 1, 1, 155.00);
INSERT INTO PURCHASE_PRICE VALUES(42862, 2, 2, 203.00);
INSERT INTO PURCHASE_PRICE VALUES(42866, 3, 1, 75.00);
INSERT INTO PURCHASE_PRICE VALUES(42870, 6, 1, 35.00);
/*********************************************************************************/
F. Populate the JRJ-DW database, using the SQL statements you wrote to answer part E.
G. Write an SQL query similar to the one shown in the text on page 499 that uses retail
price as the measure.
See the file DBC-e08-MSSQL-JRJ-DW–CH08-Questions-G-H.sql.
/* ******** Question G Query ******************************************/
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 54 of 62
H. Write the SQL view equivalent of the SQL query you wrote to answer part G.
See the file DBC-e08-MSSQL-JRJ-DW–CH08-Questions-G-H.sql.
I. Create the SQL view you wrote to answer part H in your JRJ-DW database.
J. Create a Microsoft Excel 2016 workbook named JRJ-DW–BI-Exercises.xlsx.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 55 of 62
K. Using either the results of your SQL query from part G (copy the results of the query into
a worksheet in the JRJ-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 Excel
help system for more information.)
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 56 of 62
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
If you have not already implemented the Queen Anne Curiosity Shop database shown in
Chapter 3 in a DBMS product, create and populate the QACS database now in the
DBMS of your choice (or as assigned by your instructor).
A. You need about 30 PURCHASE transactions in the database. Write the needed SQL
statements for any needed additional PURCHASE transactions and insert the data into
your database.
B. Design a data warehouse star schema for a dimensional database named QACS-DW.
The fact table measure will be ItemPrice.
We’ll take a look at how customers react to products from various vendors.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 57 of 62
C. Create the QACS-DW database in a DBMS product.
This database was created in SQL Server Express
See the file DBC-e08-MSSQL-QACS-DW-Create-Tables.sql.
CREATE TABLE CUSTOMER(
CREATE TABLE VENDOR(
VendorID Int NOT NULL,
VendorName Char(100) NOT NULL,
CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID)
);
CREATE TABLE SALE_ITEM(
PRIMARY KEY (TimeID, CustomerID, ItemID, VendorID),
CONSTRAINT SI_TIMELINE_FK FOREIGN KEY (TimeID)
REFERENCES TIMELINE(TimeID)
ON UPDATE NO ACTION
);
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 58 of 62
D. What transformations of data will need to be made before the QACS-DW database can
be loaded with data? List all the transformations, showing the original format of the
QACS and how it appears in the QACS-DW database.
E. Write the complete set of SQL statements necessary to load the transformed data into
the QACS-DW database.
See the file DBC-e08-MSSQL-QACS-DW-Insert-Data.sql.
/***** TIMELINE *************************************************************/
— use Excel DATEVALUE(“14-DEC-2016″) etc to get TimeID values
/***** CUSTOMER DATA ********************************************************/
INSERT INTO CUSTOMER VALUES(
1, ‘Shire, Robert’, ‘Seattle’, ‘WA’, ‘98103’);
INSERT INTO CUSTOMER VALUES(
2, ‘Goodyear, Katherine’, ‘Seattle’, ‘WA’, ‘98105’);
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 59 of 62
/***** VENDOR DATA **********************************************************/
INSERT INTO VENDOR VALUES(1001, ‘Linens and Things’);
INSERT INTO VENDOR VALUES(1002, ‘European Specialties’);
INSERT INTO VENDOR VALUES(1003, ‘Lamps and Lighting’);
/***** ITEM DATA ************************************************************/
INSERT INTO ITEM VALUES(1000001, ‘Antique Desk’);
INSERT INTO ITEM VALUES(1000002, ‘Antique Desk Chair’);
INSERT INTO ITEM VALUES(1000003, ‘Dining Table Linens’);
INSERT INTO ITEM VALUES(1000004, ‘Candles’);
INSERT INTO ITEM VALUES(1000005, ‘Candles’);
INSERT INTO ITEM VALUES(1000006, ‘Desk Lamp’);
/***** SALE_ITEM DATA *******************************************************/
— use Excel DATEVALUE(“14-DEC-2016″) etc to get TimeID values
INSERT INTO SALE_ITEM VALUES(42718, 1, 1000001, 1002, 3000.00);
INSERT INTO SALE_ITEM VALUES(42718, 1, 1000002, 1004, 500.00);
INSERT INTO SALE_ITEM VALUES(42719, 2, 1000003, 1001, 1000.00);
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 60 of 62
INSERT INTO SALE_ITEM VALUES(42747, 6, 1000008, 1005, 250.00);
INSERT INTO SALE_ITEM VALUES(42750, 2, 1000009, 1006, 1250.00);
INSERT INTO SALE_ITEM VALUES(42750, 2, 1000010, 1006, 1750.00);
INSERT INTO SALE_ITEM VALUES(42760, 5, 1000011, 1002, 350.00);
/********************************************************************************/
F. Populate the QACS-DW database, using the SQL statements you wrote to answer part
E.
G. Write an SQL query similar to the one shown in the text on page 456 that uses retail
price as the measure.
See the file DBC-e08-MSSQL-QACS-DW-CH08-Questions-G-H.sql.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 61 of 62
H. Write the SQL view equivalent of the SQL query you wrote to answer part G.
See the file DBC-e08-MSSQL-QACS-DW-CH08-Questions-G-H.sql.
I. Create the SQL view you wrote to answer part H in your QACS-DW database.
This is self-explanatory.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 62 of 62
J. Create a Microsoft Excel 2016 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 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
Excel help system for more information.)