Multvalued dimension bridge table and SCD 2 dimension
4 posters
Page 1 of 1
Multvalued dimension bridge table and SCD 2 dimension
I have dimension which is multi-value e.g. Person might have 2 or 3 diseases at a time so to model this I have create person- diseases bridge
But the issue is Person Dimension is which SCD 2
Person Table.
PersonSK Name City Start Date End Date
11 AA Newark Jan-1-2014 Feb-2-2014
12 AA Manchester Feb-3-2014
Bridge Table.
DiseaseSK PersonSK Start Date End Date isLatest
7 11 Jan-1-2014 Feb-2-2014 N
4 11 Jan-1-2014 Feb-2-2014 N
7 12 Feb-3-2014
4 12 Feb-3-2014
Fact Table.
PersonSK Cnt
11 2
12 4
Now if Person's attribute changes we will have another SK in bridge table .
No if join the Dieses with bridge table to get the Persons count it will join old person key as well as new person key from bridge table and will get 4 records ideally it should have been only 2 records. can anyone help me how to handle such situation.
But the issue is Person Dimension is which SCD 2
Person Table.
PersonSK Name City Start Date End Date
11 AA Newark Jan-1-2014 Feb-2-2014
12 AA Manchester Feb-3-2014
Bridge Table.
DiseaseSK PersonSK Start Date End Date isLatest
7 11 Jan-1-2014 Feb-2-2014 N
4 11 Jan-1-2014 Feb-2-2014 N
7 12 Feb-3-2014
4 12 Feb-3-2014
Fact Table.
PersonSK Cnt
11 2
12 4
Now if Person's attribute changes we will have another SK in bridge table .
No if join the Dieses with bridge table to get the Persons count it will join old person key as well as new person key from bridge table and will get 4 records ideally it should have been only 2 records. can anyone help me how to handle such situation.
Re: Multvalued dimension bridge table and SCD 2 dimension
Use natural/business keys in the Bridge table rather than SKs
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Multvalued dimension bridge table and SCD 2 dimension
Bridge tables are used to hold many to many relations between a fact and a dimension.
As far as I understand, you want to track person-disease history so the solution is fact table.
I prefer to add a new row when a person gets a disease, and update the end date when that person recovers form disease.
You don't need to create a new row when new versions added to dimension tables. You can keep the original surrogate key and if you need to query with current dimension values you do a self join on dimension table to find current (or any) dimension values.
As far as I understand, you want to track person-disease history so the solution is fact table.
I prefer to add a new row when a person gets a disease, and update the end date when that person recovers form disease.
You don't need to create a new row when new versions added to dimension tables. You can keep the original surrogate key and if you need to query with current dimension values you do a self join on dimension table to find current (or any) dimension values.
umutiscan- Posts : 51
Join date : 2010-11-26
Age : 44
Location : Istanbul, Turkey
Re: Multvalued dimension bridge table and SCD 2 dimension
You may create a factless fact table, by dragging in the surrogate keys from person, disease and time dimension. So at any point of time, you would be able to pull the disease details of a person.
time_key person_sk disease_sk
time_key person_sk disease_sk
krishgenius- Posts : 3
Join date : 2014-06-03
Similar topics
» bridge table and junk dimension on customer dimension (bank/credit union)
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» Degenerate Dimension - Bridge Table
» Multivalued Dimension & Bridge Table
» Adding mini dimension to bridge table
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» Degenerate Dimension - Bridge Table
» Multivalued Dimension & Bridge Table
» Adding mini dimension to bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum