none
SCD Key joining Fact Table

    Question

  • Hi,

    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),

    PersonNumber,

    County,

    State,

    Country

    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.

    ThankYou

    Red


    • Edited by Reddy07 Tuesday, July 23, 2013 7:54 PM
    Tuesday, July 23, 2013 7:53 PM

All replies

  • I'm guessing here that joining on Person Number will be the equivalent of a type 1 dimension, but joining on personID will be the equivalent of a type 2 SCD, this would allow for the same person working in multiple countries.

    My Blog

    Tuesday, July 23, 2013 8:02 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?

    Dim Table

    PersonID  PersonNumber  County    State

    1                    9899                 LN           FL

    2                    9899                SR            FL

    My fact have

    Person ID CaseID

    1                  2

    2                   3

    2                   4

    Tuesday, July 23, 2013 8:27 PM
  • 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.

    OR

    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.


    My Blog

    Tuesday, July 23, 2013 8:35 PM
  • My Option here is 2


    Can you suggest me how should I load Fact table?
    as I am joining with ID(Identity) column.

    If I need to put ID 1 or ID 2 in the fact table for that PersonNumber? because both have null in End date.

    Thnaks,
    Red

    Tuesday, July 23, 2013 8:51 PM
  • 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.


    My Blog


    • Edited by Duane Dicks Tuesday, July 23, 2013 9:01 PM tweak
    Tuesday, July 23, 2013 9:01 PM
  • But my data is like that he can work simultaniously at two different Counties and two diffirent managers..

    How can I proceed with this dimension? SCD Type 2

    Wednesday, July 24, 2013 1:05 PM
  • Then you must join on ID and put both id's on the fact table so that you can analyze across both countries.



    • Edited by Duane Dicks Wednesday, July 24, 2013 1:23 PM url broken
    Wednesday, July 24, 2013 1:08 PM
  • Both ID's means? I didnt get you

    Can you tell me my Fact Record here? is that the correct one below? then I wpold get wrong results correct?

    PersonID   CaseID

    1                   2

    2                   3

    2                   4

    1                   3

    1                   4

    2                    2

    Wednesday, July 24, 2013 1:20 PM
  • 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
    Wednesday, July 24, 2013 1:28 PM
  • 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?

    Wednesday, July 24, 2013 1:47 PM
  • It will only report on the personid's related to those Countries not the person nu mber. so you should be fine.


    • Edited by Duane Dicks Wednesday, July 24, 2013 2:24 PM broken url
    Wednesday, July 24, 2013 2:23 PM
  • Duane,

     Ok first let me check and let you know.because I am confussed here with designing dimensions and multiple facts in same cube.

     thanks for quick responses..

    Wednesday, July 24, 2013 2:28 PM
  • 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..

    PersonID   CaseID

    1                   2

    2                   3

    2                   2

    1                   3

    Instead I need only 2 fact records

    1             2

    2             3

    can you suggest me please.... How should I load fact record?

    Wednesday, July 24, 2013 6:20 PM