In-Depth

Nationwide spreads the warehouse wealth

• By Michael W. Bucken
• March 27, 2003

ADT's 2003 Innovator Awards

Data Warehousing Winner

Building a data warehouse can be a long, tedious process. Just ask the IT folks at Nationwide Mutual Insurance Co., Columbus, Ohio, who began work on a data warehouse in 1999, and have continued to modify and update the system to combat user criticism and bring its potential to more users.

One of those follow-up projects -- dubbed Aries -- is the winning app in the Data Warehousing category in this year's Innovator Awards competition. The Aries project, in short, sought to and brought together separate and mutually exclusive reporting apps built along with the original Nationwide data warehouse. The two primary applications -- revenue (premium) and cost (claims) -- were conceived and developed by separate departments with separate leadership and unique end-user requirements.

"[The Aries project] was an integration project for reporting purposes," said Cyndi Blake, Aries project manager. The 18-month project sought to load data compiled by both primary and other applications into a single summary layer for reporting, she said.

Front row: Curtis Withers, Janet Hendrix, Jian Tong, Cyndi Blake, Marian Burkart Back row: Dave Fawcett, Todd Leach, Felix Dashevsky, Michael Hughes, Joseph Cronin Jr., Joan Brownlee, Greg Hodge, Julie Kurtz, Pankaj Sodhi

In business terms, the goal was to first gain a competitive advantage in the auto insurance market by providing tools to financial and auto product managers to identify and manage drivers of business operating results, she said.

Blake said the development team faced substantial risks even before work began on the project. "It was difficult to get the investment" of funds needed to carry out the work, she said, because of economic conditions and a skepticism about the data warehouse concept due to the limited user base served by the original system. That system required constant programmer intervention to access all but the most simple data for managers, resulting in a backlog of requests that could take weeks or months to fill. Many turned to an older legacy reporting system for help.

In addition, changes in long-held legacy methodologies and the addition of complex tools that require substantial training ran the risk of resistance from users. Blake said a data warehouse advisory team was created to gain user input into the methodology changes.

In a significant logistical and political challenge, the developers brought together a variety of Nationwide groups -- underwriting, sales, claims, actuarial and finance -- to agree on project priorities, data content and metric definitions before the project started.

And the development team worked on the premise that the system had to support at least 250 cross-functional users, all with access to the app's full capabilities.

For the most part, Blake said, the Aries system has overcome credibility issues by allowing users to more effectively manage underwriting processes and thus has reduced the loss ratio.

Finally, the IT unit was successful in convincing corporate management that the project could not be judged by traditional ROI criteria, as a solution that lets users "know now what they didn't know then" cannot be justified by traditional criteria such as efficiency gains, reduced CPU utilization and hardware/software cost savings.

The team included developers of all stripes: experienced data warehouse developers, as well as experienced and novice developers new to the paradigm.

Officials said the Aries system has provided quantifiable expense savings by allowing a more focused use of underwriting resources in reviewing policyholder claims at the time of policy renewal; but for the most part, they agree that a data warehouse cannot quantify specific return on investment results.

The Aries system required significant hardware and software resources, but Blake said that for the most part, the team "was able to use existing tools" already used by Nationwide.

For example, the team utilized the NCR Corp. high-performance Teradata database and utilities, and the MicroStrategy Inc. Web and Desktop query and reporting toolsets used in the original data warehouse. Blake said the firm did evaluate alternatives to the MicroStrategy offerings, but knew Teradata was needed from the start due to the 1.3 terabyte size of the Aries system, which includes a table of 6.4 billion rows. (The largest table previously in the database was 1.4 billion rows.)

The reporting tools can provide canned reports, standardized reports and full ad-hoc reporting capabilities against Nationwide's new integrated database containing auto policy and claims information.

The Aries project utilized a Lotus Notes-based project management system to facilitate project administration, and the Microsoft Project toolset to construct and administer the project plan. The LoadRunner toolset from Mercury Interactive Corp. was used for performance and stress testing by the development team.

Application profile

Project: Aries

Purpose: To bring together separate and mutually exclusive reporting applications built with the original Nationwide data warehouse.

Benefits: Aries provides access to integrated premium and claims data, allows users to drill down to necessary detail levels, and can be accessed across business units.

Tools: MicroStrategy Web 7.1.7, MicroStrategy Desktop, SQL Server for Metadata, Teradata database

Development team: Cyndi Blake, Greg Hodge, Janet Hendrix, Jian Tong, Todd Leach, Pankaj Sodhi, Julie Kurtz, Felix Dashevsky, Deepak Joshi, Monika Som-Cushman, Joan Brownlee, Jim Downey and Tobi Zappe

Keane Report

Nationwide's Aries data warehouse application was developed with three primary objectives in mind: access to integrated premium and claim data that could be reconciled to the operational environment; drill down from high-level financial results to detailed data; and access to data that crosses all organizational business units and geographic boundaries. The ultimate goal was to develop competitive advantage by providing Nationwide's product managers with tools to identify and proactively manage the drivers of business operating results.

Aries' innovation lies in its ability to provide a wide range of features and functionality, including canned reports, parameterized queries, and full ad-hoc reporting against a wide range of metrics at both the detail and aggregate level. Using MicoStrategy Web 7.1.7, Aries features Web-based report sharing across a range of business users, levels of security and authorization tied to user group types, as well as an integrated meta data dictionary to define business attributes, metrics and calculations.

Nationwide managed and identified risks, including potential data credibility and tool usage issues, by making process improvements in the methodologies for measuring company KPIs and by involving the right people, including business area and business intelligence tool vendor representatives.

Team Leader: Jack Fuchs, Manager, Architecture Services, Keane Inc.

......

Click here to read about the finalist in this category, "Acquisitive ING links silos", or click here to go to the Innovator Awards home page.