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:

sdcmw.png

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:

read metadata.jpg

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:

column mapping initial.jpg

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:

column mapping final.jpg

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:

config.png

Lets explain every option:
  1. DateFromMode - Defines value for DateFrom column for the first record with given business key
  2. DateToDateFromOverlap - Defines relationship between old and new record for given business key
  3. DateToMode - Defines value to be used as DateTo column
  4. Ignore Database Prefix - If source and target databases are on the same server, database prefix can be ignored
  5. 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
  6. ShowExtendedComments - Shows additional comments in the query such as target, source, column transformations, etc.
  7. 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.





Last edited Jan 27, 2013 at 7:32 PM by knyazs, version 14

Comments

No comments yet.