Getting data from multiple source systems into one data warehouse was keeping employees at the University of Connecticut...
Health Center (UCHC) busy -- too busy.
UCHC had traditionally handled data management jobs through customized scripts, individual application features and manual intervention, but the process wasn't perfect. For example, one job takes 16 hours, so the team would let it run over the weekend, according to Bruce E. Salisbury, director of data services at the Farmington, Conn.-based hospital. If something went wrong, the system would alert the team -- but then an employee had to log in manually and figure out what had gone wrong. That translated to additional costs in overtime compensation and an inefficient process.
It's a common problem, according to Richard Ptak, partner and founder of Amherst, N.H.-based research firm Ptak, Noel & Associates. Large organizations run hundreds, even thousands, of jobs every week to load data warehouses. Jobs may have interdependencies, may need to run in a certain order, or may require lots of time and system resources. The problem is that when manual intervention is required, the entire process of loading the data warehouse slows down. Worse, Ptak said, failing jobs and manual processes can jeopardize data integrity.
As the UCHC team evaluated the supporting technology for their BI project, they zeroed in on job scheduling software. These tools provide a single, cross-platform interface and adapters for automating data management jobs.
That was a smart move, Ptak said.
"[Job scheduling software] automates repeatable tasks, brings intelligence to the process, and automates workflows," he said. "It helps with standardization, problem solving and resolution, which frees resources, adds consistency to operations, and helps in governance."
UCHC made enterprise-wide job scheduling capabilities a requirement for the BI initiative. The project team settled on a data warehousing system from Redwood City, Calif.-based Informatica Corp., with front-end BI tools from Ottawa-based Cognos Inc. and Microsoft. They looked at Informatica's native job scheduling feature, but its basic capabilities weren't enough to manage all of the enterprise systems involved, among them SQL databases, legacy mainframes and a PeopleSoft application. The team ultimately chose Palo Alto, Calif.-based Tidal Software Inc.'s Enterprise Scheduler, Salisbury said, and completed implementation last month.
Now, through a single interface, UCHC's team can define the order in which jobs run, how and when they run, and contingency plans in case one fails to run. It makes troubleshooting easier by providing data that helps identify why jobs failed. Job scheduling software ensures that data from all sources is loaded as planned into the data warehouse -- and thus that the BI tools display the most current and accurate data. And the new software will reduce the number of weekend troubleshooting hours, increase efficiencies and reduce costs, Salisbury said.
UCHC isn't the only company that's come to this conclusion during BI project planning or even after the system is up and running, according to Rod Butters, Tidal's senior vice president of marketing. IDC predicts that the worldwide market for job scheduling software will grow to $1.77 billion in 2010, and Butters said that in the last nine months, Tidal has seen an increase in the number of companies purchasing job scheduling software as part of a BI or data warehousing project. Organizations such as biopharmaceutical company UCB and biotechnology company Genzyme Corp. have also purchased Tidal software to support BI projects, Butters said. It's related to the changing role of BI and new motivations of regulatory compliance.
"If your BI tool and data warehouse are simply there for market analysis and research, and the data is out of date or inconsistent, that's not necessarily a fatal flaw," Butters said. "But if your warehouse is either a source of financial information for reporting or core to your operations, it's critical that the data is accurate, up to date, and in many cases, the result of an auditable process."