Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Source & Destination Best Practices with the Task Factory Dimension Merge SCD

  • 14 October 2013
  • Author: Michael McIntyre
  • Number of views: 11315
  • 0 Comments

When I figured out errors or other issues when it comes to IT, I often feel like I’ve just defeated the Matrix. Granted… these situations are often due to my own incorrect settings (okay… pretty much always), but let’s not split hairs. I beat the machine, and that’s what matters.

I can dodge bullets?

Whoa.

 

Moving on…

The Task Factory Dimension Merge SCD component is a lot of fun. It also has a lot of moving parts and can be a challenge when one is going through the initial setup process. This is just my basic test setup:

Many people I speak with who are just getting started with the component ask questions about performance or may experience some deadlocking/hanging issues with their destinations. Here are some best practices I would recommend when initially setting up this component.

Sources:

When using the Dimension Merge SCD, it is always recommend to have the sources (both your Source and Dimension) sorted by the Business Key in the Advanced Properties.

Advanced Properties Part 1: Change the Source Output “IsSorted” to True

Advanced Properties Pt1

Advanced Properties Part 2: Set SortKeyPosition on the Business Key(s)

Advanced Properties Pt2

Select the records using a SQL Statement and sort by your Business keys with an ORDER BY clause, not by simply selecting the table.

Select Statement with ORDER BY

Follow the same steps for your Dimension source component as well.

Destinations:

This is where we can experience hanging or deadlocking because this package, possibly in addition to other packages, are all writing to your Dimension at the same time.

In this example, I’m using both OLE DB Destinations and the Task Factory Upsert Destination. For the OLE DB Destinations, I’m going to turn off Table Locking. This is my OLE DB Destination for New rows:

Table Locks Off

For the Task Factory Upsert Destinations, I’m going to check Turn Off Internal Transactions (the same can be done with the Task Factory Update Batch Transform, for the record). This is my Upsert Destination for Updated SCD1 records:

Upsert - Internal Transactions

We’re essentially doing the same thing with each destination component.

As a side note, I have seen a number of people using the OLE DB Command to perform Updates & Inserts. If you’re already using Task Factory – take advantage of the Upsert Destination. You already have access to it, as a colleague of mine always says: let it do what it does best. 

 

These steps have helped just about everyone I've spoken with, so I hope they do the same for you. 

Print
Tags:
Rate this article:
5.0

Please login or register to post comments.