Published on 29-Jun-2012
Validated on 02 Dec 2013
"One of the major benefits that we see by using a product that actually helps us in load our data warehouse within a few hours is an increased demand of availability to the users. Our final aim is to actually have the users be able to use the system 24 hours a day, and this gets us very close to that." - Manav Mehra, Sr. Manager Information Integration, University of Arizona
University of Arizona
Information Governance, Information Integration, Information Lifecycle Governance (ILG)
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
IBM InfoSphere Information Server enables the University of Arizona to continuously populate its data warehouse, provide secure data access with trusted information at high speed. This provides a business intelligence system and a single source of truth to support strategic decision making.
Manav Mehra, Sr. Manager Information Integration, University of Arizona
We have four primary transaction systems that we actually source data from to get into the data warehouse, primarily Human Resources, Student Information, Research and Finance. We combine data from all four systems and put it into our data warehouse.
Along with that came the need for a better reporting environment. The university earlier would have to go to all four different places to gather pieces of information to make one report. Now, they can go to one central location and get access to data, as well as reports. The second main reason for this implementation was we wanted to provide our users with secure access to the data along with have them spend lesser time in actually building the report and spend more time in strategic decision making.
Primarily scalability was one of the main features we were looking at in a product. The second thing was performance. We needed to make sure that we get a product that’s able to load this data in a short amount of time. And the suite that that product belongs to was also very important to help us get a better overall picture of ETL.
The problem we were facing at the University of Arizona was that our ETL loads at night would take about nine hours to run. We really had to, from an SLA standpoint, get the ETL load finished within two and a half hours.
We decided to use the InfoSphere Information Server Suite of products for this implementation. Primarily the products that we were using was DataStage for the extract, transform and load process. We used FastTrack for actually creating mapping specifications, which would map our source tables to our target tables. And on average I realized over a long period of time that that would save about four hours per developer in job development time. And the third product that we use was Data Architect, we really didn’t have a data modeling tool at the university before. Data Architect integrated really well with FastTrack and DataStage to help us actually get an overall view using an information server product.
The last product that we used was Metadata Workbench that is again primarily used from a standpoint of maintaining your ETL environment what the jobs are, what’s the name of the job, how many jobs you have, what is an impact analysis of for example changing the length of a column in the source table where in the process is it going to impact, another table or an actual ETL job.
Currently we have approximately 22,000 to 23,000 ETL jobs running every night.
Apart from the four transaction systems that we have, we also have feeds that come in from external systems at the university for example the Career Services Department at the university or the Space Allocation Department at the university. So we do expect it to keep growing, and we actually want our Business Intelligence System to be the single source of truth, if you may call it, for the entire university.
One of the major benefits that we see by using a product that actually helps us in load our data warehouse within a few hours is an increased demand of availability to the users. Our final aim is to actually have the users be able to use the system 24 hours a day, and this gets us very close to that.
I think having a team of experts from IBM available for us to make important decisions even at the time of upgrades etc. is very important. It makes me feel very comfortable in using a product like DataStage, which we know if I ever need to in the future get help on that’s only a sort of an e-mail away.
Basically our future goal at the university and in our department of Enterprise Information and Analytics is to provide our users with an environment where they can actually make strategic decisions using the data that we provide. Currently, our environment is primarily used for operational reporting. We want to change that, we want to actually bring analytics into the university, bring Business Intelligence into the university, help students, help the professors with research, help business managers making informed decisions.