Basically my end result is being able to choose a point in time, and see the state of my members based on the different slowly changing dimensions.
I was able to get this to work, however it was quite heavy and the solution affected performance even when I only had about 5% of the data load I'll have eventually...
I figured a big cause for this was the fact that I kept all my dates in a timestamp format of 'yyyy-MM-dd hh:mm:ss' meaning that had a lot of different entries on the calendar table for the same date ( which caused problems in normalizing and compacting the date).
So I decided to split my periods from a column for "From_datetime" and a column for "To_datetime", to 4 columns:
However I'm stuck on the step where I do the interval match between my sub intervals and the original slowly changing dimensions:
This is the code I had which worked, before the separation of date and time. Note that EXTERNAL_ID is a unique persons ID which is linked to each period.
// ============ Create the bridge table between the subintervals and the TRANS ============
IntervalMatch (SubIntervalBegin, EXTERNAL_ID)
Load distinct FROM_DATE, TO_DATE, TmpEXTERNAL_ID as EXTERNAL_ID resident TRANS;