12 Replies Latest reply: May 13, 2016 3:17 AM by Magdalena Walentek RSS

    IntervalMatch, Slowly Changing Dimensions and Calendar

    Magdalena Walentek

      Hi everyone,


      My  HR data model is made mainly with slowly changing dimensions, which I have combined into one 'Sub Intervals' table.

      I have six tables of data HR dependent on the time that I had to control using the interval match and subintervlas.

      Basically my end result is being able to choose a point in time in calendar, and see the state of my employees based on the different slowly changing dimensions.

       

      Data model:

      Unbenannt.PNG

       

      I have two cases:

       

      1. When I round up date(ceil(SubIntervalEnd)) in script I have problem with SubIntervals table, this table create one additional interval (31.12.2016 - 01.01.2017) which is wrong and if I didn't remove this row from table I have wrong result, when I choose last date in calendar.

       

       

      Unbenannt.PNG

       

       

      2. When I don't round up date(ceil(SubIntervalEnd)), IntervalMatch in BridgeTableKalendar I don't see last day with  connection to interval(01.01.2017).

       

       

      Unbenannt.PNG

       

       

      Do you know what is the problem? Where I made a mistake and how can I solve it?

       

       

      SubIntervalBegin - min date is 01.01.2014

      SubIntervalEnd   -  max date is 01.01.2017

       

      Script:


      Let vEpsilon = Pow(2,-27);

      //// ============ Load all distinct beginnings and endings Intervals ============

      TempSubIntervals:
      //Tabelee PA0000
      Load distinct PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0000.BEGDA0001 as SubIntervalBegin  Resident PA0000;
      Load distinct PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0000.ENDDA0001 as SubIntervalBegin  Resident PA0000;
      //Tabelle PA0001
      Load distinct PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0001.BEGDA0001 as SubIntervalBegin  Resident PA0001;
      Load distinct PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0001.ENDDA0001 as SubIntervalBegin  Resident PA0001;
      ... for rest table script looks the same


      // ============ Create the SubIntervals ============

      SubIntervals:
      Load*,
      PERSONALNUMMER & '|' & SubIntervalBegin  as SubIntervalID,
      SubIntervalBegin & '-' & SubIntervalEnd as SubInterval.key;
      Load
      SubIntervalBegin  as SubIntervalBegin ,
      date(ceil(SubIntervalEnd)) `      as SubIntervalEnd,
      PERSONALNUMMER,
      PERSONALNUMMER  as %PERSONALNUMMER.Personendaten,
      Load PERSONALNUMMER
      SubIntervalBegin,
      Dual(SubIntervalEnd, (SubIntervalEnd - $(#vEpsilon))) as SubIntervalEnd
      Where not IsNull(SubIntervalEnd) ;
      Load PERSONALNUMMER, SubIntervalBegin,
      If(PERSONALNUMMER = Previous(PERSONALNUMMER), Previous(SubIntervalBegin)) as SubIntervalEnd
      Resident TempSubIntervals
      Order By PERSONALNUMMER, SubIntervalBegin  Desc;
      Drop Table TempSubIntervals;


      TmpBridgeTablePA0000:
      IntervalMatch (SubIntervalBegin, PERSONALNUMMER)
      Load distinct PA0000.BEGDA0001PA0000.ENDDA0001, PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER resident PA0000;

      BridgeTable_PA0000:
      Load
      PERSONALNUMMER & '|' & SubIntervalBegin as SubIntervalID,
      PERSONALNUMMER & '|' & PA0000.BEGDA0001 & '|' & PA0000.ENDDA0001 as PA0000.OriginalIntervalID
      Resident TmpBridgeTablePA0000;


      Drop table TmpBridgeTablePA0000;

      TmpBridgeTablePA0001:
      IntervalMatch (SubIntervalBegin, PERSONALNUMMER)
      Load distinct PA0001.BEGDA0001PA0001.ENDDA0001, PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER resident PA0001;

      BridgeTable_PA0001:
      Load

      PERSONALNUMMER & '|' & SubIntervalBegin as SubIntervalID,
      PERSONALNUMMER & '|' & PA0001.BEGDA0001 & '|' & PA0001.ENDDA0001 as PA0001.OriginalIntervalID
      Resident TmpBridgeTablePA0001;

      Drop table TmpBridgeTablePA0001;

      ... for rest table script looks the same

         TMP_Kalender:
      LOAD
      date(MinimumDatum + IterNo() - 1) as Datum,
      date(MinimumDatum + IterNo() - 1) as %DATUM,
      Month(date(MinimumDatum + IterNo() - 1)) as Monat,
      Year( date(MinimumDatum + IterNo() - 1)) as Jahr,
      Week( date(MinimumDatum + IterNo() - 1)) as Woche
      WHILE MinimumDatum + IterNo() - 1 <= MaximumDatum;


      LOAD
      min(PA0000.BEGDA0001) as MinimumDatum,
         max(PA0000.ENDDA0001as MaximumDatum

      RESIDENT PA0000;


      NoConcatenate

      Kalender:
      LOAD
      %DATUM,
      // date(ceil(%DATUM)) as %DATUM,
      Datum  as Kalender.Datum,
      Monat  as  Kalender.Monat,
      Jahr  as Kalender.Jahr,
      MonthEnd(Datum) as Kalender.MonatJahr,
      if(Datum = date(floor(MonthEnd(Datum))), floor(MonthEnd(Datum)),'') as Kalender.MonatJahrUltimo
      Resident TMP_Kalender;

       

       

       

      BridgeTableKalendar_TMP:
      IntervalMatch (%DATUM)
      Load Distinct SubIntervalBegin, SubIntervalEnd  Resident SubIntervals;


      NoConcatenate


      BridgeTableKalendar:
      Load
      (
      SubIntervalBegin & '-' & SubIntervalEnd) as SubInterval.key,
      SubIntervalBegin as TMPSubIntervalBegin,
      SubIntervalEnd as TMPSubIntervalEnd,
      %DATUM
      Resident BridgeTableKalendar_TMP;

      Drop table BridgeTableKalendar_TMP;

       

       

      Thanks!