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

Multvalued dimension bridge table and SCD 2 dimension

4 posters

Go down

Multvalued dimension bridge table and SCD 2 dimension Empty Multvalued dimension bridge table and SCD 2 dimension

Post  kuldeepchitrakar 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 : 41
Location : India

http://www.bidwbooks.com

Back to top Go down

Multvalued dimension bridge table and SCD 2 dimension Empty Re: Multvalued dimension bridge table and SCD 2 dimension

Post  nick_white Mon Jun 02, 2014 2:32 am

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

nick_white

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

Back to top Go down

Multvalued dimension bridge table and SCD 2 dimension Empty Re: Multvalued dimension bridge table and SCD 2 dimension

Post  umutiscan 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 : 43
Location : Istanbul, Turkey

Back to top Go down

Multvalued dimension bridge table and SCD 2 dimension Empty Re: Multvalued dimension bridge table and SCD 2 dimension

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

Back to top Go down

Multvalued dimension bridge table and SCD 2 dimension Empty Re: Multvalued dimension bridge table and SCD 2 dimension

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