Policy Customer/Address dimension question

[scabral]

Hi,

I'm trying to decide how to build a customer dimension with address information for an insurance policy data mart. Right now, each policy has a property address that has a 1 to 1 relationship with the policy. For example, I may be insuring 2 different properties with 2 different addresses and 2 different policy numbers. So there is a 1 to 1 relationship between policy and property address.

Now Mailing address could be the same or different for each policy. So both my properties have different property addresses, but I want all of my mail to go to the property where I am living, so I will only use 1 mailing address for each of my 2 policies. However, I may want the mail for policy #1 to go to 1 address and mail for policy #2 to go to a totally different address. So in this case, we could have different mailing address for each policy that is insured.

My issue is how I should model this data. At first, I was thinking of creating a Customer dimension with a Customer_Num as the natural key and store both the property and mailing address for each customer. However, this will not work because depending on how many policies the customer is insuring the property addresses will be different for each policy.

I was also thinking of creating a Policy dimension (right now I have Policy_Num as a degenerate dimension in my fact tables), and store the property and mailing address for each policy in this table. This would give me a 1 to 1 relationship between Policy Dimension and fact, so not sure if that is the best approach.

has anyone ever had to model something similar?

thanks
Scott

[BoxesAndLines]

If you don't already have a geography dimension, you have probably already arrived at the best solution. A geography dimension with two relationships for property and mailing address is the other option.

[scabral]

Thanks Boxes,

What do you mean when you say "a geography dimension with two relationships"?

[BoxesAndLines]

One relationship (FK) gives you the property address. The other relationship gives you the mailing address.

[Kumarpunna]

Scott
pol1 sur_key,Cust_key,perference_key(repeation of surrogate key)
123 1,abc, 1
234 2,def,1
In the above case when in fact of premium of
$x ,pol:123,addr:1
$y,pol:234,addr:1
-----------------------------------
123 1,abc, 1
234 2,def,2
In the above case when in fact of premium of
$x ,pol:123,addr:1
$y,pol:234,addr:2

let me know if you have any more questions
Thanks
Kumar

[ngalemmo]

First thing is you have two different entities: customer and policy.  These should be different dimensions.  

As far as what to do with address, it is a judgement call.  One way is to simply store the appropriate address in the appropriate dimension.  Customer would have its address and policy would have the property address. If you do this, policy needs to be a full dimension so you have a place to store the address.

Another way to do it is to have a 'geography' dimension (B&L's suggestion), which is simply a dimension of addresses with various geographic and demographic attributes (census, etc...) related to the location.  This type of dimension is handy if you plan to do analysis based on geography.  If you go this way, customer and policy would contain FKs to this dimension.  To avoid snowflaking, you can use the fk in the customer or policy dimension to populate an fk column in the fact for property address and/or customer address.

If you go this way, you can keep policy as a degenerate, however, you should maintain a lookup table on the side to store the policy number and address key so that ETL can populate the property address key in facts related to the policy (sales, claims, etc...).

Note: A full blown geography is usually more than just the address dimension.  There are typically hierarchies related to the various types of demographic and location data with related dimensions.  But, it all starts with the address dimension.

[Kumarpunna]

Sorry I was not clear of what I was saying

POLICY_DIM
SurrogateKey,BussinessKey
1,P1
2,P2
3,P3
4,P4
-----------
CUSTOMER_DIM
SurrogateKey,BussinessKey,PerferenceKey(this is tell the address perference of customer)
10,C1,10 <- this is same surrogate key that was generated during generating of the customer_dim table
20,C2,10
100,C10,100
200,C20,200
-------------------
In ETL when you are populating you fact which has a Perference key try to come to Customer_dim and pick up the PerferenceKey
Fact
SurrogateKeys, Amounts
(policy,customer,perference),$$
1,10,10 (P1,C1,C1),$$
2,20,10(P2,C2,C1),$$
3,100,100(P3,C3,C3),$$
4,200,200(P4,C4,C4),$$

let me know
Thanks
Kumar

[LAndrews]

Try to not get too complicated.

Here's my simplified understanding of the situation

- You have two addresses for the customer (customer_address, customer_mailing_address)
- You have two addresses for the policy (property_address, property_mailing_address)
- Your fact tables relate customers and policies.
- addresses may be the same or may be different

As ngalemmo suggested, the decision on how to store the addresses is up to you.

Option 1 : add the address attributes to the customer and policy dimensions
Option 2 : create an address dimension, and then place multiple FK's on the customer and policy dimensions (snowflake approach)
Option 3 : create an address dimension, and place multiple FK's on the fact tables

The decision will be made based on the realities of your situational requirements. For example, if the policy mailing address can change, and that change is suppose to be applied to all facts, then you probably would rule out option 3.

clear as mud?

[timpane]

If you go this way, you can keep policy as a degenerate, however, you should maintain a lookup table on the side to store the policy number and address key so that ETL can populate the property address key in facts related to the policy (sales, claims, etc...).

[Sponsored content]