Slowly changing dimensions using T-SQL MERGE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adam Aspin » Slowly changing dimensions using T-SQL MERGE

30 posts, Page 1 of 3

1 2 3 »»»

Slowly changing dimensions using T-SQL MERGE

Rate Topic

Display Mode

Topic Options

Author

Message

Adam Aspin

Posted Monday, June 20, 2011 12:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

Comments posted to this topic are about the item Slowly changing dimensions using T-SQL MERGE

Post #1127980

Franky Leeuwerck

Posted Monday, June 20, 2011 3:59 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 3:52 AM
Points: 350, Visits: 470

Great article Adam !
Thanx for sharing.

Franky

Franky L.

Post #1128048

dawalker-1068762

Posted Monday, June 20, 2011 5:27 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 05, 2013 10:04 AM
Points: 11, Visits: 96

We have been working on a new data warehouse and I thought the MERGE command was going to be a real game changer for us. I did some initial testing using small tables and it worked great. I was ready to convert all of my code over to this method when we hit a snag. Since we are merging from one server over to another we are accessing the sources tables over linked servers. As the tables got larger the merge command was no longer a viable option for us. The performance was so bad it was just unusable. In many cases I was never even able to get the command to complete and had to kill the process. I was able to write my own SQL procedures that accomplished the same thing in a very reasonable amount of time albeit a little more lengthy and less elegant. Since we are merging about 170 tables hourly throughout the day speed is very important. I never did a comparison to see if it worked with the large tables on the same server so I can't speak to that, but my experience with the linked servers was not good. I was wondering if anyone else had tried this and had similar frustrations or even better yet had gotten it to work.

Post #1128103

David Greenberg-358875

Posted Monday, June 20, 2011 6:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:04 PM
Points: 4, Visits: 107

Very Good Article. I have a question. Suppose you have a dimension that contains certain columns that are type 2 where you want to keep track of historical changes. However, the dimension also contains type 1 columns where you just want to overwite existing values without creating a new record with a different surrogate key.

For example, I have a customer record that contains an address field. If the address changes, I want to handle it as a type 2 change. However, the record also contains a phone number which I just want updated in place when it changes but does not require the creation of a new record with a different surrogate key.

Can you handle this scenario within the same merge statement?

Post #1128181

al kv

Posted Monday, June 20, 2011 7:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:08 AM
Points: 3, Visits: 64

where can I get the CarSales db?

Post #1128244

Adam Aspin

Posted Monday, June 20, 2011 7:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

Hi Al Kv,
Sorr if I was not very clear - any database can be used, as long as it contains the tables given in the script. I created an empty database called CarSales for this purpose.

Regards,

Adam

Post #1128254

Adam Aspin

Posted Monday, June 20, 2011 7:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

Hi dwalker,

I have used MERGE succesfully on multi-million record tables - but, indeed, only when all the databases were on the same server. I use delta data detection techniques when using linked servers or, as you say, it is infernal.

Thanks for the feedback,

Adam

Post #1128255

DEK46656

Posted Monday, June 20, 2011 7:54 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 10:00 AM
Points: 314, Visits: 372

David Greenberg-358875 (6/20/2011)

Suppose you have a dimension that contains certain columns that are type 2 where you want to keep track of historical changes. However, the dimension also contains type 1 columns where you just want to overwite existing values without creating a new record with a different surrogate key.

I use a snowflake approach, as least in the data warehouse area: once you get to SSAS you can break it back out via a view if you wish. Anything for a dimension that is type 1 goes into its own table and is joined to the type 2 table. If the type 1 attributes change, you MERGE the new values into the T1 table, then update the key field in the type 2 table as needed.

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"

Post #1128257

al kv

Posted Monday, June 20, 2011 8:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 8:08 AM
Points: 3, Visits: 64

Adam,

Thank you.

Post #1128265

DEK46656

Posted Monday, June 20, 2011 8:09 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 10:00 AM
Points: 314, Visits: 372

I would suggest an alternative piece of code in one area you are using. When checking your type 2 attributes for change, you are using a lot of “not equal” checks, and transforming each attribute with a ISNULL.

This type of work falls into an area called “data fingerprinting”: checking to see if the finger print is different or not. Try wrapping the attributes you want checked in a CHECKSUM function, either individually listing each column or the entire record:

CHECKSUM(DST.ClientName, DST.Country, DST.Town, 
         DST.Address1, DST.Address2, DST.ClientType, DST.ClientSize) <>
CHECKSUM(SRC.ClientName, SRC.Country, SRC.Town, 
         SRC.Address1, SRC.Address2, SRC.ClientType, SRC.ClientSize)

CHECKSUM(DST.*) &lt;&gt; CHECKSUM(SRC.*)

I typically use the individual fields approach myself.

Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"

Post #1128266

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions