Can I Use Timestamps instead of Dates in Effective and Expiry Dates

Topics: Possible Bugs/Missing Features?
Sep 8, 2010 at 9:46 AM
Edited Sep 8, 2010 at 9:47 AM

I am trying to use the Kimball SCD and I have the following issue..

Imagine that my SSIS package is executed twice per day (or even more often). For a specific date I might have 2 alterations or more in a dimension... But Kimball SCD is using Dates hence I might have more than one records with the same Business Key and same Effective Date and Expiry Date... Can't I use timestamps instead....?

I tried to ignore the outputs for these columns from Kimball and using a derived column to pass the timestamps manually... In that case Kimball does not work correctly. It always considers the values as new (even if there are not changes....)

 

Any Ideas..

Regards George

Sep 8, 2010 at 3:28 PM

This is crossing my experience made with this component.

If the start date of a row is equal the date your running the component it will fail the correct output.

One posible workaround would be ignoring the start date coming form the dimension source (setting it to null).

I don't know if this workaround has any side effects, but its worth a try and worked for me so far.

Regards
Michael

 

 

Coordinator
Sep 10, 2010 at 10:53 PM

That NULL workaround scares me.  But if you test it and it works... okay.

I'd recommend trying v1.6- even though it's alpha, which has multiple intra-day update capability.

Sep 15, 2010 at 7:54 AM

Hey Todd...

 

I downloaded and installed v1.6 .... Didn't you make changes to the Dates screen...??? Its exactly the sames as in version v1.5

I uninstalled v1.5 and then Installed v.16

Regards

George

Sep 15, 2010 at 8:31 AM

Also when I tried to execute the package I got several errors from the Kimball  (see errors below)

I noticed and SQLDumber (?!?!?) dos window popping up briefly when it failed.... Did you create a log dump.... Where is that located. I might be able to send you this.

Regards

George

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 03000000) part .) in ProcessKey detecting changes, key: 03000000.) in ProcessCache_Thread_ProcessKey.

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 03000000) part .) in ProcessKey detecting changes, key: 03000000.

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Internal error (Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 00000000) part .) in ProcessKey detecting changes, key: 00000000.) in ProcessCache_Thread_ProcessKey.

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 00000000) part .) in ProcessKey detecting changes, key: 00000000.

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 03000000) part .

[Kimball Method Slowly Changing Dimension 2] Error: Internal error (Object reference not set to an instance of an object.) in ProcessKey thread checking existing history rows for overlapping version dates (key 00000000) part .

 

Coordinator
Sep 20, 2010 at 3:59 PM

It's "SQL Dumper", and no, the output isn't useful to me.

I looked at the code in that area, and found some potential issues that might have caused your problem.  Does your data happen to have NULLs in either the "SCD 2 Effective Date" or "SCD 2 Expiry Date" columns?

Sep 21, 2010 at 6:51 AM

Both have NULL values for sure. The first record has a NULL effective from and the last record has a NULL Valid Until... If there is only one record it has NULLs both in Effective From and Valid Until.

So... Yes 99% of the records have NULLs

 

I assume thats bad...?!?!?

Coordinator
Sep 30, 2010 at 6:32 PM

Unfortunately, I can see that it is - I attempt to use those values without checking them first (assume = ass|u|me).  Sorry about that.  I'll try to get a fix up to 1.6 shortly...