Building a Data Warehouse - PowerPoint PPT Presentation

1 / 30
About This Presentation
Title:

Building a Data Warehouse

Description:

Microsoft Data Transformation Services (DTS) Extract, Transform and Load (ETL) Tools considered: ... Web-based delivery of information via ePortfolio. Full ... – PowerPoint PPT presentation

Number of Views:53
Avg rating:3.0/5.0
Slides: 31
Provided by: fae6
Category:

less

Transcript and Presenter's Notes

Title: Building a Data Warehouse


1
Building a Data Warehouse
  • By Chuck Richardson Kathy Anderson

Learn how Santa Fe Community College selected the
Extract, Transformation and Load (ETL) and
Business Intelligence (BI) tools for the
Colleges new enterprise Decision Support System.
In addition to data selection and modeling for
the Data Warehouse, this presentation will take
you from developing Microsofts SQL Server data
transformations to designing Crystal Decisions
dynamic reports for Web-based distribution.
2
Introduction of Presenters
  • Chuck Richardson16 Years in the IT field 5
    Years DBA experience in Oracle, Sybase and SQL
    Server 1 ½ Years at Santa Fe Community College
  • Kathy Anderson26 Years in the IT field15 Years
    at Central Florida Community College11 Years VSE
    DB2 experience 1 Year at Santa Fe Community
    College

KK
3
Title III Grant
  • February 2000 - SFCCs Title III Proposal
    Goal Improved AA student performance,
    persistence and graduation.
  • October 2000 - Title III grant awarded to SFCC
  • SFCCs Five-Year Plan to build the Data Warehouse
    and Decision Support System is fully supported
    by ? The SFCC Administration ? College and
    Title III grant funded resources

KC
4
Data Warehouse Tool Evaluation
SFCC spent 6 months evaluating the components and
tools needed to build a Data Warehouse.
  • Server Operating System
  • Database Engine
  • Extract, Transform and Load (ETL)
  • Business Intelligence (BI)

CC
5
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
Server Operating Systems considered -
  • Linux
  • Windows NT Server
  • Windows 2000 Server
  • Windows 2000 Advanced Server

CC
6
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
Database Engines considered -
  • Oracle
  • IBM DB2
  • Microsoft SQL Server

CC
7
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
Extract, Transform and Load (ETL) Definition
  • Three separate functions combined into one
    development tool
  • Extract Reads data from a specified source and
    extracts a desired subset of data.
  • Transform Uses rules or lookup tables, or
    creating combinations with other data, to
    convert source data to the desired state.
  • Load Writes the resulting data to a target
    database.

CC
8
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
Extract, Transform and Load (ETL) Tools
considered
  • IBM DB2 Warehouse Manager
  • Informatica PowerMart
  • Microsoft Data Transformation Services (DTS)

CC
9
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
CK
10
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
  • Business Intelligence (BI) Definition -

BI represents a broad category of applications
and technologies for providing access to data to
help enterprise users make better business
decisions. BI includes
  • Decision Support Systems Forecasting
  • Reporting Data Mining
  • Statistical Analysis
  • Online Analytical Processing (OLAP)

KK
11
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
  • Business Intelligence (BI) Tools considered
  • Brio
  • Business Objects
  • Crystal Decisions

KK
12
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
KK
13
Data Warehouse Tool Evaluation
Server Operating System Database Engine Extract
Transform Load (ETL) Business Intelligence (BI)
Selected by SFCC
  • Server OS

Windows 2000 Advanced Server
Database Engine
Microsoft SQL Server
ETL
Microsoft Data Transformation Services (DTS)
BI
Crystal Decisions
KK
14
Data Warehouse SFCC Sources of Data
College personnel need direct access to decision
support information from a variety of data
sources
  • IBM VSE VSAM and DB2 operational data Student
    Records Financial Aid Human Resources Finance
  • State Database submitted data
  • State Common Course Numbering data
  • Student assessment data
  • Student surveys
  • Network Server logs

KK
15
SFCC Data Warehouse Data Extract Schedule
Snapshots of student and SDB data are -
  • Extracted from the VSE mainframe 500 data
    elements from more than 50 sources
  • Transformed Six target snapshot tables -
    All rows contain the same Snapshot Timestamp
    Add new and modified data to three history
    tables
  • Loaded into the Data Warehouse

The process takes several hours. It is run
during the Spring, Summer and Fall terms at -
End of Drop/Add
Mid Term
End of Term
KK
16
Data WarehouseStudent Snapshot Data
The Data Warehouse snapshot data is designed to
be
  • Consistent Captured at comparable times each
    term Data will not be updated
  • Flexible Ability to handle information needs not
    yet defined
  • Easy to access Data is in a single
    database Denormalized, redundant data fewer
    tables to join
  • Expandable New data elements may be added in the
    future

KC
17
Data Warehouse SFCC Servers
  • Two Dell PowerEdge 6400 servers 1 server for
    Development 1 server for Production
  • Both servers currently contain 2 Gig Memory
    100 Gig Storage Dual Processors
  • Future expansion includes Four
    Processors Storage Array

CC
18
Data Warehouse SFCC Server Components
  • IBM HTTP Server
  • SQL Server 2000 / DTS / Analysis Services
  • DB2 Connect / CM2 / ODBC
  • FTP Server
  • E-mail notification via Collaborative Data
    Objects (CDO)

CC
19
CC
20
Data Warehouse Data Transformation Services
CC
21
Data Warehouse Data Transformation Services
C?
22
Data WarehouseBI Tool Crystal Decisions
Report Development -
  • Crystal Reports Create compelling views of data
    without relying on IT
  • Crystal Analysis OLAP reporting with
    multi-dimensional cubes

Web Delivery -
  • Crystal Enterprise Web-based delivery of
    information via ePortfolio Full report
    drill-down capabilities

?K
23
Data WarehouseCrystal Reports
SFCC Crystal Reports Developers -
  • 25 Crystal Reports licenses
  • 1 developer per Division within the college
  • Commitment, Aptitude
  • Job Description changed to include report
    development
  • On-site 2-day Crystal Report Design training
    classes
  • Camp Crystal training
  • ODBC connection to Data Warehouse
  • SFCC Crystal Users Group
  • Publish reports to Crystal Enterprise

KK
24
Crystal Report
Cross-Tab and Chart
KK
25
Crystal Report
Cross-Tab and Chart
KK
26
Crystal Report
World Map
KK
27
Crystal Report
Report Drill-Down
K?
28
Data WarehouseBI Tool Crystal Decisions
Crystal Enterprise includes
  • Crystal Management Console User, content and
    server management
  • ePortfolio A web interface for end users to
    view, schedule, and monitor published
    reports User authentication LDAP, NT or
    Enterprise
  • Report Application Server (RAS) Allows users to
    modify reports

?K
29
Crystal Enterprise ePortfolio
KK
30
SFCC Data Warehouseand Decision Support System
Future Development Plans include
  • Automatic scheduling of reports
  • Build OLAP cubes with Microsoft Analysis Service
  • Develop Crystal Analysis Reports
  • Daily ETL of Enrollment and Financial data
  • Upgrade to Crystal 9.0

Questions? chuck.richardson_at_sfcc.edu
kathy.anderson_at_sfcc.edu
KK
Write a Comment
User Comments (0)
About PowerShow.com