The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses (Includes CD-Rom)

Read an Excerpt

Chapter 9: Insurance

We will imagine that our insurance company is a $3 billion property and casualty insurer for automobiles, home fire protection, and personal liability. There are two main production data sources: all transactions relating to the formulation of policies, and all transactions involved in processing claims. Based on extensive interviews of claims processing managers, field sales managers, financial managers, and senior management, we conclude that the insurance company wants to analyze both the written policies as well as the claims. They want to see which coverages are most profitable and which are least profitable. They want to measure profit over time by covered item type (i.e., which kinds of houses and which kinds of cars), state and county, demographic profile, underwriter, sales broker and sales region, and event. Events are usually called catastrophes in this industry. The desire to see profit implies that both revenues and costs can be identified and tracked. The insurance company wants to understand what happens during the life of a policy, particularly when a claim is processed. The time from when a claim is first made to when the first payment is made is an important measure of the efficiency of the claims handling process. This can be measured only by looking at the detailed transactions.

In this chapter we will develop the design in the same way we developed the household data warehouse for the bank. We will start with the central dimensional schemas for policy creation and claims processing and then embellish them to handle monthly snapshots, heterogeneous products, and demographic minidimensions.

POLICY CREATION

We will assume that a policy is a "header" for a set of coverages sold to the insured party. Coverages are really the products that insurance companies sell. Homeowner coverages include fire, flood, theft, and personal liability. Automobile coverages include comprehensive, collision damage, uninsured motorist, and personal liability. In a property and casualty insurance company, such as the one we are modeling, coverages typically apply to a specific covered item, such as a particular house or car. Both the coverage and covered item are identified carefully in the policy. A particular covered item will usually have several coverages listed in the policy. We will assume that a policy can contain multiple covered items.

A policy is sold to an insured party by an agent or a broker. Agents are either employees of the insurance company or may be independent. Brokers are independent. Before the final policy can be created, a rater in the insurance company determines the rate that will be charged, given the particular coverages, the particular covered items, and the qualifications of the insured party. The final step of approval is made by an underwriter, who takes the ultimate responsibility for doing the business with the insured party. Typically the agent (or broker), rater, and underwriter are all different people.

We assume we capture the following transactions in the production policy creation system:

• Create Policy; Alter Policy; Cancel Policy (with Reason)
• Create Coverage on Covered Item; Alter Coverage; Cancel Coverage (with Reason)
• Rate Coverage; Decline to Rate Coverage (with Reason)
• Underwrite Policy; Decline to Underwrite Policy (with Reason)

The dimensional framework for the policy transaction fact table consists of the following:

• Transaction date
• Effective date
• Insured party
• Employee
• Coverage
• Covered item
• Policy
• Transaction

The transaction date is the date when the transaction was entered into the production system. The effective date is when the transaction is legally effective. These two independent dimensions can be implemented via a single physical table, which is declared via the SYNONYM construct to be two different tables as far as SQL is concerned, as described in Chapter 2.

The insured party is the customer. In all probability, the insured party dimension is a ditty customer dimension, in that no really serious attempt is made to identify previous instances of an insured party on other policies. The insured party can be multiple people, such as a person and their spouse, or the insured party can be a business entity. In general, insured party is a big dimension, which in the case of our $3 billion insurance company probably has a million or more entries. Later in this chapter we will definitely be using the big-dimension techniques on insured party that we developed in Chapter 6. . . .

Read More