Usage

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.

First look

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
• Configuration
• Merge query

How it works

Source / Target Connection Setup

At the beginning, you should define source and target database connection strings (OLE DB). Then, you should define source table, view or command text as well as target table.

Transformations Setup

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.

Configuration

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'

Merge Query

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.

Additional

Additionally (and highly recommended), you can save your project for future usage by clicking the Save button.