New records when no changes are made

Topics: Issues 3: Executing the Component (Run-Time)
Dec 28, 2010 at 12:28 PM

Hello,

I successfully made a dataflow task using the kimball task. I used the following items:

  • SCD1 for the description
  • SCD2 for every other change
  • The business key, surrogate key, effective and expiry date are all set correctly
  • Specifing every type for SCD1, SCD2, deleted and new records and did give it the action I wanted
  • As for the effective date, I have a variable containing the date. At the "SCD 2 Date handling" tab, I also specify this variable. For example, the date is 1-1-2011.

When I initially run the task, every change is made correctly. However, when I run the task again, without making any new changes, new records are being detected. When I change the effective date in my variable it will not detect these new records and nothing happens. 

Could someone give me an explenation for this behaviour and are there some best practices to prevent this?

Kind regards,

Tommy Tooten

Coordinator
Dec 30, 2010 at 11:45 PM

It sounds like you are trying to run the process twice using the same date in your date variable.  The SCD process is NOT [url:idempotent|http://en.wikipedia.org/wiki/Idempotent].  Running it twice on the same "day" will produce undefined results that are invariably incorrect.

May 5, 2011 at 3:47 PM

I'm getting the behavior described above regardless of the value in the date variable.

Michael McClurkin

Oct 17, 2011 at 7:39 AM

Hi Todd,

 

With v1.6, im getting similar behaviour which i cant explain. I have a dimension with SCD1 and SCD2 columns. I run the initial load with 5 source records from a static test table and they populate correctly as new records.

 

Then ive made a minor change to the source to simulate an SCD1 i.e. I updated a single SCD1 column to a different value - i did this 2 rows only.

Now I incremented my date variable by one day and reran the package with. I am getting 5 rows on the New output (instead of 0), and 5 rows on the Unchanged output (instead of 3)! No rows are being output on Updated SCD1.

 

Interestingly, if i remove the "SCD2 Effective Date" and "SCD2 Expiry Date" columns from the "Existing Dimension" input, the above scenario works as expected  - 3 unchanged, 2 Updated SCD1. This cant be the final solution though, as the component crashes with "object reference not set..etc" if an SCD2 change is detected but the "SCD2 Effective Date" and "SCD2 Expiry Date" are not configured in the Existing Members input.

 

Oct 19, 2011 at 7:32 AM

For anyone else who is having this issue.. it seems to be a change in the way v1.6 does SCD fdate handling.  Our problem was the maximum SCD2 date was set as "SQL max datetime", yet the existing dimension was not using that. We simply changed the SCD2 DAte Handling configuration to use a datetime stored in a variable, and made the value of that variable the same as our dimensions maximum exiry date.