SQLServerCentral / Discuss Content Posted by Vincent Rainardi / Article Discussions / Article Discussions by Author / Upsert Dimension Table / Latest Posts

RE: Upsert Dimension Table

VincentRainardi — Sun, 07 Jan 2007 16:03:00 GMT

Hi everybody, apologise I came late to the discussion.Calvin, I'm not sure why the link is missing but the article is at http://www.sqlservercentral.com/columnists/vRainardi/2732.asp. Steve, the link to the article is still missing today (7th Jan).

Uday, as Rasool said, to upsert a dimension in SSIS you can use SCD transformation such as http://www.microsoft.com/library/media/1033/technet/images/prodtechnol/sql/2005/s4_big.png, or as Alex and Robert said we can also use Lookup transformation.About your last post where an update is recognised as an insert, in the SSIS SCD Wizard did you choose the Change Type for that column as 'Changing Attribute' or as 'Historical Attribute'? And did you tick the 'Change all the matching records, including outdated records, when changes are detected in a changing attribute' check box?

Loner, the surrogate_key column which has 0 value in it is a temporary table, not the target dimension table. Yes you are right I forgot to include the natural key column. Thank you for your correction. The correct insert statement isINSERT INTO #dim (surrogate_key, natural_key, attribute1, attribute2, load_time)SELECT ISNULL(dim.surrogate_key,0), src.natural_key, src.attribute1, src.attribute2, @current_load_timeFROM stg.dbo.source srcLEFT JOIN dw.dbo.dimension dim ON src.natural_key = dim.natural_keyYes we can also do it using the approach that you described, Loner, i.e. the temp table doesn't have to have the surrogate key and we leave the key assignment to identity of the target dimension table. SCD3 is normally use if we only require certain level of history, for example, if we want to keep only the last 2 region for a particular branch. So if branch A is allocated to region 1 in 2001, then to region 2 in 2002 (perhaps because of reorganisation or restructurisation), then to region 3 in 2003, and we only want to keep the last 2 regions, then SCD3 could be a technique that we could use. So we don't normally add a column every time. If we want to keep the last 2 values for example, then we created 3 sets of columns since the beginning (1 set is for the current value). But you are right, if we want to keep the last 10 values then the structure would be a big table.

Cliff, I agree with Loner that we can use checksum instead of or, which is cleaner as you said. I am not sure that it is quicker though. Loner, you are right, we should use natural key when comparing rows in dimension table. The purpose of 'WHERE tmp.surrogate_key <> 0' is not for comparing, but to update the rows where the tmp table’s surrogate key is not 0, i.e. the rows already exist on the dimension table. For the rows where the surrogate key is 0 (not exist on the dimension table), we will insert them into the dimension table later on.

Ah, Uday, I saw that Rasool already explained 'Changing Attribute' or as 'Historical Attribute' and how to tag the current records, in his last posting.

RE: Upsert Dimension Table

Rasool Omidakhsh — Fri, 01 Dec 2006 08:46:00 GMT

It all depends on the SCD type. If you chose TYPE I (overwrite) then in SSIS SCD wizard you chose <changing attributes> which basically means update existing record. However if you are dealing with TYPE II then you chose <Historical Attribute> which means add a new record. Here you must have a way to distinguish the current record from the old records. Usually we design the dimension table with fields such as <current_flag> and configure SCD component to set the flag to “y” or “n”. Now here the assumption is that your businees key is not your primary key. If it is then you can only use Type I SCD and have to configure the SCD component to <changing attributes> and not <Historical Attribute>. Hope this helps.

RE: Upsert Dimension Table

Loner — Wed, 29 Nov 2006 06:10:00 GMT

cliffb,

I could use checksum, I was just in a hurry so I wrote it in using 'OR' especially there were only two values to check. However I would not use surrograte key to compare when I update the dimension table, I would use the natural key.

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 22:39:00 GMT

I tried using the Slowly Changing Dimension transormation. I basically need two paths - One to insert the new record ('New Output Path' to OLE DB Destination) and one to update changed attributes in an existing record based on a specified business key ('Changed Attribute Updates Output' to OLE DB Destination).

The insertion works fine but when i modify the same record in source data (retaining the same primary key) the data gets routed in the Changed Attribute path but fails with a primary key violation (it has attempted to insert into the destination table). Am i missing a setting or something that forces update rather than insert?

RE: Upsert Dimension Table

cliffb — Tue, 28 Nov 2006 09:27:00 GMT

Is there a reason you chose to use a list of "OR" vs CHECKSUM in your update statement? I realize CHECKSUM is not the same as CRC but it works just fine if comparing a single row.

Example:

UPDATE dimSET dim.attribute1 = tmp.attribute1, dim.attribute1 = tmp.attribute2,dim.load_time = tmp.load_timeFROM dw.dbo.dimension dimINNER JOIN #dim tmpON tmp.surrogate_key = dim.surrogate_keyWHERE tmp.surrogate_key <> 0AND CHECKSUM(dim.attribute1,dim.attribute2) <> CHECKSUM(tmp.attribute1,tmp.attribute2)

This is cleaner to read for SCD Type 1 changes and, at least in our environment, seems to be a bit quicker.

RE: Upsert Dimension Table

Rasool Omidakhsh — Tue, 28 Nov 2006 08:52:00 GMT

In SSIS you could use the <Slowly Changing Dimension> component which basically does the UPSERT.

RE: Upsert Dimension Table

Loner — Tue, 28 Nov 2006 07:59:00 GMT

The surrograte key is an identity field, how can it have zero in it?

What I usually do is loading all the data in a staging table. Then using left join to insert the new records into the dimension table. The dimension table does not need to have the surrogate key.

INSERT INTO Dim(Natural_key, attribute1, attribute2, load_time)SELECT s.natural_key, s.attribute1, s.attribute2, GETDATE()FROM staging_table sLEFT JOIN dw.dbo.dimension dim ON s.natural_key = dim.natural_key WHERE dim.natural_key is NULL

BTW, your step 2 insert statement, did you forget to insert the natural_key?

If the records are already in the dimension table, then update the record with the most recent information.

Update d set d.attitute1=s.attitute1, d.attitute2 = s.attitute2, d.load_time=GETDATE() FROM dw.dbo.dimension d inner join staging_table s where (d.natural_key = s.natural_key) and (d.attitute1 <> s.attitute1 or d.attitute2 <> s.attitute2)

I don't know what kind of benefit to do SCD3. I think the effective date and expiration date is good enough in dimension table. In SCD3 you are changing the dimension table structure. What if the attribute changes 10 times, you would have a big table.

RE: Upsert Dimension Table

Steve Jones - SSC Editor — Tue, 28 Nov 2006 07:26:00 GMT

OK, OK, apologies. That was the weirdest thing. The process we use to load the articles is generating an old one on disk for some reason. Did it again this morning and I didn't catch it last night. It's up there now.

RE: Upsert Dimension Table

Peter Kuzniewski — Tue, 28 Nov 2006 04:33:00 GMT

Steve, I think one of your indexes is slightly sick. We already know all about the sale. Now, we need the Upsert article, please.(We all hope this is NOT a sign of things to come)Regards,Peter

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 04:31:00 GMT

I'm not handling such large data. But will be mindful of that... thanks for the warning.

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 04:29:00 GMT

Oh cool! I should have thought of that before... thanks a lot :o)

RE: Upsert Dimension Table

Alan Hargreaves — Tue, 28 Nov 2006 04:16:00 GMT

SSIS is memory hungry so is you use the LookUp trans will you will need to be mindful of how SSIS will chew all your memory. I've used the process I outlined above on up to 10 million rows (which is not a lot) but SSIS manages this very well and very fast.

Simply build your package using Execute SQL taks between dataflows to faciliate my process.

RE: Upsert Dimension Table

esbob cz — Tue, 28 Nov 2006 03:59:00 GMT

Hi, use Lookup transformation on SSIS data flow page with the referential table. It is possible to update in standard output flow or insert in configured error flow.

Robert

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 03:46:00 GMT

My question was not how do it in the DB directly. Im trying to figure out if there is a way in Sql Server Integration Services (DTS) to do the same using a transformation.

RE: Upsert Dimension Table

Alan Hargreaves — Tue, 28 Nov 2006 03:34:00 GMT

Delete from the target table where the same data exists in the source table. At this point if the source row = target row its ok. If source row has updated values you dont care either becuase the next step is to insert all source rows to the target which is essentially overwritting rows that have been updated within the source.

The easiest way of all however would be to use some kind of auditing within you loads from source to target. But we dont live in a perfect world.

If I could VPN to a client site I would add a sample package but again, we dont live in a perfect world!!!!!

RE: Upsert Dimension Table

Calvin Lawson — Tue, 28 Nov 2006 03:07:00 GMT

Oh, upsert...thought this was the upskirt discussion.But seriously, what's wrong with the following?Update eset ColumnName = i.ColumnNamefrom ImportTable iJOIN TableName e on i.uniqueID = e.uniqueID Insert TableName (ColumnName)select i.ColumnNamefrom ImportTable iLEFT JOIN TableName e on i.uniqueID = e.uniqueID where e.uniqueID is nullDatabase 101, but is there some newfangled way to do this in the new Analytics? You'd still need to build the dimension and fact tables using SQL (or something), right?

RE: Upsert Dimension Table

ALZDBA — Tue, 28 Nov 2006 02:03:00 GMT

same player shoot again .... hit ball when lights are on

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 01:50:00 GMT

Ok.... i havent the slightest idea what that meant!

RE: Upsert Dimension Table

John McC — Tue, 28 Nov 2006 01:47:00 GMT

The article is printed in one of the fullstops via microfilm (lol)

RE: Upsert Dimension Table

uday balaji — Tue, 28 Nov 2006 01:21:00 GMT

Hi,

I urgently need to know how to carry out an UPSERT in SSIS 2005. If anyone knows the way to do it, please let me know as soon as possible.

TIA,

Uday.

RE: Upsert Dimension Table

Calvin Lawson — Tue, 28 Nov 2006 00:37:00 GMT

Is it just me, or is the article missing?

Upsert Dimension Table

VincentRainardi — Sat, 25 Nov 2006 11:55:00 GMT

Comments posted here are about the content posted at temp