ISQS 6339 - Data Management &
Business Intelligence
Instructor:
======================================================
Home | Schedule | Sign-up/Update | Students | Records | Group Sign-up | View Groups | Projects
======================================================
Stage 1: Introduction to Business Intelligence
------- + ------ +
------- + ------- + ------- + -------
Lecture 1, 8/26/2008,
Tuesday
Topic: Introduction
1.
Basic
BI Concepts
2.
BI
trend
Review questions:
- What is BI?
- Why is BI
getting hot?
Reading:
“Competing
on Analytics,” by Thomas H. Davenport
------- + ------ +
------- + ------- + ------- + -------
Lecture 2, 8/28/2008,
Thursday
Topic: Database
Review
Homework
1 (Due 9/9/2008, Tuesday):
1)
Write
an essay of 2 pages for a BI application case. Explain the goal, measure, and
consequences of the BI project.
2)
Apply
SSMS to create a database using the dataset
in the Excel format.
------- + ------ +
------- + ------- + ------- + -------
Lecture 3, 9/2/2008,
Tuesday
Topic: Anatomy of
Business Intelligence
1.
Concepts
2.
Applications
3.
Methodology
Terminology: data, information,
knowledge, business intelligence,
data warehouse, metadata, ETL, business rules, OLAP
Video show: ~\MS_SQL2005\Modules\Module
2\Training\2-01 - Slides
Review questions:
1)
What
is the relationship between BI program and IT project management?
2)
Explain
the business dimensional lifecycle model
------- + ------ +
------- + ------- + ------- + -------
Lecture 4, 9/4/2008,
Thursday
Topic: Data
Warehousing and Dimensional Model
1.
Data
warehousing architecture
- Dimensional modeling
- Developing data warehouse
Review questions:
1)
How
to conduct business requirements analysis for the BI program?
2)
Can
you explain the main features of Microsoft SQL Server 2005 for BI projects?
Reading: Chapter 3
------- + ------ +
------- + ------- + ------- + -------
Stage 2: Principles of Data Warehousing
------- + ------ +
------- + ------- + ------- + -------
Lecture 5, 9/9/2008,
Tuesday
Topic: Getting
Started with Microsoft SQL Server 2005
1.
Quiz
1
- Exercise
1 – Getting start with Microsoft SQL Server 2005
- Case
study – Adventure Works Cycles
Review questions:
1.
What
is the relationship between BI program and IT project management?
- Can
you explain the main features of Microsoft SQL Server 2005 for BI
projects?
Reading: Chapter 4
------- + ------ +
------- + ------- + ------- + -------
Lecture 6, 9/11/2008,
Thursday
Topic: Data
warehousing methodology
1)
Data
warehousing methodology
2)
Hands-on
of SQL Server 2005
Review questions:
1.
How
to conduct business requirements analysis for the BI program?
- What
are main different uses of BIDS from those of MMSM?
Reading: Chapter 5
------- + ------ +
------- + ------- + ------- + -------
Lecture 7, 9/16/2008,
Tuesday
Topic: Dimensional
Modeling
1)
Maximum
Miniatures Manufacturing data mart
2)
Exercise
2 – Creating a data mart
Dataset/code:
http://www.mhprofessional.com/product.php?cat=112&isbn=0072260904
Homework
2 (Due 9/23/2008, Tuesday):
Define
a cube using the tables in MaxMinManufacturingDM data mart (The tables are in
your database “ISQS6339-lastname” if you have done it). Figure out how you can
remove a dimension, such as DimBatch, from the cube, or add in a new dimension
to the cube. The deliverable is the screenshot of the cube structure.
------- + ------ +
------- + ------- + ------- + -------
Lecture 8, 9/18/2008,
Thursday
Topic: Creating and
populating data mart
1)
Demonstration:
how to create a cube using a template
2)
Exercise
3
3)
An
introduction to SSIS
4)
Trying
out an ETL project with SSIS
------- + ------ +
------- + ------- + ------- + -------
Lecture 9, 9/23/2008,
Tuesday
Topic: ETL System
Development with SSIS (1)
1)
Quiz
2
2)
More
about dimensional modeling
3)
Continue
the topics on 9/18
4)
About
the term project
Reading:
Chapter 7
------- + ------ +
------- + ------- + ------- + -------
Lecture 10,
9/25/2008, Thursday
Topic: ETL System
Development with SSIS (2)
1)
Exercise
4 – Populating Maximum Miniatures Manufacturing Data Mart
2)
Control
flow tasks
3)
Data
flow items
Reading:
Chapter 7
Dataset:
AccountingSystem.DBC
------- + ------ +
------- + ------- + ------- + -------
Lecture 11,
9/30/2008, Tuesday
Topic: ETL System
Development with SSIS (3)
1)
Populating
MaxMinManufacturingDM data mart
2)
ETL
application debugging
Reading:
Chapter 7
------- + ------ +
------- + ------- + ------- + -------
Lecture 12,
10/2/2008, Thursday
Topic: ETL System
Development with SSIS (4)
1)
Extending
ETL skills
2)
Illustrative
example: Updating database
3)
Exercise
5 – Exploring features of ETL tasks
Reading:
Chapter 7
Reference: SQL
Server 2005 Integration Services, McGraw Hill Osborne, 2007
Homework
3 (Due 10/14/2008, Tuesday):
Download
Updating_database.htm. Follow the instructions in the file to
complete the ETL system development. Submit the results via email to Zhangxi.lin@hotmail.com.
------- + ------ +
------- + ------- + ------- + -------
Lecture 13,
10/07/2008, Tuesday
Topic: ETL System
Development with SSIS (5)
1)
More
ETL features (data conversion, derived column, sort, etc.)
2)
Project
topic demonstration: How to create a dimension table from a data set.
3)
Homework
3 Q/A.
4)
Exercise
5 – Explore features of SSIS ETL
Reading:
Chapter 7
Other
issues: Form project team; start to look for a project topic.
The
following assignments are optional. Students who fulfill any of the following questions
will receive extra credits.
1.
In
the shared directory \\bam208\ISQS6339\SSIS\, find “SSIS PROJECT - UPDATING
DATABASE.
2.
The
datasets in Excel file Commrex_2008.xls in the shared directory
\\bam208\ISQS6339\Datasets\data0 has a table “transactions”. Tasks:
1)
Based
on previous Commrex_2008 exercise, include the transaction table in the
dimensional model. Yu may need to add in new Surrogate keys to the tables.
2)
If
the transactions update “land” or “Mfami” fact table which are Type 1 SCD what
should you do with the model design?
3)
There
are two member telephone updates. Redesign the dimensional model with type 2
Slowly Changed Dimension for the member database. Show how the SCD is
implemented.
4)
Transaction
#804 contains a wrong ID. How to correct it by ETL process?
5)
Optional:
Design an ETL system to update the database using the property transaction
records. You may refer to the SCD example in one of the packages in the
exercise “SSIS PROJECT - UPDATING DATABASE.
------- + ------ +
------- + ------- + ------- + -------
Lecture 14,
10/09/2008, Thursday
Topic: Getting
started with SAS Enterprise Guide
1)
Quiz
3
2)
File
access with SAS EG
3)
Illustrative
example: Updating database
Reading:
SAS EG Chapter 1-2
Homework
4 (Due 10/21/2008, Tuesday):
Exercises for Chapter
3 and Chapter 4 in Querying and Reporting Using SAS® Enterprise Guide®, Page
A5-A12. Submit the screenshots of the outcome to Zhangxi.lin@hotmail.com with email
subject “ISQS 6339 Homework 4”.
------- + ------ +
------- + ------- + ------- + -------
Lecture 15,
10/14/2008, Tuesday
Topic: GETTING Start
with SAS EG Tasks
1)
Tasks:
Listing, Frequency report
2)
Exercise
6
Reading:
SAS EG Chapter 3
------- + ------ +
------- + ------- + ------- + -------
Lecture 16,
10/16/2008, Thursday
Topic: Creating
Simple Queries
1)
Querying
2)
Exercise
7
Reading:
SAS EG Chapter 3
------- + ------ +
------- + ------- + ------- + -------
Lecture 17,
10/21/2008, Tuesday
Topic: Cubism –
Measures and Dimensions (1)
1)
Quiz
4
2)
Loading
fact tables
Reading:
BI Textbook Chapter 8
Homework
5 (Due 11/04/2008, Tuesday):
1)
pp287-294,
Learn-by-doing: Building an OLAP Cube
2)
pp303-306,
Learn-by-doing: Working with Measures and Measure groups
3)
pp308-311,
Learn-by-doing: Relating Dimensions in the Cube
These tasks are covered in the class. Please
pay attention to the critical steps in accomplishing the tasks
- Dim Time
definition, specifically the revised primary key.
- The relationship
between dimension tables and the fact tables
Email
your results in a Word file to Zhangxi.lin@hotmail.com
------- + ------ +
------- + ------- + ------- + -------
Lecture 18, 10/23/2008,
Thursday
Topic: Cubism –
Measures and Dimensions (2)
1)
Cubes
and analysis databases
2)
Exercise
8
Reading:
BI Textbook Chapter 8
------- + ------ +
------- + ------- + ------- + -------
Lecture 19,
10/28/2008, Tuesday
Topic: SAS Enterprise Guide
– Creating Summarized Output
1)
Summary
statistics
2)
Tabular
summary formats
3)
SAS
EG graphing
Reading: SAS EG
Chapter 5
------- + ------ +
------- + ------- + ------- + -------
Lecture 20,
10/30/2008, Thursday
Topic: OLAP cube Deployment
Reading:
BI Textbook Chapter 9
------- + ------ +
------- + ------- + ------- + -------
Lecture 21,
11/04/2008, Tuesday
Topic: Additional features
of OLAP cubes
1)
Review
of OLAP cubes construction
2)
SAS
EG Chapter 5
Reading:
BI Textbook Chapter 9
Homework
6 (Due 11/18/2008, Tuesday):
SAS EG Chapter 5 Exercises 1-5
Note: You need to go through the contents of SAS EG Chapter 5 before doing the homework. This part will be useful to your term project
------- + ------ +
------- + ------- + ------- + -------
Lecture 22,
11/06/2008, Thursday
Topic: An introduction to
MDX
Slides, MDX Exercise code – by Yang Yu
Reading:
BI Textbook Chapter 10, 11
------- + ------ +
------- + ------- + ------- + -------
Lecture 23,
11/11/2008, Tuesday
Topic: SAS
Programming
1)
Introduction
2)
Data
step (Preliminary)
------- + ------ +
------- + ------- + ------- + -------
Lecture 24,
11/13/2008, Thursday
Topic: SAS
Programming
1)
Data
Step
2)
SAS
SQL programming
3)
Exercise
10
Reading: PROG I chapter 5-7
Review questions: Test the SAS program
in the shared directory from c02*.sas to c07*.sas
Homework
7 (Due 12/02/2008, Tuesday):
- QuerLog.txt in the
directory ISQS6339\OtherDatasets\Log-data of the shared space contains 163
search engine transaction log records. The following are the tasks:
1)
Write
a short SAS program to read it into a SAS dataset. You need to figure out how
to convert the column of date and time into the numeric format.
2)
Hints:
1.
Use
Enterprise Guide to generate Summary Statistics table that contains the number
of transactions of each user in each day, the start and end time of the user in
the session. See the screenshot below for the parameters.
2.
Then
modify the generated SAS code to calculate the average time each user stayed
for a search.
3)
Screenshot
the print out of the results for submission.
1.
Transpose
the Excel file in the directory \OtherDatasets\6339HW7.xls of the shared space
into the one-row-one-subject format. Compare the results with and without
statement “ID Time;”.
Compare your results with the following.
------- + ------ +
------- + ------- + ------- + -------
------- + ------ +
------- + ------- + ------- + -------
Lecture 25,
11/18/2008, Tuesday
Topic: SAS
Programming
1)
SAS
Data step (2)
2)
SAS
Data Mart
a.
Variable
manipulation
b.
Transposing
c.
SAS
macros
------- + ------ +
------- + ------- + ------- + -------
Lecture 26,
11/20/2008, Thursday
Topic: SAS
Programming
1)
SAS
data mart
Input data
2)
Exercise
11
------- + ------ +
------- + ------- + ------- + -------
Lecture 27,
11/25/2008, Tuesday
Topic: SAS
Programming
1)
Weblog
data processing
2)
PROC
FREQ, MEANS
3)
Case
Study – Building a Customer data Mart
------- + ------ + -------
+ ------- + ------- + -------
Lecture 28,
12/02/2008, Tuesday
Topic: SAS
Programming
1)
Quiz
6
2)
Exercise
12
3)
Overview
of other SAS programming topics
------- + ------ +
------- + ------- + ------- + -------
Final exam, BA 363, 1:30-4:00p, 12/05/2008, Friday
------- + ------ +
------- + ------- + ------- + -------