SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slowly changing dimensions using T-SQL MERGE


Slowly changing dimensions using T-SQL MERGE

Author
Message
Adam Aspin
Adam Aspin
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2217 Visits: 1032
Comments posted to this topic are about the item Slowly changing dimensions using T-SQL MERGE
Franky Leeuwerck
Franky Leeuwerck
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1004 Visits: 495
Great article Adam !
Thanx for sharing.

Franky

Franky L.
dawalker-1068762
dawalker-1068762
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 116
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.
David Greenberg-358875
David Greenberg-358875
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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?
al kv
al kv
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 123
where can I get the CarSales db?
Adam Aspin
Adam Aspin
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2217 Visits: 1032
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
Adam Aspin
Adam Aspin
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2217 Visits: 1032
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
DEK46656
DEK46656
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 Visits: 588
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"
al kv
al kv
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 123
Adam,

Thank you.
DEK46656
DEK46656
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 Visits: 588
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)


Or

CHECKSUM(DST.*) <> CHECKSUM(SRC.*)


I typically use the individual fields approach myself.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search