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

wrote Mar 23, 2009 at 9:40 PM

wrote May 14, 2009 at 8:36 AM

wrote Jun 24, 2009 at 6:07 AM

wrote Jun 26, 2009 at 2:16 AM

wrote Sep 20, 2009 at 6:15 AM

wrote Sep 22, 2009 at 12:44 PM

wrote Sep 30, 2009 at 1:54 AM

wrote Nov 30, 2009 at 3:21 AM

wrote Feb 16, 2010 at 10:37 PM

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.

wrote Feb 17, 2010 at 10:35 PM

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.

wrote Mar 3, 2010 at 1:13 PM

wrote Mar 9, 2010 at 2:16 PM

wrote Mar 30, 2010 at 10:57 PM

wrote Apr 15, 2010 at 2:09 PM

wrote Jul 14, 2010 at 4:02 PM

wrote Jul 15, 2010 at 4:36 PM

wrote Jul 23, 2010 at 8:48 AM

toddmcdermid wrote Aug 22, 2010 at 3:43 AM

Coded...

wrote Feb 21, 2013 at 11:28 PM

wrote May 16, 2013 at 10:52 AM

wrote May 16, 2013 at 10:52 AM

wrote Jun 14, 2013 at 7:26 AM