Modelling Product Dimension when incoming fact records have missing lowest level

View previous topic View next topic Go down

Modelling Product Dimension when incoming fact records have missing lowest level

Post  abachwani on Wed Oct 14, 2009 7:09 pm

Hi All,

I am currently designing a Data Mart for the Marketing dept of an Insurance company. In the Product dimension we have four levels. The lowest one is 'Policy_Type' which rolls to 'Product' which further rolls to 'Product_Group' and 'Product_Group' rolls to LOB. The grain of the fact table is one record per Producer ( insurance agent who sells the policy) per policy_type. However the incoming fact row may at times not have the Policy_type and instead would have the 'Product' level. In order to design this I have two ways.

1) Split the Policy_Type to it's own dimension and have another dimension which has 'Product','Product_Group' and 'LOB'.
2) The other way I think this can be modeled is to have this all in one dimension. I can have surrogate keys for all the rows where we have the 'Policy_type' and then I would add more rows to this dimension which would have the 'Policy_type' as NULL and would contain one row for each 'Product'.So now if the incoming fact record has a 'policy_Type' then I would simply get the surrogate key for that row and if the 'Product' is available in the incoming fact record and 'policy_Type' is missing then I can look up the surrogate_Key for that row.

Please advise on which is the right way to approach this.

Regards,
AB

abachwani

Posts : 1
Join date : 2009-10-14

View user profile

Back to top Go down

Re: Modelling Product Dimension when incoming fact records have missing lowest level

Post  amarpal on Thu Jan 14, 2010 8:21 pm

Every distinct group of should have an additional column in the Product dimension table with PolicyType value as "Not Available".

Your Product Dim table should look like:

SurrogateKey LOB ProductGroup Product PolicyType

1 L1 PG1 P1 PT1
2 L2 PG2 P1 PT2
3 L3 PG1 P1 Not Available

3 L3 PG1 P2 PT1
4 L4 PG1 P2 PT3
5 L4 PG2 P2 Not Available

3 L3 PG1 P3 PT1
4 L4 PG1 P3 PT3
5 L4 PG2 P3 Not Available

amarpal

Posts : 3
Join date : 2010-01-14

View user profile

Back to top Go down

Re: Modelling Product Dimension when incoming fact records have missing lowest level

Post  ngalemmo on Mon Jan 18, 2010 4:47 am

If policy type is the lowest level, why isn't the dimension a 'policy type' dimension?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modelling Product Dimension when incoming fact records have missing lowest level

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum