Data Warehouse

1
Data Warehouse Data Mining

• Rich Willman

2
Data Warehouses in the Wild

• What is your zip code?
• What is your phone number?
• Please take the time to register

3

• PROFIT

4
Reasons for Investing

• Return over investment (ROI) of gt 400
• Most every company implementing a Data Warehouse
  sees some increase in revenue.
• Helps the company focus.
• Your results may vary.

5
Executive Summary

• Toys And Gift Sales
• Small business supplying toys and gifts in bulk
  to charitable organizations, corporate groups,
  non-profit organizations, and government outfits.
• Was personal for me.
• Entire companies records fit in one Data Mart.

6
What Data Do We Store??

• Items. Toys, gifts, nic-nacks, etc.
• Who we sold it to.
• Who we bought it from.
• How much did it cost.
• How much did it sell.

7
Modeling the Data
8
Modeling the Data Cont.
9
Choosing the Grain
10
Choosing the Grain Cont.
11
Choosing the Grain Finally
12
Date Issue

• One issue I faced with the use of the date type,
  is that no dates would verify.
• Solution use regedit to add NLS_DATE_FORMAT.

13
Creating Tables

• /
  
• script to create the OrderPart table
• 
  /
• CREATE TABLE OrderPart(
• OrderPartID number PRIMARY KEY,
• OrderID number NOT NULL,
• ItemID number NOT NULL,
• Quantity number NOT NULL,
• .
• .
• .
• .
• .
• .

14
ETL - First Problem

• Current Database partially in Quickbooks and a
  written notebook.
• Not conducive to automated ETL
• ETL would have to be done by hand.

15
ETL Second Problem

• NO DATA AVAILABLE!!
• Uncomfortable with giving out company data.
• Too late to start over.
• How to get data?

16
Getting Some Data

• Created a C program to generate random data based
  on some real life examples.
• Still able to do some data analysis even though
  the results would not be useful.
• Next time think further ahead!!

17
Data Mining Views

• Views make data mining and analysis easier.
• Helpful Views here are for the different fiscal
  years.
• Also created Views for customers in each state.

18
Data Analysis

• Analysis of random data.
• Joined the fiscal year views with the views of
  customers by state.
• Found that Texas had the largest sales in 2002.

19
Lessons Learned

• Verify your data sources first!
• Fixing the Date problem.
• Allow plenty of time.
• Spell Louisiana!

20

• QUESTIONS?