ISQS 3358 Lecture
Notes
Instructor:
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
------- + ------ +
------- + ------- + ------- + -------
Lecture 1,
01/09/2008, Wednesday
Topic: Introduction
1) Basic BI Concepts
2) BI trend
Review questions:
1)
What
is BI?
2)
Why
is BI getting hot?
------- + ------ +
------- + ------- + ------- + -------
Lecture 2,
01/14/2008, Monday
Topic: Anatomy of
Business Intelligence
1) Concepts
2) Applications
3) Methodology
Terminology: data, information,
knowledge, business intelligence,
data warehouse, meta data, ETL, business rules, OLAP
Review questions:
Explain the business dimensional lifecycle
model
------- + ------ +
------- + ------- + ------- + -------
Lecture 3,
01/16/2008, Wednesday
Topic: Data
Warehousing with SQL Server 2005
1) Data warehousing
process
2) Dimensional modeling
3) Developing data
warehouse
Homework
1 (Due 01/28/2008, Monday):
Write
an essay of 2 pages to answer the following questions using the supporting
materials found from the Internet. References must be included.
1)
What
are main BI tasks?
2)
Find
a BI application case. Explain the goal, measure, and consequences of the BI
project.
------- + ------ +
------- + ------- + ------- + -------
Lecture 4,
01/23/2008, Wednesday
Topic: Unified
Dimensional Model
1)
Business
information flow model
2)
Case
study – Adventure Works Cycles
3)
Exercise
1 – Getting start with Microsoft SQL Server 2005
Review questions:
1)
What
is the relationship between BI program and IT project management?
2)
How
to conduct business requirements analysis for the BI program?
3)
Can
you explain the main features of Microsoft SQL Server 2005 for BI projects?
Homework
2 (Due 02/11/2008, Monday):
Define
a cube using the tables in MaxMinManufacturingDM data mart (The tables are in
your database “ISQS3358-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 5,
01/28/2008, Monday
Topic: Dimensional
Modeling
1)
Quiz
1
2)
Data
warehousing methodology
------- + ------ +
------- + ------- + ------- + -------
Lecture 6,
01/30/2008, Wednesday
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
------- + ------ + ------- + ------- + -------
+ -------
Lecture 7,
02/04/2008, Monday
Topic: Creating and
populating data mart with SSIS
1)
Completing
Exercise 2
2)
An
introduction to SSIS
3)
Trying
out an ETL project with SSIS
------- + ------ +
------- + ------- + ------- + -------
Lecture 8,
02/06/2008, Wednesday
Topic: Review
1)
Review
of Chapter 4-6 (SSMS, SSAS) for Quiz 2
2)
Discussion
on the improvement of the course
3)
Exercise
3 – Creating a cube from a data mart
Reading:
Chapter 6, 7
------- + ------ +
------- + ------- + ------- + -------
Lecture 9,
02/11/2008, Monday
Topic: ETL system
development (1)
1)
Quiz
2
2)
Look
closer into dimensional modeling
3)
Term
project
4)
Overview
of Chapter 7
Homework
3 (Optional for Extra Credits, Due 03/03/2008, Monday):
The
following assignments are optional. Students who fulfill any of the following
questions will receive extra credits.
1.
In
the shared directory \\basrv1.ba.ttu.edu\ISQS3358\SSIS\, find “SSIS PROJECT -
UPDATING DATABASE.
2.
The
datasets in Excel file Commrex_3358.xls in the shared directory
\\basrv1.ba.ttu.edu\ISQS3358 has a new table “transactions”. Tasks:
1)
Based
on previous Commrex_3358 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 10,
02/13/2008, Wednesday
Topic: ETL system
development (2)
1)
Exercise
4 – Populating Maximum Miniatures Manufacturing Data Mart
2)
Control
flow tasks
3)
Data
flow items
------- + ------ +
------- + ------- + ------- + -------
Lecture 11,
02/18/2008, Monday
Topic: ETL system
development (3)
1)
Populating
MaxMinManufacturingDM data mart
2)
ETL
application debugging
Reference: SQL
Server 2005 Integration Services, McGraw Hill Osborne, 2007
------- + ------ +
------- + ------- + ------- + -------
Lecture 12,
02/20/2008, Wednesday
Topic: Advanced ETL
topics
1)
Extending
ETL skills
2)
Illustrative
example: Updating database
3)
Exercise
5 – Exploring features of ETL tasks
Reference: SQL
Server 2005 Integration Services, McGraw Hill Osborne, 2007
------- + ------ +
------- + ------- + ------- + -------
Lecture 13,
02/25/2008, Monday
Topic: Cubism –
Measures and Dimensions
1)
Quiz
3
2)
Exercise
6 – Loading fact tables
Homework
4 (Due 03/10/2008, Monday):
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
Email
your results in a Word file to Zhangxi.lin@hotmail.com
------- + ------ +
------- + ------- + ------- + -------
Lecture 14,
02/27/2008, Wednesday
Topic: Cubism – Measures
and Dimensions
1)
Creating
a cube
2)
Measures
and dimensions in the cube
------- + ------ +
------- + ------- + ------- + -------
Lecture 15,
03/03/2008, Monday
Topic: Getting
Started with SAS
1)
SQL
Server 2005 data warehousing Q&A
2)
Getting
Started SAS
3)
Working
with Data in a Project
------- + ------ +
------- + ------- + ------- + -------
Lecture 16,
03/05/2008, Wednesday
Topic: Cubism –
Measures and Dimensions
1)
SAS
EG Tasks (Lecture 2)
2)
Exercise
7 – Getting Started with SAS
------- + ------ +
------- + ------- + ------- + -------
Lecture 17,
03/10/2008, Monday
Topic: Deploying a
cube
1)
Quiz
4
2)
Loading
MaxMinSalesDM
3)
Deploying
the MaxMinSales Cube