1 / 32

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

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

lada
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. An Opportunistic Approach to Data Warehouse Integration:orHow 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 • What’s Next?

  4. 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 Off-Campus Systems

  5. SIS Reporting (Infomaker) Fin & HR Reporting (Brio) Canned Portal Dept Apps Ad Hoc Data Access: “Siloed”Support Archive Data Off-Campus Systems HR SIS Financials Data Warehouse: Daily Replication AIX Sybase 12.5 the line of “no support” DeptApps HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX 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. …to one-click online reports via personalized portal. 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 (SISUser 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. Table design based 1:1 on SIS Screens 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 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?

  22. Ad Hoc Ad Hoc SIS SIS Financials Financials HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX Case One: SIS-Financial Reporting Am I collecting enough application fee revenue? Canned Portal Dept Apps Data Access: “Siloed”Support 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 PaymentType FeePaid (Y/N) FeeAmount($$) SIS-FAS Account Mapping Table PaymentCode AccountID FAS Table AccountID TransactionID Amount ($$) Mapping Table FAS 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 PaymentType FeePaid (Y/N) FeeAmount($$) SIS-FAS Account Mapping Table PaymentCode AccountID FAS Table AccountID TransactionID Amount ($$) Mapping Table FAS 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? Canned Portal Dept Apps Ad Hoc Data Access: “Siloed”Support Archive Data Off-Campus Systems HR SIS Financials Data Warehouse: Daily Replication AIX Sybase 12.5 DeptApps HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX

  26. add to batch production cycle Case Two: SIS-SEVIS Reporting How many international students are in my department? Canned Portal Dept Apps Ad Hoc Data Access: “Siloed”Support Archive Data Off-Campus Systems HR SIS Financials Data Warehouse: Daily Replication AIX Sybase 12.5 DeptApps HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX

  27. HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX Case Three: SIS-HR Reporting How many faculty are in my department? Canned Portal Dept Apps Ad Hoc Data Access: “Siloed”Support Archive Data Off-Campus Systems HR SIS Financials Data Warehouse: Daily Replication AIX Sybase 12.5 DeptApps

  28. HR (PeopleSoft) SIS(Legacy) Financials (Legacy) VSAM & DB2 on zOS & AIX What’s a “faculty”? Case Three: SIS-HR Reporting How many faculty are in my department? Canned Portal Dept Apps Ad Hoc Data Access: “Siloed”Support Archive Data Off-Campus Systems HR SIS Financials 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 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

  31. Lessons “Learning” • Listen to today’s users • Seek partners • Promote the BIG VISION • Today’s chicken sandwich might be tomorrow’s jet liner…

  32. Thank you ! Bernie Kluger bkluger@columbia.edu 212-854-1361 Columbia UniversityStudent Information Systemswww.columbia.edu/cu/sis

More Related