December 12, 2012 at 12:36 pm
I've created a table in our DW with a StartDate and EndDate column, and when I use the Slowly Changing Dimension option in Data Flow in SSIS (BIDS with SQL 2008) the StartDate and EndDate columns update when data is changed but EndDate isn't updated when the source row in our OLTP database is deleted.
So for example if I have Account 123, the initial load into DW would be:
StartDate: 2012-12-12 13:30
EndDate: Null
Then I update Account 123 in the OLTP database and now the first row has this:
StartDate: 2012-12-12 13:30
EndDate: 2012-12-12 13:35
and the second row has this:
StartDate: 2012-12-12 13:35
EndDate: NULL
This is all as I expect ... but now if I delete Account 123 from our OLTP database and rerun the SSIS script nothing changes on the last row for Account 123 in DW. I'd expect that EndDate would be updated with getdate() to show it's no longer valid, but this isn't happening.
Any suggestions on how to do this? I'd have no other way to show this record is no longer in our OLTP system unless EndDate shows it's dropped off.
Thanks -
Sam
December 12, 2012 at 1:01 pm
Usually you'd use an audit and/or logging table to move deletions over. SCDs can't autodetect removed information. Trigger your deletions to an audit table and create a separate process to finalize the endDate in the fact table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply