2002 building a data warehouse
TRANSCRIPT
-
7/31/2019 2002 Building a Data Warehouse
1/30
-
7/31/2019 2002 Building a Data Warehouse
2/30
Introduction of Presenters
Chuck Richardson16 Years in the IT field5 Years DBA experience in Oracle, Sybase and SQL Server1 Years at Santa Fe Community College
Kathy Anderson
26 Years in the IT field15 Years at Central Florida Community College11 Years VSE DB2 experience
1 Year at Santa Fe Community College
KK
-
7/31/2019 2002 Building a Data Warehouse
3/30
Title III Grant
October 2000 - Title III grant awarded to SFCC
SFCCs Five -Year Plan to build the Data Warehouse andDecision Support System is fully supported by:
The SFCC AdministrationCollege and Title III grant funded resources
February 2000 - SFCCs Title III Proposal Goal: Improved AA student performance,persistence and graduation.
KC
-
7/31/2019 2002 Building a Data Warehouse
4/30
Data WarehouseTool Evaluation
Server Operating System
Database Engine
Extract, Transform and Load (ETL)
Business Intelligence (BI)
SFCC spent 6 months evaluating the componentsand tools needed to build a Data Warehouse.
CC
-
7/31/2019 2002 Building a Data Warehouse
5/30
Data WarehouseTool Evaluation
Linux
Windows NT Server
Windows 2000 Server
Windows 2000 Advanced Server
Server Operating Systems considered -
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
CC
-
7/31/2019 2002 Building a Data Warehouse
6/30
Data WarehouseTool Evaluation
Oracle
IBM DB2
Microsoft SQL Server
Database Engines considered -
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
CC
-
7/31/2019 2002 Building a Data Warehouse
7/30
Data WarehouseTool Evaluation
Three separate functions combined into one development tool:
1. Extract Reads data from a specified source and extractsa desired subset of data.
2. Transform Uses rules or lookup tables, or creatingcombinations with other data, to convert sourcedata to the desired state.
3. Load Writes the resulting data to a target database.
Extract, Transform and Load (ETL) Definition
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
CC
-
7/31/2019 2002 Building a Data Warehouse
8/30
Data WarehouseTool Evaluation
IBM DB2 Warehouse Manager
Informatica PowerMart
Microsoft Data Transformation Services (DTS)
Extract, Transform and Load (ETL) Toolsconsidered:
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
CC
-
7/31/2019 2002 Building a Data Warehouse
9/30
Data WarehouseTool Evaluation
Feature InformaticaPowerMart
MicrosoftDTS
IBM DB2Warehouse Manager
Heterogeneous Targets
Multiple, butrequires add-on
Multiple, DB2 ClientConnect requiredfor DB2
Only DB2 withoutData Joiner
Cost Prohibitive Free withSQL Server
Reasonable, butrequired add-ons
Strength of Client Tool
Excellent Excellent Weak
Join Dissimilar Objects
Yes Yes No
Vendor & Third Party Support
No Third PartySupport
Numerous Websites
VirtuallyNon-existent
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
CK
-
7/31/2019 2002 Building a Data Warehouse
10/30
Data WarehouseTool Evaluation
Business Intelligence (BI) Definition -
BI represents a broad category of applications and
technologies for providing access to data to help enterpriseusers make better business decisions.
BI includes:
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
Decision Support Systems ForecastingReporting Data MiningStatistical AnalysisOnline Analytical Processing (OLAP)
KK
-
7/31/2019 2002 Building a Data Warehouse
11/30
Data WarehouseTool Evaluation
Business Intelligence (BI) Tools considered:
Brio
Business Objects
Crystal Decisions
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
KK
-
7/31/2019 2002 Building a Data Warehouse
12/30
Feature Brio CrystalDecisions
Undo feature in Report Design
Rarely available Very strong feature
Quality of report Presentation
Similar to ExcelSpreadsheets
Unlimited formats
OLAP reports Excellent, Need trainingto develop & use Use Crystal AnalysisProfessional
Web reporting presentation
Slow, according toJuly 2001 DM Review
Extremely flexibleWith Crystal Enterprise
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
Data WarehouseTool Evaluation
KK
-
7/31/2019 2002 Building a Data Warehouse
13/30
Data WarehouseTool Evaluation
Server OS
Database Engine
ETL
BI
Windows 2000 Advanced Server
Microsoft SQL Server
Microsoft Data Transformation Services (DTS)
Crystal Decisions
Server Operating SystemDatabase EngineExtract Transform & Load (ETL)Business Intelligence (BI)
Selected by SFCC
KK
-
7/31/2019 2002 Building a Data Warehouse
14/30
Data WarehouseSFCC Sources of Data
IBM VSE VSAM and DB2 operational dataStudent Records Financial AidHuman Resources Finance
State Database submitted dataState Common Course Numbering dataStudent assessment dataStudent surveysNetwork Server logs
College personnel need direct access to decisionsupport information from a variety of data sources:
KK
h
-
7/31/2019 2002 Building a Data Warehouse
15/30
SFCC Data WarehouseData Extract Schedule
Extracted from the VSE mainframe500+ data elements from more than 50 sources
Transformed Six target snapshot tables - All rows contain the
same Snapshot TimestampAdd new and modified data to three history tables
Loaded into the Data Warehouse
Snapshots of student and SDB data are -
The process takes several hours. It is run during theSpring, Summer and Fall terms at -
End of Drop/Add Mid Term End of Term
KK
-
7/31/2019 2002 Building a Data Warehouse
16/30
Data WarehouseStudent Snapshot Data
Consistent Captured at comparable times each termData will not be updated
FlexibleAbility to handle information needs not yet defined
Easy to accessData is in a single databaseDenormalized, redundant data fewer tables to join
ExpandableNew data elements may be added in the future
The Data Warehouse snapshot data is designed to be:
KC
-
7/31/2019 2002 Building a Data Warehouse
17/30
Data WarehouseSFCC Servers
Both servers currently contain:2 Gig Memory100 Gig StorageDual Processors
Future expansion includes:Four ProcessorsStorage Array
Two Dell PowerEdge 6400 servers1 server for Development1 server for Production
CC
-
7/31/2019 2002 Building a Data Warehouse
18/30
Data WarehouseSFCC 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
-
7/31/2019 2002 Building a Data Warehouse
19/30CC
D W h
-
7/31/2019 2002 Building a Data Warehouse
20/30
Data WarehouseData Transformation Services
CC
-
7/31/2019 2002 Building a Data Warehouse
21/30
-
7/31/2019 2002 Building a Data Warehouse
22/30
Data WarehouseBI Tool Crystal Decisions
Crystal ReportsCreate compelling views of data without relying on IT
Crystal AnalysisOLAP reporting with multi-dimensional cubes
Report Development -
Web Delivery -
Crystal Enterprise Web-based delivery of information via ePortfolioFull report drill-down capabilities
K
-
7/31/2019 2002 Building a Data Warehouse
23/30
Data WarehouseCrystal Reports
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
SFCC Crystal Reports Developers -
KK
-
7/31/2019 2002 Building a Data Warehouse
24/30
C t l R t C T b d Ch t
-
7/31/2019 2002 Building a Data Warehouse
25/30
Crystal Report
KK
Cross-Tab and Chart
C t l R t World Map
-
7/31/2019 2002 Building a Data Warehouse
26/30
Crystal Report
KK
World Map
Cr stal Report Report Drill Down
-
7/31/2019 2002 Building a Data Warehouse
27/30
Crystal Report
K
Report Drill-Down
-
7/31/2019 2002 Building a Data Warehouse
28/30
Data WarehouseBI Tool Crystal Decisions
Crystal Management ConsoleUser, content and server management
ePortfolioA web interface for end users to view, schedule, and
monitor published reportsUser authentication LDAP, NT or Enterprise
Report Application Server (RAS)Allows users to modify reports
Crystal Enterprise includes:
K
C l E i P f li
-
7/31/2019 2002 Building a Data Warehouse
29/30
Crystal Enterprise ePortfolio
KK
-
7/31/2019 2002 Building a Data Warehouse
30/30
SFCC Data Warehouseand Decision Support System
Automatic scheduling of reportsBuild OLAP cubes with Microsoft Analysis Service
Develop Crystal Analysis ReportsDaily ETL of Enrollment and Financial dataUpgrade to Crystal 9.0
Future Development Plans include:
KK