Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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).
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.BEGDA0001, PA0000.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.BEGDA0001, PA0001.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.ENDDA0001) as 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!
Hi,
I checked, in the application you will see that selection for 2017 in the table PA0000 chooses bad records. It seems to me that it's a problem with the enumeration subintervals.
What do you think?
I think you are following pretty much the tech doc written by Henric
IntervalMatch and Slowly Changing Dimensions
but with slight modifications, which you should carefully review.
For example the original uses:
// ============ Create the bridge table between the two interval types ============
TmpBridgeTable:
IntervalMatch (SubIntervalEnd, SPID)
Load distinct OriginalBegin, OriginalEnd, TmpSPID as SPID resident OriginalIntervals;
BridgeTable:
Load
SPID & '|' & SubIntervalEnd as SubIntervalID,
SPID & '|' & OriginalBegin & '|' & OriginalEnd as OriginalIntervalID
Resident TmpBridgeTable;
Drop Field TmpSPID;
Drop table TmpBridgeTable;
where you are using the SubIntervalBegin fields.
It's working very good with table PA...
I had add additional flag for MonthYear in table InPerFTEEff and Fur PerFTEEff and it's working good.
Thanks for help!
BR,
Magda