Search code examples
sql-server-2012scd

Microsoft SQL Server 2012 Slowly Changing Dimensions Historical Attributes Change Date as well as Status

I am stuck with the following problem on MS SQL Server 2012 with VS 2010:

I want to use the SSIS for Slowly Changing Dimensions for changing a historical attribute. As the wizard only gives me the opportunity to decide to either save the information, whether the record is outdated or not, in one column or in the two date columns (see https://msdn.microsoft.com/en-us/library/ms187958.aspx). But I want to do both, save the information in the column 'Status' and also update 'StartDate' and 'EndDate'.

Example for what a table of employees should look like after using SSIS:

Employee Emma Johnson gets married and thus, on 20-02-2013, changes her lastname to Smith.

EmployeeID | EmployeeIDAlternateKey | Firstname | Lastname | StartDate | EndDate | Status

1 | 123 | Emma | Johnson | 01-01-2013 | 20-02-2013 | NULL

2 | 123 | Emma | Smith | 20-02-2013 | NULL | Current

Thanks for your help!

Solution

  • What about you implement that logic of yours in the split that the slow changing dimensions component generates?

    if the record is to be inserted set the status to "current"; and insert the record in the dimension

    if the record is to be updated set the status to "NULL"; and update the record in the dimension