Database Storage & Design Appendix N All Sections The Access Workbench Section Eight Objectives Learn How Create

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

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

/***** 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

— 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.)