0 Replies Latest reply: Aug 11, 2015 10:00 AM by Daniel Balchasan RSS

    Slowly changing dimension, Master Calendar and fact tables



      I'm rather new here, and I'm still working out some of the basics...so I apologize if this seems basic, but I couldn't find this case in the blogs and training books...


      I'm trying to load a slowly changing dimensions table ([SubIntervals]), a master calendar table and a facts table ([Checkins]).

      My [SubIntervals] table has periods for values changed  on a person, and has columns for :

      SubInterval_ID,PersonID, From_Date, To_Date, Status, Home_Center, Person_Type


      In order to be able to select a date and find the persons who have a specific value in one of the fields (Status, Home_Center, Person_Type) on that date, I created a [Master Calendar table], and a connecting table called [SubIntervals_x_Dates] based on the post by HIC : Creating Reference Dates for Intervals

      [SubIntervals_x_Dates] has the columns:

      DATA_Date, SubInterval_ID


      This works fine.


      Now I want to include a facts table called [Checkins] and link it to the other ones, so I am able to check who has a 'checkin' on a specific date, and what their 'Person_Type' was on that date.

      [Checkins] has the columns

      PersonID, Checkin_Date, Checkin_Center



      I'm not sure how I can load my [Checkins] table in a way that it links to the master calendar, but doesn't link to the wrong persons or wrong dates.


      Thanks in advance