Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Mapping heterogeneous attributes

3 posters

Go down

Mapping heterogeneous attributes Empty Mapping heterogeneous attributes

Post  bobb13 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

Back to top Go down

Mapping heterogeneous attributes Empty Re: Mapping heterogeneous attributes

Post  ngalemmo 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.

ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Mapping heterogeneous attributes Empty Re: Mapping heterogeneous attributes

Post  hang 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

Back to top Go down

Mapping heterogeneous attributes Empty Re: Mapping heterogeneous attributes

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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