Dana C Voss Manager Decision Support Services UIS

Dana C. Voss Manager, Decision Support Services, UIS University Information Technology Services INDIANA UNIVERSITY May 2003 Copyright Dana C. Voss, 2003. This work is the intellectual property of the author. Permission is granted for this material to be shared for non-commercial, educational purposes, provided that this copyright statement appears on the reproduced materials and notice is given that the copying is by permission of the author. To disseminate otherwise or to republish requires written permission from the author.

n Technical Implementation of Indiana University’s enterprise web reporting environment & demo History n Accomplishments n Challenges n n Business Perspective n How we did it n Lessons Learned

n Technical Implementation of Indiana University’s enterprise web reporting environment & demo History n Accomplishments n Challenges n n Business Perspective n How we did it n Lessons Learned

n Public University n 8 Campuses n 10, 565 Appointed Staff members n 4, 745 Faculty members n 97, 000 enrolled students in Fall 2001 -2002

People. Soft Student & HR Library Financial Information Systems Electronic Records Management Time. Keeping Maintenance Management System

What do All These Applications Have in Common? ?

n 20 years of mainframe reporting: Information Center in Focus n 1995 -2000: Financial Data in Sybase n 2000: moved to Oracle n Today: n n n Student Admissions (People. Soft) Human Resources (People. Soft) Library, Financial Data, etc. n 500 GB n 1000+ data structures n Upcoming (2003 -2004): People. Soft Student Financials, Student Records, Financial Aid, Academic Advising n n 3 -5 TB

n 1, 100 Users (Consumers) n 100 programmers (Providers) n What was wrong: n n n too confusing Requires Focus programming skills to write reports Users want one place to go to get data & reports Users don’t want to choose their own tools Departments don’t typically have resources to build their own reporting applications

n Data Warehouse Infrastructure n Reporting Environment

n 20 years of mainframe reporting: Information Center in Focus n 1995 -2000: Financial Data in Sybase n 2000: moved to Oracle & AIX n 2002: purchased ETL tool (Informatica) n Today: n n n Student Admissions (People. Soft) Human Resources (People. Soft) Library, Financial Data, etc. n 500 GB n 1000+ data structures n Upcoming (2003 -2004): People. Soft Student Financials, Student Records, Financial Aid, Academic Advising n n 3 -5 TB

n Simple application interfaces n One place to go to get data & reports n Don’t want to choose their own tools n Departments don’t typically have resources to build their own reporting applications

Ad-hoc Inquiries Dept-al Reporting (Crystal, MS/Access) Complex, Operational Reporting (SQR) Managed Reporting (IUIE) Reports to support daily business/operational needs Functional Support Staff Designed by Brian D. Voss Analysis Ad-hoc Query Analyze business within and across departments Specific or one-time queries/reports Analysts, Dept. Heads, Decision Makers Anyone

n Indiana University Information Environment https: //onestart. iu. edu/iuie n Web based reporting application n n n Provides point and click interface for functional users Delivery of Enterprise-Wide Operational Data and Reports User interface for the Data Warehouse In production since November 2000 Work on enhancements continues today Developed in Uniface & Perl CGI initially Conversion to J 2 EE completed in April, 2003

n 3500 Users – requests for new accounts come in daily n 1300+ Report Objects (RO) as of May 2003 n 750+ requests for ROs per day n 325+ users log in a day n Supports: Object & Row-level security (at the database level) n Scheduling n

n We believe IUIE satisfies 70 -80% of the end–user needs for reporting n n However, the reporting tool, SQR requires programming skills, significant learning curve Handful of programmers write reports and publish them in IUIE n Analysis needs are not met n Ad-hoc reporting needs are not met n Power Users use Crystal Reports, Access, Excel, and other tools

Complex, Operational Reporting (SQR) Managed Reporting (IUIE) Reports to support daily business/operational needs Functional Support Staff Designed by Brian D. Voss Business Intelligence Tool Ad-hoc Inquiries Dept-al Reporting (Crystal, MS/Access) Analysis Ad-hoc Query Analyze business within and across departments Specific or one-time queries/reports Analysts, Dept. Heads, Decision Makers Anyone

n Easier to learn than SQR – doesn’t necessarily require programming skills n Easier to build reports n Can augment or replace SQR for building enterprise wide reports n Analysis made easy n Dashboards for the decision makers n Centrally supported Ad-hoc reporting tool

n Technical Implementation of Indiana University’s enterprise web reporting environment & demo History n Accomplishments n Challenges n n Business Perspective n How we did it n Lessons Learned

n Business/Functional users are oftentimes too busy working on the ERP implementation to turn their attention to reporting specifications n Day 1 – Users want access to the Data and they want the data and reports to be PERFECT! n In some cases many new requirements or requests for changes to the reporting environment come in AFTER the implementation

n Implementation date: 1995 n FMS developers worked and continue to work very closely with core users n Financial Data Retrieval System (FDRS) https: //fdrs. fms. indiana. edu/fdrs/ converted to IUIE n 445 ROs, approx. 2500 Users

n Implementation date: Nov. 2000 (Phase 1) n n n n Oct. 2001 Row-Level security (Phase 2) Jan. 2000 – inventory of existing Focus reports Several meetings with functional users Data structure creation - Central IT team at UITS 95% of reports created by UITS 1 week before go-live date – training in IUIE content Significant paradigm switch for the end users n IE Admissions User Groups formed in early 2003 n 456 ROs, approx. 1000 users

n n n n Implementation date: December 2002 Sept. 2001 – inventory of existing Focus reports Several meetings with functional users from main offices Data structure creation - Central IT team at UITS 95% of reports created by Functional Users Limited training in IUIE content (Payroll) End users more mature (overlap with Financial Data) Today: n Payroll – less involved in the implementation phase, actively writing reports today n Academic – very involved in the implementation n HR & Benefits – less involved, but doing ok n 617 ROs, approx. 600 users (only Central Offices)

n Implementation date: January 2005 n Committees: n IUIE Implementation Team n SIS Steering Committee n SIS Executive Committee n Using People. Soft RDS running on Informatica n Inventory finalized Legacy Reports New DW tbl’s New Incrementals Frozen Files Total SF 455 225 10 5 244 484 SR + AA 466 104 26 63 195 388 FA 461 175 10 5 190 380 Total 1382 504 46 73 629 1252

n Technical n Data Warehouse n n n ETL Tool is key Data model – depends on data warehouse size, reporting requirements, hardware resources Reporting Solution n Build vs. Buy – depends on the problem addressed, the level of skill within institution, and the availability of packaged solutions n Business n End User involvement is key n Identify and understand the data elements that are needed for reporting n Identify Report Writers n Provide adequate training to the Report Writers n Metadata is important! n Training in the data content is essential

Dana C. Voss dvoss@indiana. edu INDIANA UNIVERSITY