Multvalued dimension bridge table and SCD 2 dimension

View previous topic View next topic Go down

Multvalued dimension bridge table and SCD 2 dimension

Post  kuldeepchitrakar on Sun Jun 01, 2014 7:18 am

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.

kuldeepchitrakar

Posts : 17
Join date : 2010-04-21
Age : 34
Location : India

View user profile http://www.bidwbooks.com

Back to top Go down

Re: Multvalued dimension bridge table and SCD 2 dimension

Post  nick_white on Mon Jun 02, 2014 2:32 am

Use natural/business keys in the Bridge table rather than SKs

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Multvalued dimension bridge table and SCD 2 dimension

Post  umutiscan on Mon Jun 02, 2014 2:38 am

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.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Multvalued dimension bridge table and SCD 2 dimension

Post  krishgenius on Tue Jun 03, 2014 5:25 pm

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

krishgenius

Posts : 3
Join date : 2014-06-03

View user profile

Back to top Go down

Re: Multvalued dimension bridge table and SCD 2 dimension

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