Patrick Thanks for the information.  That is interesting.  I never really look at the code as I do not has a SAS programming background.  It seems we are getting around this behaviour using the extract transform and extracting the data sorted by the business key and the datetime column.  We are also not using a source column as an input to the start and end date of the record in the dimension.  These WHO columns which are date are maintained and managed by DIS 4.5 in our case. Then selecting create a view on the Extract physical properties.  A PROC SQL statement is created which is ordered correctly ensuring the data into the SCD II transform is in the correct order. create view work.W217J6G as       select          Agency_Resign_Date,          Business_Key,           Chnl_CD,          Chnl_LDesc,    from &SYSLAST    order by       Business_Key,       Agency_Resign_Date    ; The code seems stable.  With regards to using undocumented features from release to release, I guess it depends on the feature and how it affects the process.  We have isolated our SCD II Jobs in our ETL process to very simple jobs, i.e data is staged before the SCDII Job and then a wholesale replace of our dimension after the job.  This way if the functionality changes it is very isolated and once fixed all functionality down stream will work. In situations like this I do not see much different between undocumented (not specifically stated features) capabilities and potential new features you would want to adopt in a new release.  Both will cause some rework and refinement of your ETL process. Cheers John ... View more

The SCDII loader does support multiple business keys.  The business key is designed to match records which are the same.  The detect changes is designed to determine if the attributes have changed and if so create a new record for the same business key.    When reading data from a database unless you use the order by syntax for SQL you are not guaranteed to retrieve the data in any particular order.  It will depend where the database starts to read the data from the disk.  So if you have multiple changes to data over a day, you need to specific an order by or sort to ensure the information is retrieve in the order the changes were applied. Scenario I have a customer C1, they have an address.  We build a dimension to track customer address changes. In a day they may be multiple updated to the customer information.  The data you send to the data warehouse may have all the change records.  In this scenario you business key which is the customer key in the source will be duplicated.  The change columns of address will have different values.  When extracting the data if you order the data on change date then you will get the  chronological order records have changed. Setting the Option Allow Multiple updates per day will allow the SCDII to process all the change records.  In this way you will receive multiple records within a load which have been end dated and are not the current record.   If this is not set then then the SCDII will only load the initial update and ignore the rest of the updates. A lot of our data sets have multiple changes within a load period and thr SCDII supports it.  We even use the SCDII transform for SCDI dimensions as if we ever wish to change we can reconfigure the transform without rewriting the Job. Hope this helps Cheers John ... View more

I would just add one additional step to the comments made by .  Before the SCDII loader in your extract transform ensure you are sorting the data under the order by tab so that the information is consistently sorted in the correct order to apply the changes to the SCD II.  This is important as many database engines do not guarantee the way data is read and presented in a non sorted data set. If this is not done then your SCD II records may not be applied in the correct order causing the active record not to be the current record. Cheers John ... View more