17

Resolved

Support Multiple Intra-day SCD Processing

description

Currently, the component permits usage of a date/time typed variable as the effective/expiry time cutoff. This doesn't preclude using the component to load/process the same table multiple times in a day. However, the choices for applying this variable's value to the effective and expiry columns does. The choices effectively limit the component to correct operation only for daily loads.
 
In order to properly support multiple intra-day processing, a smaller "gap" between the expiry date/time of the "old" version and effective date/time of the "new" version must be allowed.
 
Possible solutions:
A) Add more choices to the "date interpretation" dropdowns - such as "effective is now, expiry is one millisecond ago".
B) Discard "choices" structure, and simply require two variables be specified that contain the effective and expiry date/times.
C) Change "choices" from a predefined list to a set of arguments to "dateadd"

comments

JoeSalvatore wrote Mar 23, 2009 at 9:40 PM

Thanks again for capturing some of my wish list for effective and expiry processing.
The key is that a full datetime is really needed first and foremost.

One millisecond ago is not needed but set expiry equal to new row effective is good.
This works as TSQL you normally to WHERE > FromDatetime and <= ThruDatetime so that an overlap would not occur.

Often we need to use values obtained from the source without first passing to variables (thus using a column value).
The source SQL would thus include a ModifiedDateTime field and this would be used for
  1. The effective datetime for new rows (including new inferred rows and new SCD 2 current rows)
  2. The expiry datetime for the business key matching existing SCD 2 row(s)
    Additionally, an option to suppy variables would be useful when you grab the start date time of the solution and wish to use that for effective\expiry datetime stamps

dmausner wrote Feb 16, 2010 at 10:43 PM

It would be sufficient to have one extra option "old expiry = new effective" and in that case use a datetime with millisecond precision.

dmausner wrote Feb 16, 2010 at 10:44 PM

It would be sufficient to have one extra option "old expiry = new effective" and in that case use a datetime with millisecond precision.

dmausner wrote Feb 16, 2010 at 10:44 PM

It would be sufficient to have one extra option "old expiry = new effective" and in that case use a datetime with millisecond precision.

donnapkelly wrote Feb 17, 2010 at 10:41 PM

With the move to microbatch loads, the use of time is becoming more important. I had planned my current project to use overnight incrementals; now I'm at hourly. The workaround is to use triggers on insert and update, but that's a bit kludgy. I like the one millisecond ago approach (option A), as sometimes people don't always appreciate the difference between <= and < when setting time filters.

JoeSalvatore wrote Mar 3, 2010 at 1:04 AM

Other work items within the Issue Tracker are related to this:
Add Support for a "Change Stream" Source System Input http://kimballscd.codeplex.com/WorkItem/View.aspx?WorkItemId=304
and Enable SCD 2 Date Handling Effective and Expiry Dates Set to SSIS Variable Values http://kimballscd.codeplex.com/WorkItem/View.aspx?WorkItemId=5322)Please vote and add your additional insights.

toddmcdermid wrote Aug 22, 2010 at 3:43 AM

Coded...

Sign in to add a comment or to set email notifications