Mapping heterogeneous attributes

View previous topic View next topic Go down

Mapping heterogeneous attributes

Post  bobb13 on Thu Feb 23, 2012 2:09 pm

I'd like some opinions on if it is acceptable to have a dimensional model where common/shared attributes are included in a dimension table while "line of business" specific attributes are placed in separate dimensions but with the same key as found in the common/shared dimension. Doing so, allows the separate dimensions to be able to (left) join directly to the fact table OR (left) join to the main dimension table. In addition, if other alternatives have more merit.

Option1 - common/shared attributes are included in a dimension table while "line of business" specific attributes are placed in separate dimensions but with the same key as found in the common/shared dimension.
Fact
Policy_key
Member_key
Date_key
fact1
fact2
....

Policy Dim
Policy_key
Common_attribute1
Common_attribute2
...

Health_Policy_Dim
Policy_key
Health_specific_attribute1
Health_specific_attribute2
...

Dental_Policy_Dim
Policy_key
Dental_specific_attribute1
Dental_specific_attribute2
...

Option 2 - avoid the common/shared attribute Policy Dim table and just include those in each of the "line of business" specific dim.

Fact
Policy_key
Member_key
Date_key
fact1
fact2
....

Health_Policy_Dim
Policy_key
Common_attribute1
Common_attribute2
Health_specific_attribute1
Health_specific_attribute2
...

Dental_Policy_Dim
Policy_key
Common_attribute1
Common_attribute2
Dental_specific_attribute1
Dental_specific_attribute2
...

Option 3 - the Health and Dental Policy Dims have their own Keys (Health_Policy_key, Dental_Policy_key) on the Fact table.
Fact
Policy_key
Health_Policy_key
Dental_Policy_key
Member_key
Date_key
fact1
fact2
....

Policy Dim
Policy_key
Common_attribute1
Common_attribute2
...

Health_Policy_Dim
Health_Policy_key
Health_specific_attribute1
Health_specific_attribute2
...

Dental_Policy_Dim
Dental_Policy_key
Dental_specific_attribute1
Dental_specific_attribute2
...

bobb13

Posts : 1
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Mapping heterogeneous attributes

Post  ngalemmo on Thu Feb 23, 2012 3:27 pm

Nothing wrong with splitting it out, it is a common approach when different types have significantly different attributes. Keeping the same PK across tables is the best approach. However it is unusual to perform outer joins to the sub-type dimension tables.

In general, if someone was to look at multiple types of policies, they are usually restricted to those attributes common across policies, while a query that uses attributes for a specific type of policy would only see rows of that type.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Mapping heterogeneous attributes

Post  hang on Thu Feb 23, 2012 7:58 pm

Basically option 2, but with an additional supertype dimension that contains the same SK value with only attributes common to all subtypes. Repeating common attributes in both supertype and subtype dimensions is a good example of dimensional thinking to avoid snowflaking dimensions which is common in relational thinking.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Mapping heterogeneous attributes

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