Disclaimer:  I didn't tke the time to read your whole article, so please forgive me if you address this.

My concern is about the "BINARY_CHECKSUM()" function.  Will this option always tell you when data has changed?  Consider a "pathological" worst case where "Attribute1" is set to "... San Diego, CA 91311" for one data row.  Let's say the data row gets changed to "... San Diego, CA 91302".  Here the ASCII value of one byte (1) was decremented by one to (0) and then the ASCII value of the next byte (1) was incremented by one to (2) -- with a net change of zero for the computed checksum.  Will this case be correctly flagged as data changed?

I don't get it. Isn't MERGE supposed to do the insert already, then why the INSERT ststement above it? The purpose of this statement is to do the I/U/D operations in one single shot based on the rules defined and by joining the target table rows with the source rows. The only output from MERGE would be the results of the changes from the OUTPUT clause. Is that needed in the target table too?

From my understanding what that INSERT from MERGE statement will do is update the target table and then insert the rows that were updated and returned by the OUTPUT clause again into it. Is that really the intention?

I kind of agree with the first post made by "the sqlist".  Normally, when I've used the MERGE statement in the past, it is pretty much a self-contained statement that does the INSERT, UPDATE, and/or DELETE.  Not fully sure why one needs to use a MERGE inside an INSERT statement. 

You can create a "derived table" expression within the USING-clause as necessary.  And it's  easy if you just code your UPDATE section based upon the PK in the target table.   Here's a sample of a simple call:

   MERGE INTO [dbo].[dimCUSTOMER_EMAIL] AS TGT
   USING
   (
      SELECT
         C.CUSTOMERID  ,
         C.LASTNAME      ,
         C.FIRSTNAME     ,
         CE.EMAIL 
       FROM dbo.dimCUSTOMER C           
       INNER JOIN dbo.CUSTOMER_EMAIL CE  ON CE.CUSTOMERID = C.CUSTOMERID
      
   ) AS SRC(CUSTOMERID, LASTNAME, FIRSTNAME, EMAIL)
     ON  TGT.CUSTOMERID  = SRC.CUSTOMERID       
   WHEN MATCHED THEN
      UPDATE
      SET
         TGT.LASTNAME   = SRC.LASTNAME ,
         TGT.FIRSTNAME  = SRC.FIRSTNAME,
         TGT.EMAIL      = SRC.EMAIL
   WHEN NOT MATCHED THEN
      INSERT (CUSTOMERID, LASTNAME, FIRSTNAME,  EMAIL)
      VALUES (SRC.CUSTOMERID, SRC.LASTNAME, SRC.FIRSTNAME, SRC.EMAIL)
   WHEN NOT MATCHED BY SOURCE THEN
      DELETE;

The reason why he has the merge inside the insert is because the update in the merge isn't the data update. It's just updating the existing record and invalidating it as the current record. In other words, keeping a history of record updates. You'll note it flags the record as 'N' and updates the enddate. This effectively archives this record to determine when it last changed and what values it had at that time.

It's the outer insert itself which 'updates' the table with the current data record.

http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_II

Thaky you both for clarifications. I now know what a Type 2 Slowly Changing Dimension is. I actually used it a lot in my experience, also the The Type 6 pure one, but I never was not familiar with these terms.

I have to admit that using the output result from MERGE statement like that is very clever and it also must be well performing compared to other, more clasic, methods.

I understand the well-documented concerns about CHECKSUM() or BINARY_CHECKSUM() failing to detect changes in records.  However, I used the BINARY_CHECKSUM() as shown in the tip on a 4 million records and growing table with 30+ attributes passed to the function for almost four years in a production environment and it never failed to detect a change. 

With a Type 2 Slowly Changing Dimension (SCD), the idea is to track the changes to (or record the history of) an entity over time.  To accomplish this tracking, rows should never be deleted and the attributes are never updated.  Instead, changes in the data are applied through the end-dating of the existing current record and by flagging the record as no longer being current; while a new record is inserted with the changes in the attributes.

The INSERT/MERGE code above accomplishes the goals of maintaining a Type 2 SCD with a “minimal” amount of code to execute.

If you are worried about the checksums you can use the following instead of the checksum comparison in the "WHEN MATCHED" clause:

WHEN MATCHED AND NOT EXISTS (
   SELECT
   source.Attribute1,
   source.Attribute2,
   source.Attribute3
   
   INTERSECT
   
   SELECT
   target.Attribute1,
   target.Attribute2,
   target.Attribute3
   )
AND target.CurrentRecord='Y'

This handles nulls also.

Take a look at http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

I have no issue with the article for what it is demonstrating. What I do have issue with is passing this off as something to do in a data warehouse. A data warehouse is no place for a dimensional model. The dimensional model fails at so many levels when trying to do a temporal data warehouse. If you are interested in implementing an actual temporal data warehouse, then I suggest you read Temporal Data & the Relational Model by CJ Date (http://www.amazon.com/Temporal-Relational-Kaufmann-Management-Systems/dp/1558608559). Assuming you have a solid relation background it shouldn't be too hard to extend which ever RDBMS you work with to handle temporal data.

Nice post

I love the merge statement however, i am confused on some part, I am using a SP to do my SCD, where i call the SP but i do understand that merge statement is good.

My question now is that, when i have  additional columns to my dimensions  namely:,

ValidFrom

ValidTo

IsActive

With this three coloumns  above , how can i update the ValidFrom when it expires or new records comes in?Because most post i have seen dont have endDate, StartDate etc.

Any help or explanation on how to update those three columns will be usefull.

Prince10,

Based on what you are stating, I believe your ValidFrom column is synonymous to my EffectiveDate column, your ValidTo column is synonymous to my EndDate column, and your IsActive column is synonymous to my CurrentRecord column.  Please refer to how I handle these columns above.

I hope this helps.

Dallas

Hello Dallas,

Thanks for your respond. i have the below code that am using, tell me what the difference is between the merge and the insert statement below

GO
/****** Object:  StoredProcedure [dbo].[LoadDimDepot]    Script Date: 03/30/2013 15:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LoadDimDepot]
AS

DECLARE @CurrentTime DATETIME
SET @CurrentTime = GETDATE()

--STEP 1 Insert rows for source rows with changed attributes

Insert into dbo.DimDepot
(
DepotID,
DepotName,
AddressL1,
AddressL2,
TownCity,
County,
PostCode,
Country,
ValidFrom,
ValidTo,
IsActive
)

SELECT S.DepotID,
ISNULL (S.DepotName,'Uknown') as DepotName,
ISNULL (S.AddressL1,'Uknown') as AddressL1,
ISNULL (S.AddressL2,'Uknown') as AddressL2,
ISNULL (S.TownCity,'Uknown') as TownCity,
ISNULL (S.County,'Uknown') as County,
ISNULL (S.PostCode,'Uknown') as PostCode,
ISNULL (S.Country,'Uknown') as Country,
GETDATE() as ValidFrom, '9999-12-31' as ValidTo, 2 as IsActive

FROM dbo.[Staging.FinalDimDepot] S
INNER JOIN dbo.DimDepot D ON D.DepotID=S.DepotID
WHERE D.DepotID=S.DepotID AND IsActive= 1 AND
(
ISNULL (D.DepotName,'Unknown')<> ISNULL (S.DepotName,'Uknown')
OR ISNULL (D.AddressL1,'Unknown')<> ISNULL (S.AddressL1,'Uknown')
OR ISNULL (D.AddressL2,'Unknown')<> ISNULL (S.AddressL2,'Uknown')
OR ISNULL (D.TownCity,'Unknown')<> ISNULL (S.TownCity,'Uknown')
OR ISNULL (D.County,'Unknown')<> ISNULL (S.County,'Uknown')
OR ISNULL (D.PostCode,'Unknown')<> ISNULL (S.PostCode,'Uknown')
OR ISNULL (D.Country,'Unknown')<> ISNULL (S.Country,'Uknown')
)

--- STEP 2. Expire active rows if attributes are changed.

UPDATE dbo.DimDepot
set ValidTo = @CurrentTime, IsActive = 0
FROM dbo.[Staging.FinalDimDepot]S
WHERE DimDepot.DepotID=S.DepotID AND IsActive=1 AND
(
ISNULL (DimDepot.DepotName,'Unknown')<> ISNULL (S.DepotName,'Uknown')
OR ISNULL (DimDepot.AddressL1,'Unknown')<> ISNULL (S.AddressL1,'Uknown')
OR ISNULL (DimDepot.AddressL2,'Unknown')<> ISNULL (S.AddressL2,'Uknown')
OR ISNULL (DimDepot.TownCity,'Unknown')<> ISNULL (S.TownCity,'Uknown')
OR ISNULL (DimDepot.County,'Unknown')<> ISNULL (S.County,'Uknown')
OR ISNULL (DimDepot.PostCode,'Unknown')<> ISNULL (S.PostCode,'Uknown')
OR ISNULL (DimDepot.Country,'Unknown')<> ISNULL (S.Country,'Uknown')
)

-- Step 3. Update IsActive = 2 rows to 1
UPDATE dbo.DimDepot SET IsActive = 1 where IsActive = 2

-- -- Step 4. Insert new rows

INSERT INTO DimDepot
(
DepotID,
DepotName,
AddressL1,
AddressL2,
TownCity,
County,
PostCode,
Country,
ValidFrom,
ValidTo,
IsActive
)

SELECT DepotID,
ISNULL (DepotName,'Uknown') as DepotName,
ISNULL (AddressL1,'Uknown') as AddressL1,
ISNULL (AddressL2,'Uknown') as AddressL2,
ISNULL (TownCity,'Uknown') as TownCity,
ISNULL (County,'Uknown') as County,
ISNULL (PostCode,'Uknown') as PostCode,
ISNULL (Country,'Uknown') as Country,
'1900-01-01', '9999-12-31', 1

FROM dbo.[Staging.FinalDimDepot]
WHERE DepotID NOT IN
(SELECT DISTINCT DepotID FROM dbo.DimDepot)