Mapping heterogeneous attributes
3 posters
Page 1 of 1
Mapping heterogeneous attributes
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
...
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
Re: Mapping heterogeneous attributes
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.
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.
Re: Mapping heterogeneous attributes
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
Similar topics
» How to Model mapping between products with varying number of descriptive attributes?
» dimension table design question for around 100 attributes and higher level calculated attributes
» Why Heterogeneous?
» Can a Bridge Table also be used for mapping?
» Address Dimension mapping to fact with diffrent grain
» dimension table design question for around 100 attributes and higher level calculated attributes
» Why Heterogeneous?
» Can a Bridge Table also be used for mapping?
» Address Dimension mapping to fact with diffrent grain
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum