SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Adam Aspin / Slowly changing dimensions using T-SQL MERGE / Latest Posts

RE: Slowly changing dimensions using T-SQL MERGE

barkeylives — Fri, 12 Apr 2013 10:45:43 GMT

Found this when looking for good examples. Great article! I noticed that one of the fields is left out of the WHEN MATCHED clause in the SCD2 version. "Country" is there, but not "County". If you modify any of the counties in the source table, it won't create a new record.

RE: Slowly changing dimensions using T-SQL MERGE

Paul Hernández — Fri, 02 Nov 2012 07:25:47 GMT

Hi everyone, and thank you Adam for this great article.I'm wondering if it is possible to have a mix approach in one table, something like a [b]Type 1 and 2 SCD in the same table[/b]. Suppose that in the customer dimension you have a couple of columns that change very often and some others that rarely do. This is not a typical "slow changing" scenario but if for any reason you have to face this situation what would you do?I would like only to update the columns that frequently change (type 1) and record historical values for the other columns (type 2). I think this is possible when the SSIS Slow Change Dimension Transformation, but due to the performance issue is not possible for me to use it. In this transformation you can mark a single column as "Changing Attribute", "Historical Attribute" or "Fixed Attribute". Any ideas or clues about how to accomplish that with the MERGE statement. At the moment I've created to tables, one to store the columns that often change and one to be treated as a classic SCD.Any comment would be appreciated.

RE: Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Tue, 17 Jul 2012 03:44:16 GMT

You are quite right, David, this should have been added to ensure completeness. Thanks for noticing this.

RE: Slowly changing dimensions using T-SQL MERGE

David Walker-278941 — Mon, 16 Jul 2012 12:59:44 GMT

In the Type 4 example, I see that you have a "ValidFrom" column in Client_SCD4_History. But it never gets populated, since Client_SCD1 doesn't have a ValidFrom date.I see you are populating the ValidTo date in the type 4 history table, which is good.Presumably, if you're using Type 4, all records in the main table (Client_SCD1) are "currently" valid. It would be nice to see the code that populates the ValidFrom date in the Type 4 history table. But since you're doing Type 4 based on what the Type 1 table needs, plus the history table, you haven't kept the ValidFrom date from the last time the record was updated. The Type 1 table doesn't have it.Am I right that there's something missing here?David Walker

RE: Slowly changing dimensions using T-SQL MERGE

Larry Kruse — Mon, 19 Dec 2011 08:51:02 GMT

In trying to use this article to tailor a solution for my organization, I have encountered a bit of a snag.Our "user" information is kept in two tables which we call UserAuthentication and Users. The UserAuthentication table contains the IDENTITY column and it gets created first, its IDENTITY value is captured after the INSERT with a SCOPE_IDENTITY() and then this value is used when creating a new Users record using an INSERT statement.Assuming that I have all of the records to be merged in a staging table which contains fields destined for both the UserAuthentication and Users tables, how might I most efficiently utilize the MERGE statements to create records in [u]both[/u] tables? Should I structure a stored procedure to handle this as MERGE against the UserAuthentication table first, then follow it with a MERGE against the Users table? Would another solution be to perform the MERGE against the UserAuthentication table, then capture the resulting INSERT records into a temp table that I can use as a basis for INSERTS on the Users table?

RE: Slowly changing dimensions using T-SQL MERGE

Larry Kruse — Thu, 15 Dec 2011 15:51:17 GMT

Adam,Thank you very much for this article. I have been searching around for a more intelligent way to handle massive, bulk-loaded datasets of user information. We have customers that basically send us entire refreshes of their data on daily and weekly frequencies and other that only send us updates. I need to speed up this process tremendously and stop performing delete then insert operations on entire groups of users. Instead, I need to post updates only when data really changes from load to load.I was particularly thrilled with some of the speed enhancements suggested by others of using the CHECKSUM( ) over the range of data as opposed to string compares everywhere. I will be testing it out both ways and see if I can quantify the differences I see in performance. Some of our data loads are small 2,000 or less, but we have some problem-children in the 200,000+ range.Very good article, and I will make a point to search for more of these from you...your coding style makes it very easy to read and understand the T-SQL code.Larry

RE: Slowly changing dimensions using T-SQL MERGE

gxkd64 — Thu, 07 Jul 2011 09:38:13 GMT

Adam,Fantastic article. Your writing style is very concise and informative. I am forwarding this to all my teams, great job!Scott

RE: Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Thu, 30 Jun 2011 08:01:13 GMT

Hi dwalker,This is extremely interesting, and thanks for sharing.I will try and find the time to look closer at this particular problem, as I had no idea that the speed difference was so great.Adam

RE: Slowly changing dimensions using T-SQL MERGE

dawalker-1068762 — Thu, 30 Jun 2011 07:32:42 GMT

Thanks for that suggestion amenjonathan. What you mention is precisely what we are doing. We are bringing in a copy of the production source tables to a staging server and then using those copies to build our warehouse tables. For a number of reasons we can't use replication, log shipping, etc. mostly per our agreement with the software vendor. Many of these tables are very large so bringing over entire copies of them each day is not desired and would take too long. It is best to update/sync the existing tables with production. I should also mention that we use some of these copied tables to do live reporting and also audit/track changes to some of the more important info so they need to update throughout the day to stay in sync. Our production system stays up 24/7 we have to use means that won't cause any performance issues with it. Since most of the data does not change from day to day, that's where we thought the MERGE command would be very useful to update those table copies on the staging server. It seemed to be a perfect fit, but it just wouldn't perform well enough.As for the use of checksum, fortunately we have a last modified datetime field on every record so we really don't need to use that. Performance wise, the inserts and updates run very quick because they are easily identified. The slow part of syncing is making sure you delete the records in the copy that have also been deleted on production. In order to do that I have to pull down a list of all the primary keys from the production table and compare those to the copy to make they still exist. I have sync procedures setup for each table with parameters to control the types of updates allowed. We have a job that runs every 10 minutes to keep them all in sync for inserts and updates. Once every 3 hours it will also perform any deletes since that takes about 4 times as long to run as a sync without them. So far I've been amazed at how well it all works without impacting production at all. We ran traces for quite a while just to make sure. What I found out in doing this is just how much different a query will perform when run locally versus when run over a linked server. There can be huge differences even if the queries are simple.

RE: Slowly changing dimensions using T-SQL MERGE

Herman van Midden — Thu, 30 Jun 2011 03:39:53 GMT

Great article, just what I needed. ThanksI made a slight modification in the CSD2 variant, just for security, to avoid ererroneous data, when the procedure is executed on an earlier day, than the last update.I admit, this does not happen when using GETDATE() to set today, but my procedure has a date input, so I decided to be sure no updates are made on records which have ValidFrom > @Today.So I added a statement to the WHEN MATCHED condition:[code="sql"] WHEN MATCHED AND IsCurrent = 1 AND ValidFrom <= @Today --Never allow updates to overwrite future records AND (ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'') ...[/code]Thanks once moreHerman

RE: Slowly changing dimensions using T-SQL MERGE

amenjonathan — Thu, 23 Jun 2011 09:15:46 GMT

I don't see an update statement in your MERGE.Not sure about how to handle NULLS. My whole warehouse is NOT NULL.

RE: Slowly changing dimensions using T-SQL MERGE

orilefka — Wed, 22 Jun 2011 17:25:36 GMT

OK.Thank you. This is the whole code i wanted to use:[code="vb"]MERGE INTO dbo.Client_SCD2 AS DSTUSING dbo.Client AS SRCON SRC.ID = DST.BusinessKeyAND DST.ClientName=SRC.ClientNameAND DST.Country = SRC.CountryAND DST.Town=SRC.TownAND DST.Address1=SRC.Address1AND DST.Address2=SRC.Address2AND DST.ClientType=SRC.ClientTypeAND DST.ClientSize=SRC.ClientSizeWHEN NOT MATCHED BY TARGET THEN INSERT (BusinessKey, ClientName, Country, Town, County, Address1, Address2, ClientType, ClientSize, ValidFrom, IsCurrent)VALUES (SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.County, Address1, Address2, ClientType, ClientSize, @Today, 1)WHEN NOT MATCHED BY SOURCE THENUPDATE SET DST.IsCurrent = 0, DST.ValidTo = @Yesterday;[/code]Also i suppose it will have problems with NULL values. And this UPDATE will always go through all records in target table that were deactivated before.

RE: Slowly changing dimensions using T-SQL MERGE

amenjonathan — Wed, 22 Jun 2011 14:29:30 GMT

Probably won't work because what if your key's don't match? I would imagine they won't all be the same key, so you'll then end the time period for every row in your dimension and make all records not active?First you have to have a match on a key. At that point you can check to see if the other fields do not match.

RE: Slowly changing dimensions using T-SQL MERGE

orilefka — Wed, 22 Jun 2011 14:16:38 GMT

Thank you! Great article!I have question about SCD type 2.[b]Can we compare all columns in ON clause like this:[/b][code="vb"]MERGE INTO dbo.Client_SCD2 AS DSTUSING dbo.Client AS SRCON SRC.ID = DST.BusinessKeyAND DST.ClientName=SRC.ClientNameAND DST.Country = SRC.CountryAND DST.Town=SRC.TownAND DST.Address1=SRC.Address1AND DST.Address2=SRC.Address2AND DST.ClientType=SRC.ClientTypeAND DST.ClientSize=SRC.ClientSize[/code][b]and then use [/b][code="vb"]WHEN NOT MATCHED BY SOURCE THENUPDATE SET DST.IsCurrent = 0, DST.ValidTo = @Yesterday[/code][b]rather than having this part of code:[/b][code="vb"]WHEN MATCHED AND IsCurrent = 1AND ( ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'') OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'') OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'') OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'') OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'') OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'') OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'') )[/code]

RE: Slowly changing dimensions using T-SQL MERGE

amenjonathan — Tue, 21 Jun 2011 12:17:00 GMT

Actually you can do both. Put all the fields you wish to carry history on in the ON clause for matching. This will tell the MERGE you want a new record any time there is something that doesn't match, which is when you have new information in a field you wish to carry history on. For update only fields put an update clause on the fields you wish to update in the when matched clause. For static fields, just leave them in the insert. Don't make them part of the update.Easier if I just show you the template I use on my MERGE statements:[code="plain"]--JA: MERGE source with destinationMERGE dbo.DimCustomer DestinationUSING --JA: source ( SELECT DC.[HistoryCol1] ,DC.[UpdateOnlyCol1] ,DC.[HistoryCol2] ,DC.[HistoryCol3] ,DC.[UpdateOnlyCol2] ,DC.[StaticCol1] FROM dbo.DimCustomerStage DC )AS [Source] ( [HistoryCol1] ,[UpdateOnlyCol1] ,[HistoryCol2] ,[HistoryCol3] ,[UpdateOnlyCol2] ,[StaticCol1] )ON ( [Source].[HistoryCol1] = Destination.[HistoryCol1] AND [Source].[HistoryCol2] = Destination.[HistoryCol2] AND [Source].[HistoryCol3] = Destination.[HistoryCol3] )WHEN MATCHED --JA: here i specify that in order to qualify for update, the binary sum of the two rows must not be equal (a change has occured in source) - checksum is faster than checking every column individually AND BINARY_CHECKSUM ( Destination.[UpdateOnlyCol1] ,Destination.[UpdateOnlyCol2] ) != BINARY_CHECKSUM ( [Source].[UpdateOnlyCol1] ,[Source].[UpdateOnlyCol2] ) THEN UPDATE SET --JA: put only the columns you wish to update here - less = better performance Destination.[UpdateOnlyCol1] = [Source].[UpdateOnlyCol1] ,Destination.[UpdateOnlyCol2] = [Source].[UpdateOnlyCol2]WHEN NOT MATCHED BY TARGET THEN --JA: when no matching id can be found, insert INSERT ( [HistoryCol1] ,[UpdateOnlyCol1] ,[HistoryCol2] ,[HistoryCol3] ,[UpdateOnlyCol2] ,[StaticCol1] ) VALUES ( [Source].[HistoryCol1] ,[Source].[UpdateOnlyCol1] ,[Source].[HistoryCol2] ,[Source].[HistoryCol3] ,[Source].[UpdateOnlyCol2] ,[Source].[StaticCol1] ); --JA: MERGE must be terminated by a semicolon[/code]Also if you plan on cubing your warehouse I've been told by Will Brown, a Microsoft expert in SSAS, you get some performance benefit from snowflaking your warehouse because then the cubes you build from it don't have to process as many distincts during cube processing. I guess it depends on how much of your querying will be via your cube versus directly from your warehouse.

RE: Slowly changing dimensions using T-SQL MERGE

Chris Harshman — Tue, 21 Jun 2011 09:37:18 GMT

[quote][b]David Greenberg-358875 (6/20/2011)[/b][hr]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...Can you handle this scenario within the same merge statement?[/quote]The short answer is no. The problem is, in the MERGE construct, you can only have one WHEN MATCHED section. This method uses the WHEN MATCHED section to mark the "current" record in your slowly changing dimension table as no longer current, then in the OUTPUT clause the new values for this record are sent to the INSERT statement "outside" of the MERGE. We use this method for our data warehouse, but our ODS is on the same server.Snowflaking your dimensional model as was suggested may be tempting, but in essence what you're doing then is optimizing your model for the ETL instead of the reporting, and it is likely that the extra join will hurt performance when it is queried.

RE: Slowly changing dimensions using T-SQL MERGE

gary.strange-sqlconsumer — Mon, 20 Jun 2011 14:22:08 GMT

Great article Adam.Regarding cross server merging. Isn't it best to use a ETL tool for ETL tasks, making use of in-memory caching, instead of a linked server?Regarding checksum comparisons how do you handle collisions. I.E. the "Birthday Paradox"? I've used 'md5' or 'sha*' in the past but never checksum because of the increased collision probability. Do you have code that resolves collisions?

RE: Slowly changing dimensions using T-SQL MERGE

amenjonathan — Mon, 20 Jun 2011 09:01:31 GMT

In my experience, you should always stage your data on the local server before doing any manipulation and / or merging. So for dawalker, extract new information in native form from the source server to your warehouse server before you transform and load.I also use checksum to detect changes. Much faster!

RE: Slowly changing dimensions using T-SQL MERGE

yosiasz — Mon, 20 Jun 2011 08:34:00 GMT

I concur what dawalker-1068762 has said. MERGE is a wonderful command but use carefully. If the source (SRC ) table is small and light it is feasable and nice to use. But if the table has millions of rows then you will hit a performance snag.To get around this we are using MERGE along with a batch process. User very carefully! and design things in batches.Wish MERGE had a parameter to do batch processing!

RE: Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Mon, 20 Jun 2011 08:16:44 GMT

Hi DEK46656,You are absolutely right that there are many other ways of comparing data - I prefer SHA1 hashes, if possible in computed columns, myself. Here, of course, the accent was on the more basic MERGE concept, but you are spot on to point out that this is only a starting point, and that there is such a lot that you can do with the basic concept to make it really efficient and useful.Regards,Adam

RE: Slowly changing dimensions using T-SQL MERGE

DEK46656 — Mon, 20 Jun 2011 08:09:02 GMT

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:[code="sql"]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)[/code]Or[code="sql"]CHECKSUM(DST.*) <> CHECKSUM(SRC.*)[/code]I typically use the individual fields approach myself.

RE: Slowly changing dimensions using T-SQL MERGE

al kv — Mon, 20 Jun 2011 08:08:42 GMT

Adam,Thank you.

RE: Slowly changing dimensions using T-SQL MERGE

DEK46656 — Mon, 20 Jun 2011 07:54:53 GMT

[quote][b]David Greenberg-358875 (6/20/2011)[/b][hr]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.[/quote]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.

RE: Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Mon, 20 Jun 2011 07:51:16 GMT

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

RE: Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Mon, 20 Jun 2011 07:48:45 GMT

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

RE: Slowly changing dimensions using T-SQL MERGE

al kv — Mon, 20 Jun 2011 07:37:35 GMT

where can I get the CarSales db?

RE: Slowly changing dimensions using T-SQL MERGE

David Greenberg-358875 — Mon, 20 Jun 2011 06:45:17 GMT

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?

RE: Slowly changing dimensions using T-SQL MERGE

dawalker-1068762 — Mon, 20 Jun 2011 05:27:51 GMT

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.

RE: Slowly changing dimensions using T-SQL MERGE

Franky Leeuwerck — Mon, 20 Jun 2011 03:59:02 GMT

Great article Adam !Thanx for sharing.Franky

Slowly changing dimensions using T-SQL MERGE

Adam Aspin — Mon, 20 Jun 2011 00:00:41 GMT

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