Covered Item Dimension

[aravindk]

We are trying to design a Covered Item dimension for an insurance company. If we want to capture three different dates in this dimension, effective date & expiration date of an item and the transaction date, how can this dimension be organized so that users can quickly figure how many items were active on a given date?

A customer might call in today and ask his insurance be cancelled on an item as of a past, present or a future date. How can this dimension be designed so that queries such as "how many items were in-force on a given date?"

Thanks in advance,

 

[TJRTech]

Seems to me you need at least two dimension tables and an inner fact table.

Let's call the dimension tables: "InsuranceItem" and "Customer".

Then, the inner fact table could be called "CoveredItems", and would have the following columns:

InsuranceItemID - FK into InsuranceItem table
CustomerID - FK into Customer table
EffectiveDate - Date/time item became effective
ExpirationDate - Date/time item expires

I am not sure what you mean by a "transaction date", but using the above model as described it is easy to write a query that given a customer ID and a date returns all the InsuranceItems active during that date.

I hope this helps.

TR

 

[aravindk]

I would like to add a transaction date in this mix, which makes this a little too complex. For example for an item the effective date is 05/01/2002 and the expiration date is 10/01/2002, entered into the system on, lets say 05/01/02. Lets say on 11/01/2002, this item was made expired on 09/01/2002, the records in my table would look like as follows:

key natural_key eff_dt exp_dt tr_dt
1 001 5/1/02 10/1/02 5/1/02
2 001 5/1/02 9/1/02 11/1/92

If I want to know as of 6/30/02 and
active items then the following sql would work:

select max(key) from dimn where tr_dt <= 6/30/02 and exp_dt >= 6/30/02 and eff_dt >= 6/30/02

This is fine. But if I want to get more information like all the item attributes for the keys returned then this query becomes more complex. If I want to keep this sql part simple, is there a way to improve the dimension design? I am wondering if there is a different way to handle this situation.

Thanks for trying to help me!

 

[johnherman]

If I understand what you have presented, then this seems to be the best way to handle this Slowly Changing Dimension (as a type 2).

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[TJRTech]

Okay...I understand better now what you are trying to do.

So, I change my suggestion to the following:

Make EffectiveDate and ExpirationDate attributes of the InsuranceItem dimension.

Keep the Customer dimension.

Add an "InsuranceState" dimension. This table would bave static entries for the various "states" an insurance item can go through during it's lifetime, things like: "New", "In Underwriting", "Covered", "Expired",
"Lapsed", etc (you know your business, I don't).

Rename your fact table to be InsuranceItemState. The ItemState would have FromDate and ThruDate measures and FKs into each of the dimension tables, including InsuranceState.

This approach will easily and clearly give you the lifecycle of insurance items and you can see when they go in and out of various states; and you have effectively relinquished the EffectiveDate and ExpirationDate as attributes of the InsuranceItem.

I hope that helps.

TR

 

[aravindk]

I agree with you. It is indeed a SCD type 2.

 

[aravindk]

TR,

Thank you. I am going to go by your suggestion with a small change. Instead of making fromdate & todate as a measure, I am going to make them as fks to date table which will make this a factless fact table. For some strange reason I was reluctant in using a factless fact table in my design, but i think now, that is the only way to make the reports easier.

Thanks!