Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 20 Mar 2012 @ 22:27:40 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Slow changing dimension
From:   Kenneth Hansen

Such tables need to have a (business) date opened and (business) date closed and the closed date commonly defaults to a high date such as 9999- 12-31.

Load the new extract from source systems to your staging area. Left outer join those to the target table where the closed date is high or (in more recent versions, where the record is effective).

Where the identifier from the target (right hand side of the join) is null, insert the record.

Where the identifiers and other attributes match - take no action.

Where the identifiers match but the other attributes do not, update the high value to show it is closed and insert the new record with a high date to show it is effective.

That's more than one SQL instruction so use a Macro rather than an SP.

Is that what you wanted?

Kenneth Hansen

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020