Transcript Building a Data Warehouse from Colleague

Jeremy Brinkman
Director of Administrative Systems
University of Northwestern Ohio
[email protected]
Great Lakes Users’ Group Conference
August 10-11, 2009








Reporting Evolution
Why a Data Warehouse?
Planning
Technologies Used
Design
Implementation
Challenges
Resources

Legacy ERP System

Migration to Datatel Colleague / SQL

Colleague and the Data Warehouse
◦ (1981-2005)
◦ Transactional reporting
◦ (2005-Current)
◦ Transactional reporting
◦ Snapshot Reporting
◦
◦
◦
◦
◦
(2007-Current)
Transactional reporting
Snapshot reporting
Point-in-time data reporting
Ad-hoc reporting

Departments can produce their own reports

Capture point-in-time data


Reduce performance hit on transactional database server
Simplify reporting

Combine disparate data sources
◦ Empowers end users
◦ Reduces the burden on IT
◦ Daily financial standing
◦ Daily financial aid award packaging status
◦ Active student programs in a term
◦ SQL version of the Colleague database has 3,000+ tables!
“A lot of times, people don't know what they want until you show it to
them.” – Steve Jobs in BusinessWeek, May 25 1998

Meet with the departments with the most ad-hoc
data requests
◦ Financial Aid
◦ Admissions
◦ Registration and Advising



Review existing reports for KPIs
Involve the decision makers
Leverage your tech-savvy users to help drive
adoption








Star vs. Snowflake Schema
Fact and Dimension tables
Metadata
Conformed Dimensions
Type One, Two, and Three Slowly Changing
Dimensions
Measures
Key Performance Indicators (KPIs)
Inmon Model vs. Kimball Model



Datatel Colleague Release 18
SQL Server 2005
Business Intelligence Development Studio (BIDS)
◦ To develop the data load processes

SQL Server Integration Services (SSIS)
◦ To move data from Colleague to the warehouse
◦ Datatel Data Orchestrator ODS
 We started before ODS was an option, so we kept SSIS.
 ODS is a nice tool to get the data from Colleague into SQL, especially for UniData shops



SQL Server Management Studio (SSMS)
Business Objects Enterprise XI R2
Web Intelligence (WEBI)
◦ Ad-hoc report development

Data Modeling Software
◦ ERWin
◦ Power Architect






Uppercase naming in the database
Yes/No fields end in _IND
Coded fields end in _CODE
Description fields end in _DESC
Date fields end in _DATE
All tables will have a unique key field that identifies the
record.
◦ These fields will include the table name and end in _KEY

Use descriptive names for tables and fields





Star Schema Used
Avoid the Snowflake!
Central Fact Table
Supporting Dimension
Tables
Financial Aid Awards by
Term Example

Identify data that will be shared among many subject areas
in the warehouse (conformed dimensions)
◦ Person Bio/Demo
◦ Academic Term
◦ Student



Build your dimensions with the assumption that they will
be used with other subject areas in the warehouse
Start with a few basic, useful fields per dimension
Focus on one department first
◦ Financial Aid was a good starting point for UNOH because they requested
the most ad-hoc data and had more tech-savvy users

Focus on one subject area to produce a quick “win”
◦ Financial Aid Awards by Term
◦ These users will be your evangelists!




SQL Server Integration Services (SSIS) moves the data from the
Colleague database to the warehouse
Data is loaded from multiple sources
Datatel ODS can also be used as an intermediate data store
Load the dimension tables first, then the fact tables
Load a fact table
Load a dimension table



Centralized reporting system
Web Intelligence provides ad hoc reporting from the
data warehouse
Subject-based data is organized into Universes
◦ Universes store that metadata for the fields in the data
warehouse

The Universes add a user-friendly layer to the reporting
model


Identify the data subject area
Design and build the dimension tables
◦ Data modeling software

Design and build the fact table
◦ Data modeling software

Build the ETL package and load the tables
◦ Business Intelligence Development Studio
◦ SQL Server Integration Services

Build the Business Objects Universe
◦ Business Objects Designer

Publish the Universe

WEBI Demo


Getting users to understand the concept of a
data warehouse
Learning the technical concepts
◦ We are still learning!

Determining where to place the data
◦ Fact or dimension table

Finding educational data warehouse
examples
◦ Business uses a Time dimension , education uses
Term (in most cases)

The Data Warehouse Toolkit: Practical
Techniques for Building Dimensional Data
Warehouses
◦ by Ralph Kimball
◦ ISBN: 0-471-15337-0


Other Campus Data Warehouse Sites
Google
Jeremy Brinkman
Director of Administrative Systems
University of Northwestern Ohio
[email protected]
Great Lakes Users’ Group Conference
August 10-11, 2009