American United Life Insurance (AUL) is not unique in having to deal with enormous and fast-growing data warehouses and the drag those have on system performance. What is unique is the amount of money at stake if AUL’s database updates were not completed on time. More important, the money belongs to thousands of pension plan customers and any losses would have understandable consequences for AUL. Here is how Warehouse (an integral component of Taurus’ DataBridger product) data movement tool solved AUL’s dilemma.

The 19th Century Company With 21st Century Ideas.

American United Life Insurance Company is a mutual company headquartered in Indianapolis, Indiana. The company was founded in 1877 and does business in 46 states and the District of Columbia. AUL controls almost $4 billion in assets and offers 4 major product lines: individual life insurance and annuities, group life and disability insurance, pension products, and reinsurance services.

Donald Evans, IS Manager for AUL’s Pension Group, told us about the fix he was in and how little time he had to find a solution. He said, “We work principally with companies of 100 employees or less. We handle their pension plans and are adding new investment options all the time. Though we do the investing, a corporate customer (referred to as a “case”) can access us through Teleserve and change their investments. They can transfer funds, play a little bit of the stock market, and actually make extra money for themselves. We have to be prepared for these changes at any time. We were quickly approaching the point where we could no longer process all the data on schedule.”

Growing Too Fast For Its Own Good.

New offerings and the ability of customers to call in changes unexpectedly were problems enough. The situation was made even more complicated by AUL’s tremendous growth. In the past, AUL would expect to add 30 or 40 cases a year with a great year topping out at approximately 200 cases. Last year, cases increased by more than 1000. Then, in January 1997 alone, 1100 cases were put on the books. 

Suddenly, the main data warehouse had grown to 136 Gbytes. Things were getting out of hand. The database was so huge, the nightly cycles -- using serial reads -- could not complete between the time processing closed at midnight and the general ledger update, which occurred at 3:30 am. 

According to Don Evans, “We look for unit values to go with the pricing of the stock that has been moved. If we get the unit value at night, but we’re not able to get to everything people have processed during the day, the entry goes into the next day’s processing. That’s not too bad really, unless there’s an increase or drop in the market. It could mean a huge dollar loss or a huge gain, depending on direction of the customer’s choice. The problem is, the difference should result from the customer’s decision, not from our inability to process data. Customers don’t understand data warehouse overload. They just want to know where the money is.” Something had to be done. AUL management gave Don’s group two months to solve the problem.

System Horsepower Was Not The Problem.

The Pension Group already works on two HP 995 800s and uses an HP 969 for development. AUL uses HP’s Shareplex software with Quest’s Netbase for shadowing. This configuration allows users to access the same database from both machines. Specific pension management software has all been written in-house on Basic 5, though the plan is to convert all the software to Speedware. The CPU capacity was there. The answer had to be in the database itself.

The main data warehouse received all of AUL’s new cases. One way to process it faster was to reduce its size by moving 2000 to 3000 cases to other databases. The problem was how. There was no sufficiently large period of time when the database was not in use to do the movement; new entries and updates were always being added. 

One approach tried was an in-house Basic program that would lock the case files and transfer them to the new database one at a time. A test run of 69 cases, involving more than 2000 participants, took over 20 hours to complete. Each case took 15 to 30 minutes to move. Way too long. Doing the job would take many full weekends, a luxury Don Evans did not feel he could afford. Even then, the project would interfere with normal weekend cycles. The amount of data was just too vast. A new answer was needed.

DataBridger Item Level Locking To The Rescue.

Don says, “We could write a COBOL program to do the job, but our programmers were already committed to other work, chiefly the year-2000 problem. We had Warehouse already and liked the program, so we contacted Taurus to see if Warehouse could get us out of this mess. Anything would be better than that Basic approach.

After we found out how to implement item level locking in Warehouse, we tested it running 6 and 8 jobs at a time. They were running at 3 to 15 minute clips, moving huge amounts of data, and there seemed to be no performance hit at all.”

Warehouse allowed AUL to create a script that allowed item level locking to assure security of the data. With the new script, the same movement tasks that took 20 hours with the Basic program were being finish in 20 minutes. A 60 times advantage.

According to Don, “A quarter/year-end is our heaviest time, when we bring in the most cases. People predicted we wouldn’t make it. Last year, it took 3-1/2 weeks to do it, running programs every day and every weekend. This year, even with the larger case load, we finished it all in about two weeks. We’re heroes around here. Warehouse was a real lifesaver.”

DataBridger Really Cleans Up.

Now that the main AUL database has been cut down to size, Don Evans uses Warehouse regularly in what he calls “clean up” cases. These are cases that have to be moved from one database to another, from one case to another, even from one platform to another. He has yet to find a situation for which Warehouse wasn’t the answer. He’s even receiving requests from the company’s other IS Managers to show them how Warehouse can be used for similar projects in their areas.