Using the SCD Merge Wizard
Usage of SCD Merge Wizard is relatively simple and straightforward. Of course, you should be familiar with
Slowly Changing Dimension concepts
to be able to configure transformations properly.
When you open the application, it will appear like this:
Wizard consists of several pages for easier configuring:
- Welcome screen
- Source connection setup
- Target connection setup
- Transformations setup
- Merge query
How it works
Source / Target Connection Setup
At the beginning, you should define source and target database
(OLE DB). Then, you should define source table, view or command text as well as target table.
After configuring source and target, you are ready to read the metadata. Click on the Read Metadata:
Application will read all source and target columns and place them into the transformation grid. Wizard is smart enough to automatically pair columns with the same names:
Initially, application sets default transformations for all columns: SCD0 or Skip, depending on source and target columns. Of course, you need to define transformations according to your business needs. One example of properly configured transformation could
be like this:
You may noticed that this grid is very similar to the transformation definition grid in SSIS Slowly Changing Dimension component.
After configuring transformations, you should configure your query. There are several options for you to choose:
Lets explain every option:
- DateFromMode - Defines value for DateFrom column for the first record with given business key
- DateToDateFromOverlap - Defines relationship between old and new record for given business key
- DateToMode - Defines value to be used as DateTo column
- Ignore Database Prefix - If source and target databases are on the same server, database prefix can be ignored
- RecordsOnTargetNotFoundOnSource - Defines what to do with records on target when they are missing from the source. We can either update DateTo, delete record or ignore
- ShowExtendedComments - Shows additional comments in the query such as target, source, column transformations, etc.
- Use datetime2 - datetime2 has greater range: from '0001-01-01 00:00:00' to '9999-12-31 23:59:59'
As final step in this procedure, you need to click Generate Query to generate final T-SQL Merge query for Slowly Changing Dimension. And that's it.
Additionally (and highly recommended), you can save your project for future usage by clicking the Save button.