Features - Enterprise Data Insights:

DATA HANDLING EASED
By Dennis Callaghan

Enterprises faced with the daunting task of managing hundreds of thousands —- if not millions —- of records in data warehouses are finding that job scheduling applications can ease their data management headaches.

Companies use job scheduling tools to automatically schedule migrations and conversions of data from flat files into data warehouses, specifying how often to update the data warehouse and to what folders to send data.

SAS Institute Inc recently developed integration between its SAS/Warehouse Administrator data warehousing application and Platform JobScheduler from Platform Computing Inc, in Markham, Ontario. The integration enables companies to apply Platform's workload management and distributed management capabilities to SAS' data capturing and integration technologies to schedule, analyze and monitor data warehousing job flow with little human interaction.

Spectra Marketing Systems Inc is using the two technologies to better manage market research data it compiles on retail outlets for its packaged goods manufacturing clients.

The Chicago-based market research company compiles data on 750,000 retail outlets. It must convert data from flat files into a relational format, store them in a data warehouse, then ship the appropriate reports from the data to clients on a monthly basis.

"We need to automate as much of this process as we can to minimize the business impact," said Dennis Klos, vice president of data development in Spectra's Lancaster, PA, office. "We've saved quite a bit of time with the scheduling package."

A process that once took three weeks to complete now takes seven business days on average and runs automatically 24 hours a day without human intervention, Klos said. He said the job scheduling tools in Spectra's Oracle Corp database don't match the capabilities offered in Platform JobScheduler.

"It's a huge efficiency gain that every one of our clients benefits from," Klos said. "Because of the increased throughput, we can bring products to market faster."

Integrated tool sets are only one way enterprises are using job scheduling technologies to accelerate data warehouse administration.

Asurion Inc, in San Mateo, CA, provides wireless communication services such as roadside assistance and handset insurance and built its data warehouse on Sagent Technology Inc's Data Flow Server, residing on a Microsoft Corp SQL Server database.

Sagent, of Mountain View, CA, then helped the company to write Perl script to handle job scheduling, said Wayne Collins, manager of IT enrollment, who's based in Asurion's Nashville, TN, office. "We get the flat files or text files and populate the data warehouse," said Collins. "The Perl scripting moves the files to the proper places in the data warehouse. It moves it to where Sagent looks at it, then moves it to another folder to show that it's been processed."

This process happens automatically on an hourly basis as Asurion processes more than 2 million records a day from its handset insurance enrollment database, he said. In addition, Asurion's sales team gets updated reports on the data without human intervention.

Because of the success of the initial project, Asurion is looking to expand its use of the Sagent-powered solution to its roadside assistance database, which would increase the workload to 5 million records a day, Collins said.

There are other ways to integrate job scheduling tools with data warehousing applications. Before its integration with Platform JobScheduler, SAS, of Cary, NC, like most data warehousing vendors, simply provided support for the Cron and AT commands in the Unix and Windows NT operating systems, respectively. Company officials said Platform JobScheduler provides more sophisticated job scheduling tools.

SQL Server also includes its own job scheduling tool, SQL Server Enterprise Manager. Asurion's Collins said the tools in SQL Server didn't meet his needs. But Enterprise Manager fits the bill for COBE Cardiovascular Inc, a manufacturer of cardiac surgery equipment.

"It was a large part of why we chose SQL Server as our database because Enterprise Manager has the scheduler built in," said Marsha Williams, vice president of IT at COBE, in Arvada, CO.

COBE uses Ottawa-based Cognos Inc's DecisionStream extraction, transformation and loading tool to build its data warehouse. The scheduler in Enterprise Manager kicks off SQL statements, which call data from DecisionStream. DecisionStream repopulates the data warehouse, which contains about 338,000 base records, on a nightly basis, which takes about 2.5 hours, Williams said.

Regardless of the tools a company requires, Williams said warehouses need automated scheduling. "If you never change your historical updates, then it really isn't an issue," she said. "But I'm not sure if there's a company around today that doesn't have to update their historical data."