Transcript Document

An Opportunistic Approach to
Data Warehouse Integration:
or 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
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.
Presentation Overview
i.
ii.
iii.
iv.
v.
The Big Idea
Columbia and SIS
SIS Reporting Services
Three Cases
What’s Next?
The Big Idea
One view of all data…any one, any way, right now.
Web
Portal
Dept
Apps
Ad Hoc
Reporting
ERP
Systems
Legacy
Archives
Dept
Apps
OffCampus
Systems
Where we are today…
SIS Reporting (Infomaker)
Archive
Data
Off-Campus
Systems
Dept
Apps
the line of “no support”
Canned
Fin & HR Reporting (Brio)
Dept
Apps
Portal
Ad Hoc
HR
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
VSAM & DB2
on zOS & AIX
How to Make a Chicken Sandwich
Columbia and SIS Reporting
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
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.
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
SIS Reporting Services
An Evolving Toolkit
From green screens and green
bar paper…
…to one-click online reports via
personalized portal.
SIS Systems: In the beginning…
The Student Information System
Initiation:
• Users:
• Platform:
• Ad Hoc:
• Reports:
1992
980
0S/390 Mainframe
none
minimal
CICS/VSAM/Cobol
First Generation Reporting
SISUMENU (SIS User MENU)
Initiation:
• Users:
• Platform:
• Ad Hoc:
• Reports:
• Integration:
Green Bar Paper, Green Screens
1992
25 and falling
0S/390 Mainframe
FOCUS in CMS
~100
None
Second Generation: Ease of Access
SIS Report Server
Initiation:
• Users:
• Platform:
• Ad Hoc:
• Reports:
• Integration:
Table design based 1:1 on SIS Screens
1994
50 and falling
Sybase, AIX Unix
SQL, InfoMaker
None
Possible
Third Generation: Ease of Use
SIS Desktop Reports
Initiation:
• Users:
• Platform:
• Ad Hoc:
• Reports:
• Integration:
2000
296 and rising
InfoMaker
None
505 and rising
None
Sybase stored procedures, parameterdriven, easier-to-use.
Third Generation: Ease of Use
Third Generation: Ease of Use
Third Generation: Ease of Use
Three Cases: Platypus, Rubber Chicken,
or Dodo
Simple Questions…Not 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?
Case One: SIS-Financial Reporting
Am I collecting enough application fee revenue?
Canned
Dept
Apps
Portal
Archive
Data
Off-Campus
Systems
Ad Hoc
HR
SIS
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
Dept
Apps
VSAM & DB2
on zOS & AIX
Case One: SIS-Financial Reporting
Am I collecting enough application fee revenue?
APPL Table
ApplicationID
StudentID
DeptID
PaymentType
FeePaid (Y/N)
FeeAmount($$)
Account Code
Table
DeptID
PaymentType
PaymentCode
SIS Tables
SIS-FAS Account
Mapping Table
PaymentCode
AccountID
Mapping Table
FAS Table
AccountID
TransactionID
Amount ($$)
FAS Table
Case One: SIS-Financial Reporting
Am I collecting enough application fee revenue?
APPL Table
ApplicationID
StudentID
DeptID
PaymentType
FeePaid (Y/N)
FeeAmount($$)
Account Code
Table
DeptID
PaymentType
PaymentCode
SIS Tables
SIS-FAS Account
Mapping Table
PaymentCode
AccountID
Mapping Table
FAS Table
AccountID
TransactionID
Amount ($$)
FAS Table
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
Case Two: SIS-SEVIS Reporting
How many international students are in my department?
Canned
Dept
Apps
Portal
Archive
Data
Off-Campus
Systems
Ad Hoc
HR
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
Dept
Apps
VSAM & DB2
on zOS & AIX
Case Two: SIS-SEVIS Reporting
How many international students are in my department?
Canned
Dept
Apps
Portal
Archive
Data
Off-Campus
Systems
Ad Hoc
HR
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
Dept
Apps
add to batch production cycle
VSAM & DB2
on zOS & AIX
Case Three: SIS-HR Reporting
How many faculty are in my department?
Canned
Dept
Apps
Portal
Archive
Data
Off-Campus
Systems
Ad Hoc
HR
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
Dept
Apps
VSAM & DB2
on zOS & AIX
Case Three: SIS-HR Reporting
How many faculty are in my department?
Canned
Dept
Apps
Portal
Archive
Data
Off-Campus
Systems
Ad Hoc
HR
SIS
Financials
HR
(PeopleSoft)
SIS
(Legacy)
Financials
(Legacy)
Data Access:
“Siloed”Support
Data Warehouse:
Daily Replication
AIX Sybase 12.5
Dept
Apps
What’s a “faculty”?
VSAM & DB2
on zOS & AIX
Next Steps at Columbia
Next Steps at Columbia
• Advertise the availability of integrated reports
• Monitor “freshness” of mapping tables…or
create new mapping tables
• Add off-campus system to data warehouse
• Seek more projects that push the limits of
existing data warehouse design
Lessons “Learning”
• Listen to today’s users
• Seek partners
• Promote the BIG VISION
• Today’s chicken sandwich might
be tomorrow’s jet liner…
Thank you !
Bernie Kluger
[email protected]
212-854-1361
Columbia University
Student Information Systems
www.columbia.edu/cu/sis