Building A Better Data Warehouse

In preparation for a corporate strategy meeting, a claims executive for Cleveland-based Ohio Bureau of Workers' Compensation (BWC) recently embarked on a data-finding mission. The objective: generate specific detail on all company-wide workers' compensation claims associated with railroad-related back injuries filed in Cleveland over a two-year period.Drilling down this deep for information would enable the executive to pinpoint and correct some of the inefficiencies associated with these types of claims. Once exposed, BWC could use the data to reduce its policy loss ratio and pass the savings along to policyholders.

At most insurance companies, a data-seeking endeavor like this is a Herculean task. It invariably means extracting information from disparate databases scattered across multiple corporate divisions. It also requires an IT department to write new software code to unearth data from multiple layers of information stored in various databases. It's a process that often takes weeks or months to complete.

However, because the BWC has an extensive data warehousing program, employees can bypass these painstaking steps. Data warehousing takes disparate data stored in mainframe systems across a company and extracts, cleanses and transforms it to achieve standardization. Once loaded within a central repository, end users can extract "slices" of data via a front-end application known as a data mart.

In the end, data warehousing has the ability to garner both strategic and operational gains for end users, streamlining such areas as customer relationship management (CRM) and supply chain management.

Faulty towers

By logging on to BWC's desktop-based Passport solution, developed by Eden Prairie, Minn.-based Apertus Carleton Corp., the claims executive was able to conduct dimensional data modeling via the front- end data mart built on the warehouse platform. Rather than waiting weeks, the user obtained the data within a few hours.

But as data warehousing in the insurance industry goes, BWC's Passport technology is an aberration. Industry experts say data warehousing has been an exercise in futility, particularly among large carriers who over the years have accumulated mountains of data in multiple databases built on different platforms.

To observers, it's a troubling irony that an industry that relies so heavily on information hasn't yet found a failsafe way to unleash its power.

"If ever there was an industry with a need for better tools to organize and access data, it is the insurance industry in the 21st century," says Jack Gohsler, senior vice president for the Hartford, Conn.- based market research company Conning & Co.

"It seems the perfect match of application and need. Insurers are flooded with data and many companies have trouble figuring out what to do with it all. Carriers simply need to learn how to harness its power. An effective data warehouse can help them do just that."

But an effective data warehousing program has been an elusive undertaking. Many of the industry's glaring weaknesses were exposed in a report released last fall by Conning & Co. entitled "Data Warehousing and Mining in the Insurance Industry: Floods of Information, Fountains of Knowledge."

Performed in conjunction with approximately 40 insurance companies and encompassing 60 separate interviews, the study chronicles numerous warehousing initiatives that were either terminated or scaled back. Conning states that as many as 90% of all insurance-related data warehousing initiatives are ultimately unsuccessful. "Companies have spent millions of dollars on data warehouse projects that either were never completed or were never used to their full potential," Gohsler says.

Success stories

When it comes to the perils of warehousing, mid-sized carriers such as BWC have had a better time vaulting the hurdles, while the largest carriers have struggled mightily, industry experts say. Although large carriers have deeper pockets, they also have more data integration challenges to reconcile.

Nevertheless, several large carriers are proving that data warehousing can be pulled off successfully. San Antonio-based USAA, a property/casualty carrier with revenues topping $55 billion a year, personifies "the poster company for successful data warehousing in the insurance industry," says Todd Eyler, senior research analyst for Forrester Research Inc., Cambridge, Mass.

"USAA spent an enormous sum to build the program, and they're now completely wired. They got the senior-level support and they enlisted people from across the enterprise to participate in program development. They also exhibited patience, realizing that the return on investment doesn't happen overnight."

Other large carriers have been lauded for successful warehousing programs. Columbus, Ohio-based Nationwide Insurance, for example, christened its data warehousing program in 1998 with much fanfare. Starting with fewer than 100 internal users, Nationwide-which markets homeowners, auto and property/casualty coverage-now has an internal user network of more than 300.

The program was recognized last year by Gaithersburg, Md.-based The Data Warehousing Institute (TDWI) for application excellence.

"Our goal was to evolve from a company that provided static data on a monthly basis to one that made data available on a daily, self-service basis," says Ronald Edgington, officer of business intelligence solutions at Nationwide. "We had to take information stored in data silos and integrate it broadly across the corporation."

Detailed information

Hartford, Conn.-based Travelers discovered that before the advent of data warehousing, the "granular level of detail (at Travelers) couldn't be obtained to yield improved decision making and profitability," says Jim Wellman, director of MIS for personal lines business at Travelers. "Data warehousing enables us to look at underlying detailed policy and claims information and ultimately improve these areas."

The results of Travelers' program are bearing fruit on several fronts. In one instance, an end user group cut 25% off the time it typically takes to perform rate filings due to data warehousing.

Employing the PowerPlay front-end data warehousing application developed by Burlington, Mass.- based Cognos Inc., Travelers employees are able to perform their own multidimensional analysis. PowerPlay enables them to create reports for online analytical processing (OLAP) in a Web, Windows or Excel environment.

As carriers such as Travelers have discovered, participation in data warehousing comes with a double- edge sword. Although data warehousing enables a carrier to look at underlying and detailed policy and claims data, this can only be executed by centralizing the information.

When Nationwide made a commitment to data warehousing in 1997, the layers of complex and fossilized data within its legacy systems served as just one major challenge. Compounding the matter was the fact that data in most legacy systems is "very transaction-oriented and compartmentalized," says Edgington.

Nationwide identified 10 original source systems of data that it had to condense into a single source, Edgington explains. "We started by evaluating product systems, such as auto, fire and property data. We also looked at it from a downstream application standpoint, such as report-oriented data. We had to find a way to distill it all."

After receiving a heavy dose of senior-level support, Nationwide formed a business impact team whose primary function was to classify and assess data before beginning the process of integrating it within a central repository.

Embarking on an 18-month effort encompassing multiple business areas, such as marketing, claims and pricing, Nationwide's objective was to construct a data warehouse built on Dayton, Ohio-based NCR Corp.'s Teradata Warehouse platform that could compress and standardize 500 or more gigabytes of data within a single source.

Density of data

With so much data to deal with, Nationwide's development team recognized the likelihood that discrepancies and errors were great. Much of the data carriers have accumulated over the years has become an albatross around their neck.

"Insurance carriers have gotten to the point where there's layer upon layer of complex and fossilized data within their legacy systems," Eyler says. "I would estimate that 30% to 50% of the data stored in a legacy system is corrupt and shouldn't be included in a data warehouse, but is included anyway."

After coming to initial conclusions about the degree of data it would-and would not-store into the warehouse, Nationwide rolled out the product to a limited group of end users during a test phase.

The users extracted data via the company's Web-based relational online analytic processing (ROLAP) tool set, and then provided critical feedback about the gaps within the warehouse. ROLAP stores data and aggregates it in a relational database management system.

A so-called "SWAT" team was assembled that consisted of data and reporting specialists. The team, Edgington says, worked through nine alternatives and determined that a "snowflake schema" would provide the best solution to eliminate these discrepancies.

With snowflaking, some of the fields of the dimension tables are split off into separate tables. This achieves a higher level of normalization, but makes the database design more complex and can reduce the performance of the business intelligence tools. A graphic of a snowflake schema looks like multiple stars connected together, hence the appearance of a snowflake.

Working around the clock, the SWAT team developed newly designed data tables to balance and reconcile the data warehouse so it could perform at 100% efficiency.

Double the effort

Similar to Nationwide, Travelers faced the task of distilling five to six years of detailed policy and claims data within a central repository as it constructed a data warehouse for its personal lines business.

Realizing the amount of data that was involved, Travelers thought it prudent to construct not one but two warehouses-one for auto and the other for property business.

The first to be built was intended for auto policies. Built from a mainframe-based system, Travelers took auto policy data from six independent mainframe systems and two from auto claims systems, Wellman says. In 1996, Travelers proceeded with a data warehouse for its property policy lines that was built on a server-based platform.

"On the property side, we took data from four different policy systems and one claims system," Wellman explains. "Through it all, each one of these interfaces had to be built by mapping, transforming and moving data into the warehouse."

The Travelers team was under the gun to produce rapid results. "We had six months, a total of six individuals and a limited budget to build a strong case for data warehousing," says Wellman. "If after six months there was enough proof of success, we were able to take it to the next step."

Travelers now has a data warehouse that consists of between three and four terabytes of data within the two personal lines warehouses.

The quality assurance of a data warehouse initiative doesn't cease once it's built. To many industry observers, a robust data warehouse is rendered useless without the extraction efficiencies of a flexible and scalable data mart tool.

"Companies stuff a whole lot of data into a warehouse and nine times out of 10 they don't want to deal with it directly-there's too much there and it's awkward," says Kelly Cannon, vice president for operations solutions at Nationwide. "A data warehouse is built as an efficient place to store data but not an efficient place to retrieve it. The data mart is the front-end retrieval application."

At Travelers, the carrier's two data warehouses are supported by nine data marts. "The data mart provides a snapshot for the individual business groups," says Wellman. "We build data marts based on functional needs.

"Underwriting managers might want just a slice of information that has to do with the distribution of business and trends in demographic marketing. Actuaries may want detail and history on quarterly accident occurrences. The detail is drilled down so an actuary can extract all the rating variables to get a sense of profitability of the business by looking at underlying factors," he says.

Different levels of users

When Nationwide employees embark on a data search, they log into a password-protected desktop application called Galaxy. If data warehousing novices want to access Galaxy for future use, they can log onto a corporate intranet to schedule training through the company's IT help desk.

After lodging the request, employees are sent an e-mail with an "install" icon embedded within the message. This enables them to click the icon where an automated training setup screen is installed on their desktop.

Training takes a day or two, and is predicated on the degree of data mining employees plan to conduct. "We have different levels of users," Edgington explains. There are basic users and what he calls "power users," who require much more detail and data modeling for reports.

"Within a data mart, some reports may have five parameters to choose from while others may have 20," Edgington says. Power users would be inclined to launch data queries that contain more data tables and modeling parameters. Many of these parameters have relationships that only those with more extensive training can identify and understand.

Similar to Nationwide, Travelers users must first enter a user name and password to enter a Microsoft NT server environment to access one of the nine data marts.

Stressing the importance to "walk before we run," Nationwide's Edgington says the company has limited access to data warehousing for its employees, either internal or field personnel. Travelers has taken the same approach. Down the road, both carriers may offer agents and brokers access to their data warehouses.

Developing strategies

Much has been made about the power of data warehousing, but what does it really accomplish?

Users state that it has a two-fold purpose: to improve both the strategic and operational aspects of a business.

When executives at the Ohio Bureau of Workers' Compensation can identify patterns occurring within its claims unit, this in turn enables them to make modifications to underwriting and pricing strategies.

As more carriers each year identify pricing and claims as intertwined elements of the operation that must be linked, data warehousing is the means to that end. "Let's say a policyholder has an outstanding claim at policy renewal time," says Forrester Research's Eyler. "The lack of integration between a claims database and underwriting data means that the policy could be renewed without reflecting the outstanding claim. This is an operational breach that can be prevented through data warehousing."

When it explored data warehousing, Nationwide saw it as a vehicle for reducing its claims loss expenses. "We felt we had to understand what the real drivers of loss were so we could better manage claims," Cannon says. "Last fall, we introduced warehousing to our claims field organization and they have reacted enthusiastically to it."

Bracing for warehousing

Why not? By logging on to the front-end ROLAP application, Nationwide's claims examiners can evaluate costs, life cycle of claims, average net claim payment and various business metrics, Edgington says. On the auto side, they can examine written premium, earned premium and vehicles in force. Users can construct queries against these data fields ultimately to make adjustments with corporate strategy.

With so much at stake and competitive pressure mounting, the deliberate expansion of data warehousing by insurance carriers will have to accelerate, industry analysts insist. Many don't yet regard the technology as business-critical, but they will adopt a greater sense of urgency once they see the correlation between data warehousing and fiscal responsibility.

The projection is that data warehousing will permeate the industry over the next two years. In 1999, initiatives were eclipsed by Y2K priorities, says Kimberly Harris, a senior analyst with Gartner Financial Services Inc., Durham, N.C.

Last year was also a disappointing year for warehouse adoption. But Harris predicts that by 2002, this topic will have "full attention of carriers as they get past front-end Web initiatives that were done to appeal to shareholders."

MORE TO COME

"We have barely scratched the surface of data warehousing," says Nationwide's Cannon. "Insurance is an information-based business. A three-terabyte warehouse is a very large entity. As we learn to manage it more effectively, we'll reap more value. As we learn to exploit the front-end tool sets and learn to ask the right questions, we're going to see world-shaking changes in our ability to effectively underwrite risk and manage losses."

Insurance carriers that want to forge ahead with data warehousing have several plusses in their favor. "I see a much smarter insurance CEO that grasps IT much better than in the past-ones that look at technology not as a hindrance but as a leveraging tool," says Conning & Co.'s Gohsler.

"The technology tools are the second reason that data warehousing efforts will improve. The tools are far more robust and sophisticated than in the past," he says.

Even with better tools and more savvy CEOs, the watchword for data warehousing will continue to be "patience."

"Data warehousing does not offer instantaneous gratification," says Diana Beecher, CIO for Travelers. "It took us three to four years to reap the benefits of data warehousing in our personal lines business. Data warehousing has been saddled with a bad reputation due to unrealistic expectations."

For reprint and licensing requests for this article, click here.
Analytics Policy adminstration Data and information management
MORE FROM DIGITAL INSURANCE