I have Slowly changing dimension Type 2. How can I joi that with fact table?
I have Persons table as
PersonID int Identity(1,1),
I have person working in multiple county's also. How to handle this situation to join with Fact table
Should I joinPerson number to fact table or should I join PeronID(identity column in my dimension)
Please suggest me.
- Edited by Reddy07 Tuesday, July 23, 2013 7:54 PM
Appreciate for quick response.
But how could I join with Fact table? how does that record know if he was working for which county if both the end dates are Open?
PersonID PersonNumber County State
1 9899 LN FL
2 9899 SR FL
My fact have
Person ID CaseID
Well in that case you effectively have 2 choices.
1.) If it's not possible for him to be working for 2 different countries at the same time, then you need to fix the data, maybe make the end date of the first one to be equal to the start date of the second one.
2.) If it is possible for him to be doing work in 2 different countries at the same time, then still join on person ID as you will need to still report on work in both countries.
It's up to you to decide.
If you only chose 1 of the 2 in this case you will be throwing away some data.
Do you want to be losing some of that data?
If not, and it truly is a data problem then I would suggest updating the end date of the first 1 to be the same as the start date of the second one.
- Edited by Duane Dicks Tuesday, July 23, 2013 9:01 PM tweak
They are both correct.
You need to just join as they are, don't worry about chosing one.
That is thwe only way you will be able to reflect data from both countries for the same person. as long as you link to the dimension on personid
- Edited by Duane Dicks Wednesday, July 24, 2013 1:28 PM url incorrect
Then, if I pull County LN it should have only one Case.
Same way If I pull County SR should have 2 Cases. but if I have the above fact table then it will show
3 cases for each County right which is wrong. What should I do to avoid that and show right values?
If I do like this as mentioned above it will show up in both the counties?
i.e. if i have one case in LN and another in SR
if i oin with both the dimension Person ID's then
4 records in fact table and both cases are shown in both th ecounties which is wrong..
Instead I need only 2 fact records
can you suggest me please.... How should I load fact record?