Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Slowly changing dimensions using T-SQL MERGE Expand / Collapse
Author
Message
Posted Monday, June 20, 2011 12:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
Comments posted to this topic are about the item Slowly changing dimensions using T-SQL MERGE
Post #1127980
Posted Monday, June 20, 2011 3:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 2, 2014 3:39 AM
Points: 350, Visits: 475
Great article Adam !
Thanx for sharing.

Franky


Franky L.
Post #1128048
Posted Monday, June 20, 2011 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 6:06 AM
Points: 11, Visits: 104
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
Posted Monday, June 20, 2011 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 20, 2011 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:54 AM
Points: 3, Visits: 77
where can I get the CarSales db?
Post #1128244
Posted Monday, June 20, 2011 7:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
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
Posted Monday, June 20, 2011 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:03 AM
Points: 81, Visits: 903
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
Posted Monday, June 20, 2011 7:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 384, Visits: 432
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
Posted Monday, June 20, 2011 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:54 AM
Points: 3, Visits: 77
Adam,

Thank you.
Post #1128265
Posted Monday, June 20, 2011 8:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 384, Visits: 432
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.*) &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 »

Add to briefcase 123»»»

Permissions Expand / Collapse