question

tlenzmeier avatar image
tlenzmeier asked

Slowly Changing Dimension: Update “row is current” value

I have a customer SSIS package that is configured to be a slowly changing dimension. During the wizard, when choosing either the is current or the start-end date option, I have chosen the start and end date options. I also have a column called RowIsCurrent It has a bit (boolean) data type with a default value of 1. I am trying to configure the SCD to change the value from 1 to 0 when the SCD ends a historical row. I thought I could just add a line to the derived column task that sets the end date. I am most likely missing something rather elementary, but alas I don't know where I've gone wrong.
ssissql server 2014slowly-changing-dimension
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jeff Moden avatar image
Jeff Moden answered
If you have a start and end date in an SCD, you don't need an "IsCurrent" bit. The dates will tell you which row is current or not. Don't fall into the trap of having two pieces of information that tell you the same thing.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, SCD Type2, where we are trying to store historical data should have startdate,enddate and a version flag (with a bit value 0 or 1).0 should signifies older record and 1 should signifies new record. Start Date and EndDate should be declare in the SCD wizard and VersionFlag should be a derived right after union and before inserting to destination.
0 Likes 0 ·
What Jeff is trying to say is that you done need the version flag because you get to know the active record directly by looking at the end date column. If the End Date is NULL, that record is active.
0 Likes 0 ·
Correct. But the EndDate should NOT be NULL because that makes for awkward/sometimes slow queries that require the use of an OR or other NULL test in WHERE clauses. The EndDate should be some ridiculous future date. I always use '9999', which auto-magically translates to '9999-01-01' in DATETIME columns.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.