Automated ETL Processes From iWay Software Reduce Costs, Streamline Customer Service at North Carolina Farm Bureau

Agricultural fields look pretty much the same from one decade to the next. But the business arrangements that farmers depend on to protect their assets have undergone extensive changes in the past half-century, particularly with the rise of the Internet and the availability of Web-based information. Consider the business processes used by the North Carolina Farm Bureau Mutual Insurance Company (NCFB), which is leveraging technology to help farmers and other customers initiate policies, make payments, and manage claims.

With its home office in Raleigh, North Carolina, NCFB was incorporated in 1953 to provide rural and farming citizens appropriate and affordable insurance coverage. Since then, the company has expanded its reach far beyond the sun-bleached fence posts of local farms to become the largest domestic property and casualty insurer in the state. With the help of technology and services from iWay Software, NCFB is enhancing information-access capabilities and speeding up the delivery of Web-based applications that benefit NCFB agents and customers. Like most financial services firms, NCFB succeeds through its ability to quickly access and deliver information. Before iWay came on the scene, information technology (IT) personnel at NCFB wrote COBOL programs to move data from a mainframe computer, where their production DB2 databases reside, to smaller servers distributed throughout the enterprise. Creating these extract, transform, and load (ETL) processes was labor intensive, and running them as batch jobs consumed mainframe cycles, making the mainframe unavailable for ad hoc query and reporting activities.

IT leaders decided to build a data warehouse that could be periodically refreshed from the mainframe DB2 data. The warehouse, residing on a Microsoft Windows NT platform, would always be available for live queries, thus sparing the mainframe the arduous task of fielding ad hoc requests.

Systems analysts knew this architecture would be an ideal way to streamline claims-processing activities while reducing mainframe cycles. The tricky part came with transfer-ring data from one platform to another in a straightforward, reliable manner. "We had to come up with a better way to move data to the warehouse without running COBOL programs at night," says Gail Wall, data administration supervisor at NCFB. "The ideal solution would automate the ETL process and only capture the changes that occurred each day."

Surveying the ETL Field

NCFB conducted a comprehensive evaluation of all the ETL software on the market, focus-ing on technology from Informatica, Ascential, DataMirror, Sagent, SAS, and iWay Software. The only product that met all NCFB's technical requirements was iWay's DataMigrator. "We did an extensive proof of concept," Wall recalls. "We needed a solution that could read a DB2 log, support change data capture, and deliver bidirectional loading across a mainframe/Windows NT environment."

According to Wall, change data capture (CDC) was a critical element in NCFB's decision. "We have 4 million rows of data in our DB2 tables," she says. "We didn't want to refresh the entire database every night, so we needed a way to simply copy the differences. iWay automates that process for us so we don't have to use our resources to write COBOL batch routines. Additionally, our operational database has compressed tables, and iWay was the only product that could capture the changes on a compressed database." Another important deciding factor was iWay's ability to work bidirectionally – reading from the mainframe, writing to the Windows NT servers, and vice versa.

Difficulty getting their DB2 Connect processes to work correctly led to another element in the decision process. "All the other products we considered depend on DB2 Connect to work," Wall says. "But iWay has its own adapters." The foundation for iWay's broad data-access capability is a comprehensive set of 250 codeless adapters that work together to provide universal "anything to anything" connectivity. iWay is based on open standards, which help NCFB protect its investments in existing technologies. "Of all the solutions we considered, iWay fit best in our environment," Wall notes. "We have a lot of specific needs, and iWay met all of them."

Putting a Shoulder to the Work

With help from Information Builders' Consulting, Wall and her team had several iWay ETL processes up and running within a couple of months. "Using DataMigrator is relatively easy," she says. "Setting up the source, target, and transformational routines to move data is all simple to understand."

For example, in addition to nightly transfers to and from the data warehouse, NCFB is using iWay ETL tools to replicate and move tables from a test server to a production server. iWay is also assisting with customer-service processes by staging data for access by Web-based applications. "Our insurance rating package depends on iWay to pull policy information from our operational DB2 database on the mainframe and load it into a Microsoft SQL Server database on a Windows NT server so our reps can access it easily," Wall explains.

iWay also pushes data to servers in district claims offices all over North Carolina. "Using iWay, we can upload data to 10 district offices with one ETL request," Wall adds. "It gives our branch offices a backup copy of this important information in case the systems at our home office are down."

In its largest iWay initiative to date, NCFB is putting the finishing touches on a Web-based credit card application that will allow agents at NCFB's branch offices to accept online credit card payments and enable customers to make payments from home. iWay's change data capture capabilities will play an essential role. "In this architecture, iWay moves data to the server where we can quickly capture the amount currently due for each bill," Wall explains. "Payment information is stored on a Windows NT server, and each night we use DataMigrator to upload the changes back to our mainframe systems."

Reaping a Bountiful Harvest of Benefits

The North Carolina Farm Bureau Mutual Insurance Company is experiencing many benefits as a result of its iWay implementation. Once the new credit card application goes online, they will be depending on iWay to automate more than 150 ETL processes. "The processes we have created with iWay are boosting our productivity, reducing costs, and enhancing revenue," says Wall. "Instead of taxing an overburdened mainframe, we are offloading claims-processing cycles to other servers and opening a larger work window for nightly batch processes. iWay has also alleviated the need for my staff to write so many COBOL programs and reduced the programming time for creating data conversion routines."

Now that NCFB has become acquainted with iWay's basic ETL functions, developers are delving into more sophisticated capabilities. For example, iWay is helping NCFB to centralize and automate the scheduling of jobs running on multiple servers. "iWay allows us to distribute query processing more efficiently across the enterprise," Wall says. "Our customers benefit directly through streamlined bill-paying processes and better response time."

In the future, NCFB plans to leverage iWay for other e-business processes as well. "As we build new Web applications, we won't have to spend time trying to figure out how to move data to and from our operational databases," says Wall. "Thanks to iWay, we can quickly move data to any platform, which helps us build Web applications faster."