Dimensional Modeling for Insurance Industries 1

[monsky]

Has anyone here designed enterprise data warehouse dimensional models for insurance industries?

 

[johnherman]

Chapter 15 of Kimball's book "The Data Warehouse Toolkit, 2nd edition" covers P&C insurance. I also have personal experience with Medical Insurance.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[monsky]

hi john,

Unfortunately, I still don't have the book although I got the Lifecycle Toolkit. My experience with data warehouse development is still very young.

Okay, I'm starting out with a star schema approach with my model but I'm thinking if I should create a fact table out of a policy master file or treat it as a dimension. Here's the basic structure:

policy number
currency
effective_date
policy_status
loan_status
billing_mode
line_of_business
policy_plan_code
market_segment code
face_amount
modal_premium_amount

This table acts as a control table where the policies gets updated. I'm wondering if I should create a fact table and put the face_amount and modal_premium_amount in there as my base facts. If I do this I basically have a one-to-one relationship with the a policy dimension and policy fact which I'm worried will affect the performance of my queries.

I would appreciate any suggestion you might provide.

 

[johnherman]

Kimball (and I do recommend you buy the book - make sure it's the second edition) details three major business processes for Insurance: Policy Transactions, Policy Premium, and Claim Transactions.

Policy Transaction has one fact table, Policy Transaction Amount with dimensions of Time, Policyholder, Coverage, Covered Item, Employee (who did the transaction) and Policy.

Note that policy is a dimension while transactions against the policy are facts.

Policy Premium also has one fact table with two facts: Written Premium Revenue Amount, and Earned Premium Revenue Amount. The Earned amount is the written premium minus expenses (exclusive of claims) incurred in acquiring and maintaining the account, i,e, the cost of doing business.
This fact table has the same dimensions as above.

The final Businss Process is Claims Transactions with three fact tables, Claim Transactions, Claim Accumulating Snapshot, and Accident Event. The dimensions are the same except that there is no Employee associated with the Accident Event. In addition, there are dimensions for Claim, and Claimant (or Claim Party(ies))

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[monsky]

Thanks for the input. Basically one report requires me to present a distribution of policy count with a similar format below:

Inforced NewBusiness Lapsed
0-13 90 20 30
14-27 150 60 70
28-40 180 90 10

There are other reports that requires to present the distribution in terms of face amount, billing mode or line of business.

 

[johnherman]

The report example you show consists of counts across dimensions. Counts are derived measures (or facts). There are really very few facts/measures available in the transaction system.

Other than counts and other summary fields and statistics, there are very few measres: money collected (premium) and spent (claims, expenses).

The only other facts are facts associated with events such as date of claim, date of accident, date claimant paid, date vehicle inspected, etc.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[tdimarco]

I have personally designed enterprise data warehouse dimensional models for 7 insurance carriers and currently working on number 8. On average our customers are maintaining approx. 15 different dimensions on how they analyze their business. My current design will incorporate close to 50 dimensions in order to allow their actuaries to perform detailed rating formula analysis.

Typically, we store all policy and claim transaction detail at a very low level. We then design fact tables around the different subject areas. They could be loss analysis (i.e. Incurred losses, LAE, ULAE, outstanding reserves, paid losses, etc.), premium analysis (i.e. written premium, net written premium, earned premium, unearned premium, inforce premium, etc.) and policy analysis (i.e. inforce policy count, policy count, etc.)

Obviously, there are differences in the dimensionality when analyzing claims vs. policy related measures. When calculating a loss ratio, for example, only the common dimensionality between the earned premiums and incurred loss is useful during analysis.

I would be glad to share any other experiences...

 

[ffoley]

You should read the data model resource books available from Len Silverston. It shows the overall model that could be used to build a 3N form enterprise datawarehouse layer. If you are looking to build an enterprise datawarehouse by building marts and snapping them together ala Kimball, you should first look at what is a dimension and fact. Policy (the physical paper that represents the agreement between the customer and the insurance company to have a specific coverage put in place) itself in the insurance world can be thought of as the product dimension.
The design of the star is based on the measures (KPIs) required for the business to track and measure their business, not the physical implementation of a report. All front end query tools can do the types of multi-dimensional reporting. The grain is the key, doesn't matter whether you are building a warehouse layer or straight into the marts, keep the lowest level of details available. You can always aggregate up but you cannot always allocate down.

Currently building Insurance warehouse and associated marts and I was also wondering where the product was when I started.

Hope it helps