ISQS 6339 Lecture notes

ISQS 6339 - Data Management & Business Intelligence

Instructor: Zhangxi Lin

======================================================

Group Sign-up | View Groups | Projects

======================================================

Stage 1: Introduction to Business Intelligence

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

Lecture 1, 8/28/2007, Tuesday

Topic: Introduction

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

Lecture 2, 8/30/2007, Thursday

Topic: Business Intelligence Architecture and Methodology (I)

Meeting with Tim Lu
BI trend
BI Dimensional Lifecycle

Review questions:

What is BI?
Why is BI getting hot?
Explain the business dimensional lifecycle model

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

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

Lecture 3, 9/4/2007, Tuesday

Topic: Business Intelligence Architecture and Methodology (II)

1. BI Concepts

2. IT Project Management

3. BI Methodology

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

Homework 1:

Write an essay of 2-3 pages to answer the following question using the supporting materials found from the Internet. References must be included:

1) What are main BI tasks?

2) How do SAS, IBM, Microsoft, and Oracle deal with these tasks with their products?

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

Lecture 4, 9/6/2007, Thursday

Topic: BI Requirements – Case Study

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?

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

Stage 2: Principles of Data Warehousing

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

Lecture 5, 9/11/2007, Tuesday

Topic: Dimensional modeling for BI

1) Quiz 1

2) Data warehousing architecture

3) Dimensional modeling for BI

4) Exercise 2 – Getting familiar with database engine

Preview/review questions:

1) What is “dimensions” in the context of data warehousing?

2) What is “Slowly changing dimensions”? What are “the Type 1 change” and “the Type 2 change”?

3) What is “conformed dimension”?

4) What is “drill-across”?

5) What is “surrogate keys”? How does it work?

6) How to degenerate dimensions?

7) What is “snowflake” approach?

8) Why do we need many-to-many relationship for dimensional modeling? How to implement it?

Homework 2:

Use the datasets in Excel file Commrex_db0 in the shared directory \\bam131.ba.ttu.edu\ISQS6339 to

1) Design an E-R diagram

2) Create a database with SQL Server Database Engine which contains at least two tables: a User table and a Real Estate Property table

3) Manually add a few records to the two tables.

4) Design the dimensional model for data warehouse

5) Identify the key issues for constructing the data warehouse

For more information, check http://www.commrex.com to understand the background of the homework assignment.

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

Lecture 6, 9/13/2007, Thursday

Topic: Dimensional modeling for BI (II)

1) Exercise 2 (continued)

1) Case study – Commrex data warehousing (Reference: Zhangxi Lin, Gary Anderson and TJ Anderson, "Enabling Real Estate Businesses on the Web: From E-Business Model to The Application Services," IEEE IT Professional, Jan/Feb, 2004, 44-49.)

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

Lecture 7, 9/18/2007, Tuesday

Topic: Dimensional modeling for BI (III)

1) Exercises 2:

a. Importing data to the database

b. Defining data source definition, data source views, cubes.

2) Review of dimensional modeling

3) BI Toolset

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

Lecture 8, 9/20/2007, Thursday

Topic: Extraction, Transformation, and Loading (I)

1) Exercise 3:

2) Outline of Chapter 4

3) An introduction to ETL system

Homework 3:

1) Check the upgraded dataset Commrex_db2.xls (or Commrex_db2a.xls, a much smaller dataset). Refine the previously developed dimensional model, with the following considerations:

a. What are analytic themes

b. How to use a single fact table for three different property types

c. How to handle the updates in the transaction log (or member) table as the Type 2 SCDs to create necessary dimensional tables

2) Create fact and dimension tables according to the above design

3) Create data source views and cubes

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

Lecture 9, 9/25/2007, Tuesday

Topic: Extraction, Transformation, and Loading (II)

1) Quiz 2 (dimensional modeling)

2) Designing the ETL system

3) SSIS hand-on tasks

Review questions and exercises:

1) Why do we need ETL?

2) What is the relationship between control flow and data flow?

3) Go through SSIS tutorial lesion 1.

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

Lecture 10, 9/27/2007, Thursday

Topic: Extraction, Transformation, and Loading (III)

1) SSIS hand-on tasks – Container

2) Exercise 4

Homework 4:

Based on the results from homework 3 using SSIS to:

1) Design a high-level map for an ETL project. Here you can assume: (1) Member data and property listing tables are of Type 2 SCDs. (2) Data are from sources in Excel files.

2) Conceive an ETL project with one control flow and at least two data flows to perform member information updates from two separate files.

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

Lecture 11, 10/2/2007, Tuesday

Topic: Developing the ETL System

1) Principles of ETL system design

2) Exercise 5 - Implementing Type 2 SCDs dimensions

3) About Project 1

Review questions:

1) Review the process of Type 2 SCD implementation

2) Try any one of fact processing tasks

3) Start the trials of the skills you learned for the identified technical components for Project 1

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

Lecture 12, 10/4/2007, Thursday

Topic: SSAS OLAP Database

1) Introduction to SSAS

2) Designing OLAP

3) Exercise 6 – Designing OLAP

4) SAS Enterprise Guide (Chapter 2)

Self-taught & Exercise: SAS EG Chapter 2

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

Lecture 13, 10/09/2007, Tuesday

Topic: SSAS OLAP Database

1) Quiz 3 (ETL)

2) SAS Enterprise Guide (Chapter 3)

3) Exploring SSAS for Cube Design

Self-taught & Exercise: SAS EG Chapter 3

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

Lecture 14, 10/11/2007, Thursday

Topic: BI Applications

1) Project 1 discussion – ETL implementation

Self-taught & Exercise: SAS EG Chapter 4

Optional ETL Exercises:

You are encouraged to try some of important functions of SSIS at this stage and share the knowledge with your classmates. Here are three pieces of nuts for you to crack:

How to use Merge function of Data Transformation to merge variety dataset in two tables as I showed in the class.
How to remove duplicated rows in the table. For example, we can extract variety data from the “data” table of the cotton Excel file. However, the rows from the table are duplicated. Try how to remove the duplications
How to detect the changes of the rows in the data sources and extract the updated rows into a table in the data warehouse.
If the above problems have been taken by others you may find a new one and inform the instructor.

You can form a group of two to do the problem. Once you have chosen the problem you can simply reply this email to let others know. The deadline is Monday Oct 15 before 5p (Now it is extended to Oct 19 5p).

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

Lecture 15, 10/16/2007, Tuesday

Topic: SSRS

1) BI Applications development methodology

2) Introduction to SSRS

3) Exercise 7 – basics of SSRS

4) SAS Enterprise Guide (Chapter 4)

Review:

1) BI application methodology

2) Basic uses of SSRS

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

Lecture 16, 10/18/2007, Thursday

Topic: SSRS

1) SSRS Tutorial: Adding Grouping, Sorting, and Formatting to a Basic Report

2) Exercise 8 – Query with SSRS

3) SAS Enterprise Guide (Chapter 5)

Homework 5 (due Oct 25):

SQL Server 2005 Books Online - Tutorial: Using a Dynamic Query in a Report . Deliverable: the printout of the results.

Self-taught & Exercise: SAS EG Chapter 5

Optional Reporting Services Exercises:

1) Tutorial: Creating a Data-Driven Subscription

2) Tutorial: Generating RDL Using the .NET Framework

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

Lecture 17, 10/23/2007, Tuesday

Topic: Project 1 Presentation – ETL task implementation

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

Lecture 18, 10/25/2007, Thursday

Topic: BI Applications

1) SAS EG Chapter 3-4

2) Cube deployment

3) OLAP with SAS EG

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

Lecture 19, 10/30/2007, Tuesday

Topic: SAS Enterprise Guide Based BI Applications

1) Using parameters in SAS Enterprise Guide Query (Chapter 3)

2) Graphing (Chapter 5)

3) Parameterized queries (Chapter 6)

4) Exercise

5) Project 2 orientation

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

Lecture 20, 11/1/2007, Thursday

Topic: Project discussion

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

Stage 3: Data Preparation for Analytics with SAS

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

Lecture 21, 11/6/2007, Tuesday

Topic: Data Preparation for Analytics – Introduction

1) Introduction to data preparation for analytics

2) SAS Programming with Base SAS (GS Ch 1-2， SAS-Prog 1-2)

3) SAS Programming with SAS Enterprise Guide 4.0 (Listing, Proc Freq, Proc Contents)

4) Project 2 introduction (the website of Federal Reserve Bank of Chicago)