Business intelligence

fantasicgilamonsterData Management

Nov 20, 2013 (3 years and 4 months ago)

92 views



UW Certificate Program in

Business Intelligence





Business Intelligence: Project Planning, Analysis & Design

Spring Term, 2007



Course Objectives

This course focuses on how to design and build a Business Intelligence solution.


We will introduce an
d compare various BI technologies across key BI Platform, BI Pure Play, and ERP
vendors.


You will also learn how to design and build a data warehouse within the context of student BI projects.
Students can develop their own projects within collaborative
teams or be assigned an existing data source
to develop a project. To ensure success during the implementation phase in course 3, students will plan
for and gather business requirements, as well as design the data warehouse in order to develop an
effective

BI plan.


Other topics include:



Defining the BI project



Scoping the project down



Asking the right questions



Data modeling



Analysis of reports & metrics



Gathering business requirements



Project analysis



Defining needed dimensional measures



Warehou
se design



Sourcing the data and source mapping to the target location

___________________________________________________________________





Required Textbook
(s)



Data Warehouse
Lifecycle
Toolkit



Microsoft Data Warehouse Toolkit






Instructor

Elizabeth

Vitt

evitt@intellimentum.com





Assessment Criteria


Students will be assessed based on class participation and project work.


Final class deliverables include




Project Planning

o

Prioritization Matrix

o

Proj
ect Plan

o

Team Plan

o

Biz Requirements

o

Issues List (if necessary)




Data Deliverables

o

Bus Matrix

o

Data Element Inventory

o

Data Profiling Results

o

Data Model (3
rd

Iteration)

o

Source to Target Mapping




Technical Functional Specs

o

Overall Architecture

o

ETL Function
al Spec

o

OLAP Functional Spec

o

Data Mining Functional Spec

o

Reporting Functional Spec



Students will
implement projects in Course 3
.

* READING Leg
end

DWTK = Data Warehouse
Lifecycle Toolkit


MSTK = Microsoft Data Warehouse Toolkit



Custom =

Custom Course Project Con
tent Available in Student Files



WK

CLASS
MTG

FOCUS

TOPICS

READING


*


ASSIGNMENT


/ DELIVERABLES

-

Current week assignment organized
by Folder

Number

1

1/07

BI Strategy





Course Overview



Kimball Lifecycle
Methodology



BI / DW Architecture Framework



Enterprise vs.
Departmental BI



Dimensional Modeling Process
Review



Business Process / Analytic Themes



Bus Matrix



Prioritization

Matrix

o

Business
Value

o

Feasib
ility




Project Introduction



General
B
I/
DW
Concepts

o

MSTK

Introduction

o

MSTK
Ch

1:
Biz

Reqs
pg. 3


21

o

DWTK
Ch

3:
Project Planning
pg 42


63




Adventure Works
Project Info

o

MSTK

Ch

1:
Company
Overview
pg. 21


37

o

Custom
-

Original
AW
Interview Summaries


o

Custom
-

New
AW
Interview

Summaries

o

Custom
-

New AW Organizational Chart

o

Custom
-

AW Data Source Information
(File
Layout and Example Data)

o

Custom


AW Example Reports




Prepartion
Reading for 1/14 Class
Mtg

o

MSTK

Ch

2:
Dim Model
Basics
pg. 39


49

o

MS
TK
Ch

2:
Dim Model
Process
pg. 72


91

o

DWTK
Ch

3:
Project Planning
pg. 64


94

o

DWTK
Ch

4:
Biz Reqs
pg. 95


136

o

DWTK Ch 7: Building Dim Models
(265


312
)

Assignment Details Provided on 1/07



01

-

Environment Set Up

o

Restore a New OLTP



02



Adventure Work
s
Synthesis
Questions

o

Assignment List




03


Adventure Works
Deliverables


o

AW
Analytic Themes
&
Processes

o

AW
Bus Matrix

o

AW Prioritization Matrix

o

Issue
/ Question
List

2

1/14

Project Planning
and


Business
Requirements




Project Planning
and Scoping



Iter
ative Development



Implementation Team



Risk Management



Architecture Planning



Technology Selection



Business Requirements Gathering



Data Profiling



Data Element Inventory



BI
Requirements Document



Logical / Physical Dimensional
Modeling Review

o

Fact
Table


o

Dim

Table



Source to Target Mapping



Adventure Works Project Info

o

Detailed Interview Summaries

o

Original Data Mart Review




Prepartion
Reading for 1/2
8

Class Mtg

o

MSTK

Ch

2
:

Dim Model
pg. 49


72

o

DWTK Ch 5
:
Intro
Dim Modeling
(139


216
)

o

DWTK Ch 6
:
Grad
Dim Modeling
(217
-

263)




Additional
Preparation
Reading for 1/28 Class Mtg

Kimball Online
-

Design Tips
:

o

T
ip
25

-

Dimensional Models for Parent Child
Applications

o

Tip
37

-

Modeling a Pipeli
ne with Accumulating
Snapshots

o

Tip
42

-

Combining Periodic and Accumulating
Sna
pshots

o

Tip
46

-

Another Look at Degenerate Dimensions

o

Tip
86

-

Reference Dimensions for Infrequently
-
Accessed Degenerates

o

Tip
95

-

Patterns to Avoid when Modeling
Header/Line It
em Transaction

Assignment Details Provided
on
1/14



Business
Requirements Summary



Existing
Data Mart Review



Interview Questionnaire Review



Team
Evaluation
Review



Project Plan Review



Data Profiling Reports



Data Element Inventory



Source To Target Mappin
g



Iteration 1 Data Model



As Necessary

o

Updated Bus Matrix

o

Issues Questions List


* READING Legend

DWTK = Data Warehouse
Lifecycle Toolkit


MSTK = Microsoft Data Warehouse Toolkit



Custom
=
Custom Course Project Con
tent Available in Student Files


WK

CLASS
MTG

FOCUS

TOPICS

READING

*


ASSIGNMENT


-

Current week assignment organized by Folder
Number

3

1/28

Dimensional
Modeling



Iteration 2



Dimension Design Techniq
ues


o

Heterogeneous

o

Date / Time

o

Degenerate

o

Role Playing

o

Snowflake vs. Star

o

Recursive



Fact Ta
ble Design
Techniques

o

Allocations

o

Granularity

o

When to Use
Different
Fact Table Types

o

Types of Measures

o

Workflow
Calculations



Techniques to
Validate the Desi
gn



Prepartion
Reading for 2/04 Class Mtg

o

MSTK
Ch

3
:

Toolset
(sections TBD)

o

MSTK
Ch

4
:

Setup and Phys
ical

Design
(sections
TBD)

o

DWTK Ch 8
:

DW Arch (317


334)

o

DWTK Ch 13
:

Arch Plan (449


499)


Assignment Details Provided on 1/28



Iteration

2

of the Model

o

Major Design Push



V2
Source To Target Mapping



As Necessary

o

Updated Bus Matrix

o

Updated Biz Reqs Doc

o

Issues Questions List

4

2/04

Dimensional
Modeling



Iteration 3

and

Architecture
Planning

Continuation of
topics from the prior
week
plus
the followin
g:



Many
-
to
-
Many



Junk



Aggregate Dimensions



Audit Dimensions



Reference Dimensions



Usage of
Outrigger Tables



Slowly Changing Dimensions



Physical Design Considerations



Prepartion
Reading for 2/11 Class Mtg

o

MSTK
Ch

5
:

Design
ETL System

o

MSTK Ch 6
:

D
evelop
ETL System (sections TBD

o

DWTK Ch 9
Back Room Arch
(335
-

372)


o

DWTK Ch 16 Data Staging (609


661)

Assignment Details Provided on 2/04



Iteration 3

of the Model

o

Many
-
to
-
Many

o

Outrigger table

o

SCD

Support



V3
Source To Target Mapping



As Necessary

o

Updated Bu
s Matrix

o

Updated Biz Reqs
Doc

o

Updated
Issues List

5

2/11

Extract
Transform and
Load (ETL)



Data Quality Assessments



Data Loading Techniques



Detecting Data Changes



Historical vs. Incremental Data
Loads



Data Load
Auditing



Late Arriving Attributes



Comm
on
ETL Challenges



ETL Evaluation Criteria



Prepartion
Reading for 2/25 Class Mtg

o

MSTK

Ch

7
:
OLAP
and SSAS

(sections TBD)

o

DWTK Ch 14
Aggregates
(543


567)

Assignment Details Provided on 2/11



ETL
Functional Spec



ETL
Prototype



As Necessary

o

Updated Biz Reqs
Doc

o

Updated Issues List


* READING Legend

DWTK = Data Warehouse
Lifecycle Toolkit


MSTK = Microsoft Data Warehouse Toolkit



Custom
=
Custom Course Project Con
tent Available in Student Files


WK

CLASS
MTG

FOCUS

TOPICS

READING

*


ASSIGNMENT


-

Current week assignment organized by Folder
Number

6

2/25

Online
Analytical
Processing
(OLAP)



Dimension
&
Cube Design
Principles



Data Aggregation & Storage Modes



Cross Fact T
able
Analysis



Loading
Data At Different Levels of
Detail



Advanced
Calculations



Time Analysis



KPIs



Drill
-
through



OLAP
Data Refresh Strategies



OLAP
Evaluation Criteria



Prepartion
Reading for 3/03 Class Mtg

o

MSTK Ch 8
:
BI Apps

o

MSFT Ch 9
: SS
RS (sections
TBD)

o

DWTK Ch 10
:

Front Room Arch (373


409)

o

DWTK Ch 17
:

Building End User Apps (665


686)

Assignment Details Provided on 2/25



OLAP Functional Spec



OLAP Prototype



As Necessary

o

Updated Biz Reqs Doc

o

Updated Issues List


7

3/
0
3

Reporting



User Community Se
gmentation



Report
Design
&
Delivery Paradigms



Summary vs. Drill
-
through Reports



Parameters / Filters



Data Visualization



BI Portals



Reporting
Evaluation Criteria



Prepartion
Reading for 3/10 Class Mtg

o

MSTK Ch 10
:

Data Mining

o

MSTK Ch 17
:
Real Time BI (s
ections TBD)


Assignment Details Provided on 3/03



Reporting Functional Spec



Reporting Prototype



As Necessary

o

Updated Biz Reqs Doc

o

Updated Issues List

8

3/10

Advanced
Analytics



Data Mining
Algorithms



Decision Trees



Naïve Bayes



Clustering



Sequence Cluste
ring



Time Series



Association



Neural Network



Data Mining Development Process



Real Time

BI

(time permitting)



Tech
Evaluation Criteria



Prepartion
Reading for 3/17 Class Mtg

o

MSTK Ch 12
:
Security

o

MSTK Ch 13
:
Metadata

o

MSTK Ch 18
:
Present and Future

o

DWTK C
h 11:
Metadata 435


447





Optional
Reading

o

DW Maturity
from TDWI

Assignment Details Provided on 3/10



Data Mining Functional Spec



Data Mining Prototype



As Necessary

o

Updated Biz Reqs Doc

o

Updated
Issues List


* READING Legend

DWTK = Data Warehouse
Lifecycle Toolkit


MSTK = Microsoft Data Warehouse Toolkit



Custom
=
Custom Course Project Con
tent Available in Student Files



WK

CLASS
MTG

FOCUS

TOPICS

READING

*


ASSIGNMENT


-

Current week assignment organized by Folder
Number

9

3/17

Finalizing
the
Project



Report
Security




Data Security



Metadata
Management




Designing Subsequent Data Marts



BI Maturit
y Models Revis
ited



Final Technical Evaluations


Not Applicable

Final Course Deliverables




Project Planning

o

Prioritization Matrix

o

Project
Plan

o

Team Plan

o

Biz Requirements
Doc

o

Issues List (if necessary)



Data Deliverables

o

Bus Matrix

o

D
ata

Element Inventory

o

Data Profiling Results

o

Data Model (3
rd

Iteration)

o

Source to Target Mapping



Technical Functional Specs

o

Overall
Architecture
Spec

o

ETL Functional Spec

o

OLAP Functional Spec

o

Reporting Functional Spec

o

Data Mining Functional Spec