An Opportunistic Approach to Data Warehouse Integration: or How to Make a Chicken Sandwich

1
An Opportunistic Approach to Data Warehouse
Integrationor How to Make a Chicken Sandwich

• April 19, 2005
• Bernhard Kluger, Associate Director
• Student Information Systems
• Columbia University, New York, NY
• Best Practices in Data Warehousing in Higher
  Education
• Northwestern University, Evanston, IL

2
Presentation Goal

• Identify specific examples of how Columbia has
  used its data warehouse to provide a single,
  integrated view of enterprise data
• and where we have not.

3
Presentation Overview

• The Big Idea
• Columbia and SIS
• SIS Reporting Services
• Three Cases
• Whats Next?

4
The Big Idea

• One view of all dataany one, any way, right now.

Web
Portal
Dept Apps
Ad Hoc
Reporting
ERP Systems
Legacy Archives
Dept Apps
Off-Campus Systems
5
Where we are today
6
How to Make a Chicken Sandwich
7
Columbia and SIS Reporting
8
Columbia University

• Located in New York
• Founded 1754
• 23,400 Students
• 7,700 Degrees Conferred
• 8,900 Faculty and Staff
• 17 Schools
• 4 campuses
• Highly Decentralized

http//www.columbia.edu/cu/opir/Facts2003.htm
9
What Is SIS?

• Mission Statement
• At SIS we provide the best possible integrated
  student information services to the Columbia
  University community by working collaboratively
  to ensure the accessibility, integrity, and
  security of student information.

10
What Is SIS?

• A Partnership of Administrative Information
  Services and the Division of Student Services
• 25 employees in application development,
  maintenance, help desk, training, business
  process consulting, and reporting
• In 2003, received 2,436 requests from 426
  distinct users in 99 departments

11
SIS Reporting Services
12
An Evolving Toolkit

• From green screens and green bar paper

13
SIS Systems In the beginning

• The Student Information System
• Initiation 1992
• Users 980
• Platform 0S/390 Mainframe
• Ad Hoc none
• Reports minimal
• CICS/VSAM/Cobol

14
First Generation Reporting

• SISUMENU (SIS User MENU)
• Initiation 1992
• Users 25 and falling
• Platform 0S/390 Mainframe
• Ad Hoc FOCUS in CMS
• Reports 100
• Integration None

Green Bar Paper, Green Screens
15
Second Generation Ease of Access

• SIS Report Server
• Initiation 1994
• Users 50 and falling
• Platform Sybase, AIX Unix
• Ad Hoc SQL, InfoMaker
• Reports None
• Integration Possible

16
Third Generation Ease of Use

• SIS Desktop Reports
• Initiation 2000
• Users 296 and rising
• Platform InfoMaker
• Ad Hoc None
• Reports 505 and rising
• Integration None
• Sybase stored procedures, parameter-driven,
  easier-to-use.

17
Third Generation Ease of Use
18
Third Generation Ease of Use
19
Third Generation Ease of Use
20
Three Cases Platypus, Rubber Chicken, or Dodo
21
Simple QuestionsNot Simple to Answer
Is our department collecting as much application
fee revenue as last year? How many international
students are in my department? How many faculty
are in my department?
22
Case One SIS-Financial Reporting

• Am I collecting enough application fee revenue?

Canned
Portal
Dept Apps
Data Access SiloedSupport
Archive Data
Off-Campus Systems
HR
Data Warehouse Daily Replication AIX Sybase
12.5
DeptApps
23
Case One SIS-Financial Reporting

• Am I collecting enough application fee revenue?

APPL Table ApplicationID StudentID DeptID Payment
Type FeePaid (Y/N) FeeAmount()
FAS Table AccountID TransactionID Amount ()
SIS-FAS Account Mapping Table PaymentCode Account
ID
FAS Table
Mapping Table
Account Code Table DeptID PaymentType PaymentCode
SIS Tables
24
Case One SIS-Financial Reporting

• Am I collecting enough application fee revenue?

APPL Table ApplicationID StudentID DeptID Payment
Type FeePaid (Y/N) FeeAmount()
FAS Table AccountID TransactionID Amount ()
SIS-FAS Account Mapping Table PaymentCode Account
ID
FAS Table
Mapping Table
Account Code Table DeptID PaymentType PaymentCode

• Design Issue Will each user require FAS
  authorization?
• SIS database authorizes by DeptID
• FAS database authorizes by AccountID
• Is mapping table accurate?
• Resolution
• SIS stored proc hands user ID to FAS database
• Publish mapping report

SIS Tables
25
Case Two SIS-SEVIS Reporting

• How many international students are in my
  department?

Ad Hoc
Canned
Portal
Dept Apps
Data Access SiloedSupport
Archive Data
Off-Campus Systems
SIS
Financials
HR
Data Warehouse Daily Replication AIX Sybase
12.5
DeptApps
SIS(Legacy)
Financials (Legacy)
HR (PeopleSoft)
VSAM DB2 on zOS AIX
26
Case Two SIS-SEVIS Reporting

• How many international students are in my
  department?

Ad Hoc
Canned
Portal
Dept Apps
Data Access SiloedSupport
Archive Data
Off-Campus Systems
SIS
Financials
HR
Data Warehouse Daily Replication AIX Sybase
12.5
DeptApps
SIS(Legacy)
Financials (Legacy)
HR (PeopleSoft)
VSAM DB2 on zOS AIX
27
Case Three SIS-HR Reporting

• How many faculty are in my department?

Ad Hoc
Canned
Portal
Dept Apps
Data Access SiloedSupport
Archive Data
Off-Campus Systems
SIS
Financials
HR
Data Warehouse Daily Replication AIX Sybase
12.5
DeptApps
28
Case Three SIS-HR Reporting

• How many faculty are in my department?

Ad Hoc
Canned
Portal
Dept Apps
Data Access SiloedSupport
Archive Data
Off-Campus Systems
SIS
Financials
HR
Data Warehouse Daily Replication AIX Sybase
12.5
DeptApps
29
Next Steps at Columbia
30
Next Steps at Columbia

• Advertise the availability of integrated reports
• Monitor freshness of mapping tablesor create
  new mapping tables
• Add off-campus system to data warehouse
• Seek more projects that push the limits of
  existing data warehouse design

31
Lessons Learning

• Listen to todays users
• Seek partners
• Promote the BIG VISION
• Todays chicken sandwich might be tomorrows jet
  liner

32
Thank you !

• Bernie Kluger
• bkluger_at_columbia.edu
• 212-854-1361
• Columbia UniversityStudent Information
  Systemswww.columbia.edu/cu/sis