Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
All Sections – The Access Workbench
SECTION EIGHT
CHAPTER OBJECTIVES
• Learn how to create an OLAP report for Microsoft Access databases as an Excel
PivotTable
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of The Access Workbench shows your students how to use Microsoft
Access as a DBMS to support business intelligence (BI) systems, specifically an
OLAP report in the form of an Excel Pivot table.
• Illustrate creating an Excel PivotTable in class.
ANSWERS TO ACCESS WORKBENCH EXERCISES
AW.8.1. Using the discussion of dimensional models and OLAP reports in the text and the
specific discussion of OLAP reports based on a Microsoft Access 2016 database in
this chapter’s section of “The Access Workbench” as your reference, complete
exercise 8.48 (excluding part N) for Heather Sweeney Designs. Create your HSD-
DW database in Microsoft Access 2016 and your OLAP report in Microsoft Excel
2016.
A. Using the SQL statements shown in Figure 8-10, create the HSD-DW
database in a DBMS.
Edited for Microsoft Access 2016, the SQL statements are:
All Sections – The Access Workbench
© 2018 Pearson Education, Inc. Page 93 of 102
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL,
CustomerName Char(75) NOT NULL,
EmailDomain VarChar(100) NOT NULL,
PhoneAreaCode Char(6) NOT NULL,
City Char(35) NULL,
State Char(2) NULL,
ZIP Char(10) NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)
);
CREATE TABLE PRODUCT(
CREATE TABLE PRODUCT_SALES(
);
The following table is needed for step N, which the Access Workbench Exercises do not
ask students to do. Here it is in case you want to add that question to your assignment:
If you include the PAYMENT_TYPE table, the following ALTER TABLE statement will be
needed:
If you include the PAYMENT_TYPE table, the following constraint will be needed:
All Sections – The Access Workbench
The following relationships and constraints must be entered in the Relationships
window:
Other settings (Numeric (9,2)) must be made in the GUI.
B. What transformations of data were made before HSD-DW was loaded with
data? List all the transformations, showing the original format of the HSD data
and how they appear in the HSD-DW database.
NOTE: These are the actual transformations, and do not include the same data column
in different tables (for example, LINE_ITEM.Quantity appears as PRODUCT-
SALES.Quantity, but there is no change in either the column name or data type).
All Sections – The Access Workbench
C. Write the complete set of SQL statements necessary to load the transformed
data into the HSD-DW database.
See the files:
DBC-e08-MSSQL-HSD-DW-Insert-Data-TIMELINE.sql.
DBC-e08-MSSQL-HSD-DW-Insert-Data-CUSTOMER.sql.
DBC-e08-MSSQL-HSD-DW-Insert-Data-PRODUCT.sql.
DBC-e08-MSSQL-HSD-DW-Insert-Data-PRODUCT-SALES.sql.
D. Populate the HSD-DW database, using the SQL statements you wrote to
answer question C.
E. Figure 8-26 shows the SQL code to create the SALES_FOR_RFM fact table
shown in Figure 8-15. Using those statements, add the SALES_FOR_RFM
table to your HSD-DW database.
Edited for Microsoft Access 2016, the SQL statements are:
);
The following relationships and constraints must be entered in the Relationships
window:
Other settings (for example, Numeric (9,2)) must be made in the GUI.
All Sections – The Access Workbench
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.
All Sections – The Access Workbench
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.
-- 40831, '10/15/2011', 10, 'October', 3, 'Qtr3', 2011);
/***** Invoice 35002 - '12-Dec-16' = 42724 'Sally.George@somewhere.com' = 7 */
/* INSERT INTO PRODUCT_SALES VALUES(42724, 7, 'VK004', 1, 24.95, 24.95); */
INSERT INTO SALES_FOR_RFM VALUES(42724, 7, 35002, 24.95);
All Sections – The Access Workbench
© 2018 Pearson Education, Inc. Page 98 of 102
/***** Invoice 35005 - '27-Mar-17' = 42821 'Sally.George@somewhere.com' = 7 **/
/* INSERT INTO PRODUCT_SALES VALUES(42821, 7, 'BK001', 1, 24.95, 24.95); */
/* INSERT INTO PRODUCT_SALES VALUES(42821, 7, 'BK002', 1, 24.95, 24.95); */
/* INSERT INTO PRODUCT_SALES VALUES(42821, 7, 'VK003', 1, 19.95, 19.95); */
/* INSERT INTO PRODUCT_SALES VALUES(42821, 7, 'VK004', 1, 24.95, 24.95); */
INSERT INTO SALES_FOR_RFM VALUES(42821, 7, 35005, 94.80);
INSERT INTO SALES_FOR_RFM VALUES(42825, 9, 35006, 47.89);
INSERT INTO SALES_FOR_RFM VALUES(42828, 11, 35007, 109.78);
INSERT INTO SALES_FOR_RFM VALUES(42833, 5, 35008, 47.89);
INSERT INTO SALES_FOR_RFM VALUES(42833, 1, 35009, 47.89);
INSERT INTO SALES_FOR_RFM VALUES(42848, 3, 35010, 24.95);
All Sections – The Access Workbench
© 2018 Pearson Education, Inc. Page 99 of 102
-- 41036, '5/7/2012', 5, 'May', 2, 'Qtr2', 2012);
-- 41766, '5/7/2014', 5, 'May', 2, 'Qtr2', 2014);
-- 42862, '5/7/2017', 5, 'May', 2, 'Qtr2', 2017);
/***** Invoice 35011 - '07-May-17' = 42862 'Bobbi.Pearson@elsewhere.com' = 9 */
/* INSERT INTO PRODUCT_SALES VALUES(42862, 9, 'VK002', 1, 14.95, 14.95); */
/* INSERT INTO PRODUCT_SALES VALUES(42862, 9, 'VB002', 1, 7.99, 7.99); */
INSERT INTO SALES_FOR_RFM VALUES(42862, 9, 35011, 22.94);
INSERT INTO SALES_FOR_RFM VALUES(42876, 8, 35012, 54.89);
-- 41065, '6/5/2012', 6, 'June', 2, 'Qtr2', 2012);
-- 41795, '6/5/2014', 6, 'June', 2, 'Qtr2', 2014);
-- 42891, '6/5/2017', 6, 'June', 2, 'Qtr2', 2017);
INSERT INTO SALES_FOR_RFM VALUES(42891, 11, 35014, 45.88);
All Sections – The Access Workbench
H. Populate the SALES_FOR_RFM fact table, using the SQL statements you
wrote to answer question G.
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).
SELECT C.CustomerID, C.CustomerName, C.City,
J. Write the SQL view equivalent of the SQL query you wrote to answer
question I.
CREATE VIEW HSDDWProductDollarSalesView AS
SELECT C.CustomerID, C.CustomerName, C.City,
All Sections – The Access Workbench
K. Create the SQL view you wrote to answer question J in your HSD-DW
database.
The SQL query in question J will run in Microsoft Access 2016. Save it as a view
equivalent Microsoft Access query named viewHSDDWProductDollarSaleView.
All Sections – The Access Workbench
L. Create an Excel 2016 workbook named HSD-DW-BI-Exercises.xlsx.
M. Using either the results of your SQL query from question 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) or your SQL view form question L
(create an Excel data connection to the view), create an OLAP report similar
to the OLAP report shown in Figure 8-20. (Hint: If you need help with the
needed Excel actions, search in the Excel help system for more information.)
