Modelling Product Dimension when incoming fact records have missing lowest level

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

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

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