Published on 20-Dec-2011
"Now our business managers, administrators and deans have the information needed to answer questions they couldn’t answer before." - Manav Mehra, ETL Team Lead, Enterprise Information and Analytics Department, Office of the CIO, University of Arizona
University of Arizona
Information Infrastructure, Information Integration
In 2008, University of Arizona leaders recognized that to stay competitive with other universities they had to replace aged administrative computer systems. But replacing these source systems was only the first step in expanding the availability of business information to school staff. The University’s Business Intelligence (BI) team used IBM InfoSphere Information Server software to expand its data warehouse.
University leaders sought to expand the availability of business information to authorized users. Decision makers routinely depended on IT experts to compile data, sometimes waiting for days or weeks for information to answer business and operational questions.
The University used IBM InfoSphere Information Server software to understand, cleanse, transform and deliver data from source systems into its enterprise data warehouse, providing a single source of information for business intelligence.
- Increased data warehouse demand to over 1,900 users and growing - Improved time to compile report data from weeks to seconds—more than 90 percent faster - Reduced time to run more than 22,000 nightly ETL jobs by almost 70 percent with upgrade to IBM InfoSphere Information Server 8.5
In 2008, University of Arizona leaders recognized that to stay competitive with other universities they had to replace aged administrative computer systems. The University, located in Tucson, Arizona, serves 38,000 students and is one of North America’s premier research institutions.
“Some of our systems were almost 30 years old,” says Manav Mehra, ETL team lead, Enterprise Information and Analytics Department, Office of the CIO, University of Arizona. “They were no longer supported and couldn’t handle our growing need for business intelligence information.”
Through its enterprise systems replacement strategy, the University implemented PeopleSoft Campus Solutions (for student administration), PeopleSoft Human Capital Management (for human resources/payroll management) and Kuali software (for financials and research administration). But replacing these source systems was only the first step in expanding the availability of business information to school staff.
“We also needed a single source of data that users could query with ease and at their convenience,” says Mehra. “Before, decision makers routinely depended on IT experts to compile data from an Operational Data Store, built nightly from all our source systems, sometimes waiting for days or weeks for the needed answers. For day-to-day University operations we needed to provide this information in an accessible form, not just mirroring the transactional systems.”
Creating a single source for data
The University’s Business Intelligence (BI) team used IBM® InfoSphere® Information Server software for expanding its data warehouse. The software helps staff understand, cleanse, transform and deliver data from various transactional systems and University databases into its enterprise data warehouse. More than 22,000 ETL (extract, transform and load) jobs are run and 3,000 database tables migrated nightly.
“We undertook a phased approach to provide users with access to multiple sources of data, starting with information from our human resource system, then from our student management, research management and financial systems,” says Mehra. “Now our business managers, administrators and deans have the information needed to answer questions they couldn’t answer before. Our current data warehouse is about 630 gigabytes; when we started two years ago, there was only 200 gigabytes of data in the warehouse for reporting.”
The solution includes tools to help University BI staff:
- Discover, model, visualize, relate and standardize diverse and distributed data sets
- Capture and define business requirements in a common familiar format to support the development of ETL jobs
- Gain insight into data source analysis, ETL processes, data quality rules, business terminology, data models and business intelligence reports
“On average, InfoSphere Information Server software saves us around six hours per developer in terms of data modeling and ETL job creation,” says Mehra. “We had two graduate students from our MIS department help us create ETL jobs and they were able to build close to 9,000 ETL jobs from a template within three months. But more important for me is the amount of time it saves in finding and fixing data problems. Everything is well defined, and we can immediately see if there’s a data integrity issue and go back to the system owner to improve the quality of data at the source.”
And, according to Mehra, the newest version of the software further simplifies information integration processes. “We were able to install InfoSphere Information Server 8.5 in just 55 minutes compared to version 8.1, which took a few days to install,” says Mehra. “Performance has also been improved. We can run more than 22,000 nightly ETL jobs in 2.5 hours now as compared to 9 hours before.”
Transforming information into a trusted asset
With integrated data from its core transactional systems, the BI team can provide University staff with information at their fingertips through dashboards, reports and ad hoc querying capabilities. Reports that could take IT weeks to compile for users before are now returned instantaneously. For example, the BI team is developing a salary assessment tool that helps deans determine the right salary level for each new employee—a process that requires staff to take into account not only the candidate’s experience and the job’s salary range, but also the salaries of other employees in the department.
“We have nearly 700 reports to help our staff manage University operations and support student administration,” says Mehra. “Advisors use the reports to determine if their students have met all their graduation requirements. Finance and departmental staff can quickly reconcile payroll and see remaining vacation and furlough hours for each employee. Departments can access statistics on demographics, headcount, hiring rates, salaries, and so on. Even our campus phonebook uses the reports to gather information for each listing.”
According to Mehra, in the six months since deployment, use of the organization’s enterprise data warehouse has significantly increased—a sign that users are finding the information they need.
“We measure our success based on user adoption—in terms of both number of users and how often they log into the system each month,” says Mehra. “We’ve gone from about 500 users to more than 1,900. Almost 40 percent of the University’s staff has access to the system, and nearly 25 percent of these users run reports at least six to seven times a day.”
IBM® InfoSphere® Information Server
- IBM InfoSphere DataStage®
- IBM InfoSphere Data Architect
- IBM InfoSphere FastTrack
- IBM InfoSphere Metadata Workbench
For more information
To learn more about IBM information integration solutions, please contact your IBM sales representative or IBM Business Partner, or visit the following website: ibm.com/software/data/integration
To increase the business value of your IBM InfoSphere software, participate in an on-line community. Learn about opportunities at:
For more information on the University of Arizona, visit: www.arizona.edu
For more information about the University of Arizona enterprise systems replacement project, visit: http://mosaic.arizona.edu/about_esrp
Products and services used
© Copyright IBM Corporation 2011 IBM Corporation Software Group Route 100 Somers, NY 10589 U.S.A. Produced in the United States of America December 2011 All Rights Reserved IBM, the IBM logo, ibm.com, and InfoSphere are trademarks of International Business Machines Corporation in the United States, other countries or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the web at “Copyright and trademark information” at ibm.com/legal/copytrade.shtml Other company, product and service names may be trademarks or service marks of others. References in this publication to IBM products or services do not imply that IBM intends to make them available in all countries in which IBM operates.