Executives at a property and casualty insurance carrier issuing $700 million dollars in business annually did not have access to the disaggregated product and customer information they needed to run and grow their particular lines of business. The data warehouse had failed twice at the beginning of the previous year. Existing reports only provided an aggregate view of business that was not comparable across lines of business and did support the decision-making needs of executives from specific lines of business.

Solution

We utilized our Business Dimensional Lifecycle approach to design and delivery a data warehouse solution on time and on budget for the personal automobile product line. We designed the data warehouse’s technical architecture and developed Microsoft Visio diagrams to aid in its discussion. The goal was to transform mainframe flat-files into a relational database. The ETL tools proposed were Common Business-Oriented Language, EMC Corporation’s InfoMover, and Microsoft SQL Server’s Data Transformation Services.

The source platform was an IBM Operating System \ 390 mainframe, and we recommended that the target platform be a Microsoft Structured Query Language Server database on a Microsoft Windows 2000 server. Three 2000 servers would be necessary to support the warehouse’s lifecycle, and we outlined the hardware and software specifications for each.

Our consultants led the data modeling sessions and lend in-depth software development and database knowledge to the team as it sought to interpret and implement the IBM Insurance Information Warehouse (IIW) model.

When the two initial source systems had been mapped and development began in earnest, we transitioned to a programming team leadership role. We developed the team’s project plan for the next several months and began managing each member’s daily contributions.

We also implemented security both on Microsoft SQL Server and the Windows 2000 machines, and worked with the programming team to incorporate these changes into their code. We then established a version-control process, and instructed everyone on its use. We also managed the team’s unit and volume testing.

The first phase of the data warehouse (which included two mainframe source systems) was successfully implemented on time and under budget at the end of the year. At this time, we defined the project plan for the second phase, which was expected to take 9 months. We identified several ways to shorten the timeframe to 6 months, including the team’s need for an additional team leader and two more full-time developers. The project plan and the need for these three additional resources were then presented to the CIO and approved.

Now that all of the platforms, standards and processes were in place, we began training another manager to lead the data warehouse project – even while we continued to manage several outstanding items from the first phase and the entire development cycle of the next two source systems

Results

The carrier estimates that the product and customer knowledge, efficiency and flexibility provided by this new “decision support system” for executives amounts to $10 million in savings. These savings are derived from:

1. Better and faster executive decision-making.

Executives can quickly and easily access reliable, accurate information for their particular lines of business. The high-level figures can be rolled-down to a detailed transactional view for a comprehensive, in-depth diagnosis of product sales and customer information.

2. Improved data quality.

The enterprise data is organized and structured so that it's easier to navigate and mine for an executive’s specific line of business. The operational detail has been tailored and “scrubbed” to enable executives to plan and manage their sales, product mixes and profitability. The system’s security features allow the carrier to determine who can mine the value that is found in its enterprise warehouse of claims and policy information as well as how.

3. Enhanced flexibility and modularity.

Because the data warehouse design and architecture can adapt to changes in the carrier’s underlying operational systems, there is less risk of business disruption if its original systems are changed in the future. In fact, the client is already planning to expand the scope of the enterprise data warehouse to include its life and commercial automobile products information.

Other Client Successes