CASE STUDY - UCHC Leverages Enterprise Job Scheduling to Automate Data Integration Processes
Commentary by Kevin McDonnell, Jay Coppola, and Bob Senack, Data Services Team, University of Connecticut Health Center
In 2006, the University of Connecticut Health Center (UCHC), a leading biomedical research center, healthcare facility, and medical school, launched a data warehousing initiative designed to leverage its massive pool of data.
Essentially, UCHC wanted and needed one source of truth. That requirement mandated creating a global data warehouse that would dissolve barriers between departments and produce uniform and accurate reporting across the organization.
The Data Integration Challenge
Prior to using enterprise scheduling technology, UCHC managed data extraction and loading manually through customized scripting, point solutions, and hands-on management. Though IT was usually able to deliver results within the time frames that users required, the effort was labor intensive and inefficient. In a typical scenario, the data warehouse team would run a data load that was dependent on certain files arriving via FTP. The team would have to check repeatedly to determine if the files had arrived and then run the load when they were all present. If any of the files were corrupted, it could take hours to recover from the resulting errors.
The Solution: Robust Job Management through Enterprise Scheduling
The IT team chose Tidal Enterprise Scheduler for its ability to automate the complex data integration process, manage dependencies, and ultimately ensure the accuracy of information and reporting.
The data warehouse team found that Tidal Enterprise Scheduler met its needs for a secure, easy-to-use interface and central location for monitoring all job processes. Tracking the movement of files from one group of users to another became faster and simpler. In addition, the group was able to assume responsibility for FTP jobs and data duplication tasks that had been scattered across other UCHC departments and manage them more efficiently. Tidal’s method of handling errors also proved helpful when jobs ran into problems. Its proactive e-mail alerting significantly reduced the time involved in identifying and addressing issues.
Apart from specific features, what ultimately influenced the decision to choose Tidal Enterprise Scheduler was UCHC’s overarching need for process automation. Kevin McDonnell, DBA at UCHC, remarks that Tidal Enterprise Scheduler complements elements of the Informatica solution. “Informatica creates staging environments for our data warehouse initiatives. To move from a staging to a production environment successfully means bringing in flat files and cleaning up data. Tidal helps us keep track of all aspects of the process and schedules them in the correct sequence. We’ve largely been able to eliminate manual intervention. That’s a huge bonus.”
Results: Greater Efficiency and Savings across a Complex Organization
Effectively monitoring and scheduling the processes involved in data aggregation and loading ensures that a sophisticated BI solution, such as Informatica, delivers the accurate, timely information that users need.
At UCHC, the IT team has made good use of Tidal Enterprise Scheduler’s automation and standardization capabilities and has extended its deployment use beyond the core user group. In the works are plans to incorporate its help desk in day-to-day Tidal operations.
“Tidal Enterprise Scheduler has done an excellent job of reducing the amount of time we need to spend in managing our environment,” says Jay Coppola, data warehouse architect, UCHC. “For example—and this is just one saving I can point to—monitoring file size used to take as much as four hours of a team member’s time. Now, Tidal handles that for us. That’s time we can divert to other important tasks.”
Coppola further notes that the scheduler will enable UCHC to work effectively with new applications. “We’ve recently added Oracle to our environment and already use SQL Server. Though both incorporate some native scheduling, we have found that it makes more sense from a management and performance standpoint to leverage Tidal Enterprise Scheduler. We see that as part of our new application-related procedures going forward.”
For a free white paper on this topic, click here and choose the title “Automating Data Flows to Your Business Intelligence System.”