1

All Powder Board and Ski

Microsoft Access WorkbookChapter 8: Data Warehouses and Data MiningJerry PostCopyright © 2003

2

Desired Sales Cube Dimensions

Sales Dimensions

State (ship)MonthCategoryStyleSkillLevelSizeColorManufacturerBindingStyleWeightMax?ItemMaterial?WaistWidth?

3

Early Data: Spreadsheets

4

Create Customer and Employee

CustomerID and EmployeeID are missing from the old data.Instead of relying on blank cell values, create a new customer called “Walk-in” and a new employee called “Employee”Write down the ID numbers generated for these anonymous entries.If you use SQL, you can assign a value of zero to these entries.

INSERT INTO Customer (CustomerID, LastName)Values (0,'Walk-in')

INSERT INTO Employee (EmployeeID, LastName)Values (0,'Staff')

5

Extract Model Data

SELECT DISTINCT OldSales.ModelID, OldSales.ManufacturerID, OldSales.Category, OldSales.Color, OldSales.ModelYear, OldSales.Graphics, OldSales.ItemMaterial, OldSales.ListPrice, OldSales.Style, OldSales.SkillLevel, OldSales.WeightMax, OldSales.WaistWidth, OldSales.BindingStyleFROM OldSales;

6

UNION Query for Models

SELECT DISTINCT ModelID, ManufacturerID, Category, …

FROM OldSales

UNION

SELECT DISTINCT ModelID, ManufacturerID, Category, …

FROM OldRentals

7

Insert Model Data into ItemModel

INSERT INTO ItemModel (ModelID, ManufacturerID, Category, Color, ModelYear, Graphics, ItemMaterial, ListPrice, Style, SkillLevel, WeightMax, WaistWidth, BindingStyle)SELECT DISTINCT qryOldModels.ModelID, qryOldModels.ManufacturerID, qryOldModels.Category, qryOldModels.Color, qryOldModels.ModelYear, qryOldModels.Graphics, qryOldModels.ItemMaterial, qryOldModels.ListPrice, qryOldModels.Style, qryOldModels.SkillLevel, qryOldModels.WeightMax, qryOldModels.WaistWidth, qryOldModels.BindingStyleFROM qryOldModels;

8

Insert SKU Data into Inventory

INSERT INTO Inventory (ModelID, SKU, Size, QuantityOnHand)SELECT DISTINCT qryOldInventory.ModelID, qryOldInventory.SKU, qryOldInventory.Size, 0 As QuantityOnHandFROM qryOldInventory;

Note the use of the column alias to force a zero value for QuantityOnHand for each row

9

Copy Sales Data

INSERT INTO Sales (SaleID, SaleDate, ShipState, ShipZIP, PaymentMethod)SELECT DISTINCT OldSales.SaleID, OldSales.SaleDate, OldSales.ShipState, OldSales.ShipZIP, OldSales.PaymentMethodFROM OldSales;

Note that if you have added data to your Sales table, your existing SaleID values might conflict with these

You can solve the problem by adding a number to these values so they are all larger than your highest ID

INSERT INTO Sales (SaleID, SaleDate, ShipState, ShipZIP, PaymentMethod)SELECT DISTINCT OldSales.SaleID+5000, OldSales.SaleDate, OldSales.ShipState, OldSales.ShipZIP, OldSales.PaymentMethodFROM OldSales;

10

Copy SaleItem Rows

INSERT INTO SaleItem (SaleID, SKU, QuantitySold, SalePrice)SELECT DISTINCT OldSales.SaleID+5000, OldSales.SKU, OldSales.QuantitySold, OldSales.SalePriceFROM OldSales;

If you transformed the SaleID in the prior step for the Sale data, you must do the exact same calculation for SaleID in the SaleItem table

11

Query for PivotTable

Include all desired sale dimensions Compute Value as quantity times price

Format SaleDate as year and month: yyyy-mm

12

PivotTable Form Wizard

13

PivotTable Screen

Place columns (month)

Place rows (State, Category)

Place Value last

Place other columns

14

PivotTable

Right click to select all columns and choose Hide Details

15

PivotTable Groups

16

Time Series Analysis: Excel

17

GIS: Microsoft MapPoint

The PivotTable places the data into rows and columns

A dynamic copy of this sheet is used to remove the top rows

18

MapPoint Data Wizard

19

GIS Analysis of Sales

20

Sales by State for Regression

Note that some states are missing from the list.

21

Regression Setup

You should include the label row but be sure to check the box to show you included it

22

Regression Results

Relatively high R-square

Population is a significant predictor, income is not