ISQS 6339 Lecture Notes

 

Instructor: Zhangxi Lin

 

-----------------------------------------------------------------------------------

Home | Schedule | Projects | Video Demons

-----------------------------------------------------------------------------------

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 1, 01/23/2012, Monday

 

Topic: Introduction

1)       Basic BI Concepts

2)       BI trend

 

Review questions:

 

1)     What is BI?

2)     Why is BI getting hot?

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 2, 01/25/2012, Wednesday

 

Topic: Anatomy of Business Intelligence (1)

 

1)       Cases

2)       BI Framework

3)       Applications

4)       BI tools

 

Terminology: data, information, knowledge, business intelligence, data warehouse, meta data, ETL, business rules, OLAP

 

Reading:

1)     “How Much Information? 2003” (http://www2.sims.berkeley.edu/research/projects/how-much-info-2003/execsum.htm)

2)     Find a BI application case from the web, and understand how it works.

3)     Find paper “CACM2011 Overview of BI.pdf” in the network drive under ~\Texts\Readings\. Read it carefully.

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 3, 01/30/2012, Monday

 

Topic: Anatomy of Business Intelligence (2)

 

1)       Microsoft SQL Server 2008

2)       Exercise 1: Database vs. data warehousing

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 4, 02/01/2012, Wednesday

 

Topic: Data warehousing

 

1)       Concepts

2)       Architecture

3)       Data modeling

4)       ETL

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 5, 02/06/2012, Monday

Topic: Data Warehousing

1)     Quiz 1

2)     Dimensional modeling

3)     Unified dimensional model

4)     Data warehousing methodology

 

References:

 

1)     Inmon vs. Kimball - Book

2)     Inmon vs. Kimball - comments

3)     Kimball and Inmon DW Models

4)     Design of data warehouse

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 6, 02/08/2012, Wednesday

 

Topic: Creating data mart

 

1)     Quiz 1 review

2)     Data warehousing with SQL Server 2008

3)     Exercise 2 - Create a data mart

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 7, 02/13/2012, Monday

 

Topic: Creating data mart (2)

 

1)     Exercise 2 Review

2)     How to create data mart with SSAS - demon

3)     Term project orientation

4)     Exercise 3 – Create data mart with BIDS

 

Online References:

1)     Measures: http://en.wikipedia.org/wiki/Measure_%28data_warehouse%29

2)     Dimensions: http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29

3)     Slowly changing dimensions : http://en.wikipedia.org/wiki/Slowly_changing_dimension

4)     Surrogate keys : http://www.geekinterview.com/question_details/17591

5)     Aggregate: http://en.wikipedia.org/wiki/Aggregate_%28Data_Warehouse%29

6)     Many-to-many relationship in data warehousing: http://www.pythian.com/news/364/implementing-many-to-many-relationships-in-data-warehousing/

7)     Degenerate dimensions: http://en.wikipedia.org/wiki/Degenerate_dimension

 

Homework assignment 1 (optional):

 

A.    Using dataset Commrex_2011.xls to develop a logical design of multi-dimensional model. Take into the following requests into consideration and explain why you decide the approach:

1)     Host both table Office and Land into the datawareehouse

2)     The fact table must reflect the updates of listing status each time (hints: use SCD)

3)     If realtors will form some kind of partnership to work on a groupd of property listings, how the dimensional model will be resigned?

B.    Search the web to find the comprehensive explanations of the following terms. Give an expample for each of them.

1)     Junk dimensions

2)     Many-to-many or multivalued dimensions

3)     Degenerate dimensions

 

Homwork is due 2/22 at 8p. Send your answers by email isqs6347@gmail.com, with a subject title "ISQS 6339 homework 1 - <your name>". A late submission with a week after the deadline is also fin,e but may not receive the timely feedback.

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 8, 02/15/2012, Wednesday

 

Topic: Creating data mart (3)

 

1)     More about dimensional modeling with SQL Serve 2008

2)     An introduction to SSIS

3)     Control flow tasks

4)     Data flow items

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 9, 02/20/2012, Monday

 

Topic: ETL system development (1)

 

1)     Quiz 2

2)     Exercise 4 – Populating Dimension Tables for Maximum Miniatures Manufacturing Data Mart (Guidelines)

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 10, 02/22/2012, Wednesday

 

Topic: ETL system development (2)

 

1)     ETL application debugging

2)     How to create a time dimension

3)     An introduction to SSAS

 

Homework assignment 2 (Due 03/07/2012, Wednesday):

 

The following assignments are optional. Students who fulfill any of the following questions will receive extra credits.

 

Check Updating_database.htm.  Follow the instructions in the file to complete the ETL system development. Submit the results via email to isqs6347@gmail.com .

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 11, 02/27/2012, Monday

 

Topic: ETL system development (3)

 

1)     Extending ETL skills

2)     Illustrative example: Updating database

3)     Exercise 5 – Populating Fact Tables (Guidelines)

 

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 12, 02/29/2012, Wednesday

 

Topic: ETL system development (4)

 

1)     Continue Exercise 5

2)     Advanced topics in ETL

 

------- + ------ + ------- + ------- + ------- + -------

Lecture 13, 03/05/2012, Monday

 

Topic: Cubism (1)

 

1)     MaxMinManufacture Data Mart Debugging

2)     A simple cube for MaxMinManufacture Data Mart

3)     Defining a time dimension

4)     Exercise 6 – Create cube for MaxMinManufacture Data Mart

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 14, 03/07/2012, Wednesday

 

Topic: Cubism (2)

 

1)     Quiz 3

2)     Continue Exercise 6

3)     Advanced topics in OLAP

 

------- + ------ + ------- + ------- + ------- + -------

 

Spring Break

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 15, 03/19/2012, Monday

 

Topic: Cubism (3)

 

1)     Calculation and KPI

2)     Exercise 7: Customize a cube

3)     Introduction to SAS programming

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 16, 03/21/2012, Wednesday

 

Topic: SAS Programming (1)

 

Online learning instructions

 

Readings: Chapter 1-4, Prog-I

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 17, 03/26/2012, Monday

 

Topic: SAS Programming (2)

 

Exercise 8

 

Readings: Chapter 5-6, Prog-I

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 18, 03/28/2012, Wednesday

 

Topic: SAS Programming (3)

 

1)     Project Stage 2 review

2)     Basic SAS INPUT statements

Readings: Chapter 6, Prog-I; Chapter 4, PROG-II

 

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 19, 04/02/2012, Monday

 

Topic: SAS Programming (4)

 

E-learning & Exercise 9

 

Readings: Chapter 7-8, Prog-I;

 

References:

1.   http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146292.htm

2.   http://www2.sas.com/proceedings/sugi29/253-29.pdf

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 20, 04/04/2012, Wednesday

 

Topic: Enterprise Guide – Getting Started

1)             Quiz 4

2)             Introduction

3)             Data access and process

4)             Listing report

5)             Exercise 10a

Readings: EG Chapter 1-3, Prog-I

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 21, 04/09/2012, Monday

 

Topic: Enterprise Guide - Tasks

 

1)     EG tasks

b.    Frequency reports

c.     Variable parameters

2)     Exercise 10b

 

Readings: EG Chapter 3

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 22, 04/11/2012, Wednesday

 

Topic: Enterprise Guide – Simple Queries

1)         SAS EG

o    How to access SQL Server tables

o    Basic queries

o    Creating new variables

o    Joining tables

2)         Exercise  11a

Readings: EG Chapter 4

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 23, 04/16/2012, Monday

 

Topic: Enterprise Guide – Summarized Outputs

 

1)         Quiz 6

2)         SAS EG

o    Summary statistics

o    Tabular summary report

3.         Graphs

3)         Exercise 11b

 

Readings: EG Chapter 5

 

------- + ------ + ------- + ------- + ------- + -------

 

Lecture 24, 04/18/2012, Wednesday

 

Topic: Statistical Analysis with Enterprise Guide

 

1)         ANOVA

2)         Regression

 

Readings: EGBS Chapter 2

 

------- + ------ + ------- + ------- + ------- + -------