Data Warehousing

Fall 2007


General

Syllabus, Fall 2007

doc Graduate Topics Fall 2005

Please be sure that I have a resume from you.

SQL Resources

doc SQL Review using MS Access

doc WholesaleProducts database for SQL Review using MS Access

doc Data Manipulation Syntax in SQL.

doc Data Definition Statements in SQL.

doc Installing MS products.

SAS Resources

For a student copy of SAS, contact Lane Reiser (1536)

pdf Using SAS for data cleaning.

pdf General principles for data cleansing in the context of a Customer Relationship Management (CRM) product called Maximizer.

link Online SAS Documentation

doc SAS Introduction

doc SAS and Data Manipulation

doc Frequently Asked Questions in SAS Data Cleansing

References

**1************

Introduction

Monday, 08/20/07

ppt Introduction

ppt Basic Concepts of BI/DW

Wednesday, 08/22/07

ppt SQL Overview

ppt Defining Business Requirements

**2************

Monday, 08/27/06

ppt Babysitter Example

mdb Babysitter Transaction Database

mdb Babysitter Data Warehouse

doc Dimensional Modeling Introduction

ppt GB Video Example

Resume due.

Wednesday, 08/29/06

ppt Ch 4, Requirements Gathering

ppt ETL Processing using SQL Server Syntax

mdb Babysit Examples From Class

Homework: Due 9/07/06 Write SQL code to

* Extract the data from the Sales database into a new table

* Generate surrogate keys

* Add the surrogate keys into a new analysis table

mdb Sales Data for Homework

doc Getting Started with SQL Server

****** Dimensional Modeling: Designing the data warehouse *****

**3************

Monday, 09/03/07

LABOR DAY HOLIDAY

Wednesday, 09/05/07

ppt Introduction to Dimensional Modeling

doc Introduction to Data Warehouse Development

mdb Premiere Products E-R model (Source Tables)

mdb Premiere Products Staging Tables

mdb Premiere Products Presentation Data Mart

ppt Ch 4, Dimensional Modeling

**4************

Monday, 09/10/07

Wednesday, 09/12/07

**5************

Monday, 09/17/07

Wednesday, 09/19/07

ppt Dimensional Model Types

doc Dimensional Model Types

doc Additional Dimensional Modeling Exercises

**6************

Monday, 09/24/07

ppt Ch 2, Dimensional Modeling

Wednesday, 09/27/07

xls Exercise: Follow the instructions in the Excel file to create a course data warehouse.

****** The ETL Process: Loading the data warehouse *****

**7************

Monday, 10/01/07

Wednesday, 10/03/07

class

**8************

Monday, 10/08/07

Wednesday, 10/10/07

doc SAS Introduction

doc SAS and Data Manipulation

doc Frequently Asked Questions in SAS Data Cleansing

pdf Using SAS for data cleaning.

pdf SAS Data Cleaning Guidelines.

doc Data for SAS Data Cleaning Guidelines.

**9************

Tuesday, 10/15/07

doc SAS Introduction

doc SAS and Data Manipulation

pdf Using SAS for data cleaning.

pdf General principles for data cleansing in the context of a Customer Relationship Management (CRM) product called Maximizer.

Wednesday, 10/17/07

<<<<<<<<<<<<<<<<<<<<< Quiz 1 >>>>>>>>>>>>>>>>>>>>>>>

doc Quiz 1 from Spring 2003

**10************

Monday, 10/22/07

****** The ETL Process: Data Preparation and Cleansing *****

ppt Data Quality Issues: combining records

doc The Data Cleansing Process (Text)

ppt The Data Cleansing Process

doc SAS for the data cleaning process

mdb SAS database for quality editing.

doc SAS for standardizing the Supplier field in the Products table

Wednesday,10/24/07

**11************

Monday, 10/29/07

ppt Data Transformation in SAS

xls Fall 2007 Classes

xls Spring 2008 Classes

ppt Code for Data Parsing

doc SAS for Parsing the PCODE in the Products table

sas SAS for cleaning the Name field of the Demographics table

Thursday, 10/31/07

sas SAS for merging the Catalog and Products tables

doc Understanding the operations of Merge and Proc SQL

doc SAS DATA CLEANING EXERCISE DUE 10/14/07

pdf SAS Data Cleaning Guidelines.

txt Data for SAS Data Cleaning Guidelines (new version).

**12************

Monday, 11/05/07

sas SAS code for creating the date dimension

ppt Working with dates and time in SAS

doc Graduate Topics Fall 2005

doc Functions, Informats and Formats in SAS

Wednesday, 11/07/07

doc Graduate Topics Fall 2005

SAS code used so far

ppt SAS for the data cleaning process

sas SAS code for editing numeric entries

ppt The ETL Process

doc Babysitter Club Exercise: Documentation

ppt Babysitter Example

mdb Babysitter Transaction Database

mdb Babysitter Data Warehouse

ppt ETL Documentation

doc Source Table Definition Worksheet

doc Source to Target Extraction Worksheet

doc Derived Fact Worksheet

doc Dimensional Model Table Metadata Worksheet

**13************

Monday,11/12/07

doc SAS code for charts and plots

ppt Structered Progamming in SAS

Wednesday, 11/14/07

**14************

Monday, 11/19/07

pdf SAS Data Cleaning Guidelines (Cody, et. al.).

txt Data for SAS Data Cleaning Guidelines (new version).

doc Modified Code from the cleaning guidelines above.

Wednesday, 11/21/07

********* THANKSGIVING HOLIDAY *********************

**15************

Monday, 11/26/07

Assignment: Due Wednesday December 5

* Import the Access dimensional database below into SQL/Server

* Create a Data Mart using SQL/Server Analysis Services

* Deploy and explore a multidimensional data cube using SQL/Server Analysis Services

mdb Wholesale Products Dimensional Model

doc Importing Databases into SQL/Server (review, from MS tutorial)

doc Creating a simple DW Mart in SQL/Server Analysis Services (from MS tutorial)

doc Deploying and Viewing a Data Cube in SQL/Server Analysis Services (from MS Tutorial)

Wednesday, 11/28/07

ppt Introduction to Data Warehouse Infrastructure (Kimball Ch 11)

ppt Infrastructure Issues for a Data Warehouse

ppt Developing End User Applications

ppt Standard Dialog Types

**16************

Monday, 12/03/07

ppt User Based Design Decisions

ppt Using Data Warehouses Effectively (OU Research Report)

Wednesday, 12/05/07

<<<<<<<<<<<<<<<<17 EXAM WEEK >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Exam:

doc Exam from Spring 2003

doc Exam Case and Questions (questions at end) Due Wed.

OLD ITEMS **********************

ppt Busines Intelligence - Jai Windsor

ppt Customer Relationship Management - Vinod Ramanathan

EXERCUSE DUE: Please turn in a clean SAS log that does the following to the SAS example database:

* Cleans the Supplier field of the Products table

* Extracts a product category from the PCODE variable in the Products table

* Parses the name field of the Demographics table into first and last names as correctly as you can

* Corrects the obvious PCODE errors in the Customer_Orders table, and

* Joins the Products and Demographics tables to the Customer_Orders table

doc Dimensional Modeling Class Problems

doc Homework 1: Dimensional Modeling Problem, due Tuesday, 9/26/06.

ppt Dialog Development Strategies

ppt Standard Dialog Types

ppt User Based Design Decisions

ppt Introduction to Prototyping

doc SAS for October 28

doc Exercises for in class SAS.

Homework Assignment: Convert the following data sets into a data warehouse intended to support class planning. Ignore the columns for male and female students. Your deliverable should be a working MS Access database following the design below. The edit process should be done in SAS. Your deliverable should include good documentation of how your data cleansing logic works along with a copy of the clean SAS log for each of the programs you used. Due in my mailbox December 13, 5:00 p.m.

zip Course Offerings Database

ppt Dimensional Model for the Course Offerings Data Warehouse

Graduate homework assignment use your book and any other resources necessary to write a white paper (short report on the state of business practice) about the ongoing management of a data warehouse. Good resources can be found in Intelligent Enterprise. Your report should be 8 to 12 pages in length. You should also provide 8 - 10 PowerPoint slides that I could use for a future class lectures. Due in my mailbox December 13, 5:00 p.m.

mdb Dataset from SAS for class edit examples.

doc SAS code for consolidating and parsing data.

doc ETL SAS example from Premiere Products

ppt Using SQL and SAS to create the date dimension for Premiere Products

doc SAS sample code for developing displays.

doc Coding instructions for Problm 4.

doc Coding instructions for Problem6.

ppt Kerr McGee Visit: Extraction and Presentation using DW Tools

**15**

Monday, 11/24/03

ppt User Based Design Decisions

Wednesday, 11/26/03

**16**

Monday, 12/02/03

· Import and Export a Table from a Database using the SAS Wizard

· Use SCAN and SUBSTR functions to parse a variable

· Use PROC CHART and PROC GCHART to display a single level of variable

Wednesday, 12/04/03

Devon Energy

ppt Acxiom Visit: Data Warehousing Infrastructure

**15**

Monday, 12/09/03

Mini 4

· Use PROC CHART with a SUMVAR option

· Use If .. Then, DO .. END, and OUTPUT to create new attributes or to produce new datasets.

Wednesday, 12/11/03

Thursday, 03/06/03

doc Dimensional Model Homework Directions

doc Coding instructions for Problm 4.

doc Coding instructions for Problem6.

Tuesday, 04/1/03

zip Course Offerings Database

Thursday, 04/05/03

pdf Using SAS for data cleaning.

pdf General principles for data cleansing in the context of a Customer Relationship Management (CRM) product called Maximizer.

link Online SAS Documentation

****

Tuesday, 04/08/03

Thursday, 04/10/03

****

Tuesday, 04/15/03

doc Frequently Asked Questions in SAS Data Cleansing

Thursday, 04/17/03

doc Final Exercise Set. Turn in by April 29.

****

Tuesday, 04/22/03

Thursday, 04/24/03

doc ETL SAS example from Premiere Products

mdb Premiere Products Extract Example

ppt Ch 3 & 4, Project