Dje_be (2) [Avatar] Offline
#1
Hello,

This weekend I read this article about optimizing SSIS for dimensional data loads and I found the query listed in the listing 3 awesome.
After some testing, while it is correct syntax wise, unfortunately, it is not functionally correct.

The update within the MERGE statement updates all dimension columns values while setting up the CurrentFlag to 'N'.
This lead to the old dimension record to be "overwritten" with new incoming values making historical data unavailable - which is not the purpose of a SCD2.

Best regards,
Jerome
michaelcoles (2) [Avatar] Offline
#2
Re: Article 46 - Listing 3 - Using MERGE to feed INSERT for SCD2
The code sample on page 466-467 (Chapter 46, Listing 3) has a typo. The corrected listing should read:


INSERT INTO Dim.Product_Type2
(
  UPC, Category, Manufacturer,
  ProductName, Size, Color,
  Price, Hash, StartDate, CurrentFlag
)
SELECT UPC, Category, Manufacturer,
  ProductName, Size, Color,
  Price, Hash, GETDATE(), 'Y'
FROM
(
  MERGE INTO Dim.Product_Type2 AS Target
  USING Staging.Product AS Source
  ON Source.UPC = Target.UPC
  WHEN MATCHED AND Source.Hash <> Target.Hash AND Target.CurrentFlag = 'Y'
    THEN UPDATE SET Target.CurrentFlag = 'N'
  WHEN NOT MATCHED
    THEN INSERT
    (
      UPC, Category, Manufacturer,
      ProductName, Size, Color,
      Price, Hash, StartDate, CurrentFlag
    smilie
    VALUES
    (
      Source.UPC, Source.Category, Source.Manufacturer,
      Source.ProductName, Source.Size, Source.Color,
      Source.Price, Source.Hash, GETDATE(), 'Y'
    smilie
  OUTPUT $action, Source.UPC, Source.Category, Source.Manufacturer,
    Source.ProductName, Source.Size, Source.Color,
    Source.Price, Source.Hash
)
AS T
(
  action, UPC, Category, Manufacturer,
  ProductName, Size, Color,
  Price, Hash
)
WHERE action = 'UPDATE';


Sorry for the confusion.

Thanks
Michael Coles
michaelcoles (2) [Avatar] Offline
#3
Re: Article 46 - Listing 3 - Using MERGE to feed INSERT for SCD2
I'll try to get this posted to the Errata for the book.

Thanks
Michael Coles

null
Dje_be (2) [Avatar] Offline
#4
Re: Article 46 - Listing 3 - Using MERGE to feed INSERT for SCD2
Thank you very much for sharing this corrected version Michael.

Best regards,
Jerome