Products that change Product ID's HELP


  • So i came across my first spanner in the works in building my product dimension

    our products can be listed on our website as Live or Draft status.

    Live product are visible to everyone and available for purchase, but a store manager whos store sells those products can bring products back to Draft which means they cannot be seen to buy the public.

    When in Draft mode there attributes can be edited color, size etc. once the manager is happy with the edit they can push them back to Live status (visible again and purchasable).

    my problem is whenever a product goes from live to draft this products IsActive flag is set 0. When the same product then goes from draft back to live a new record is inserted in the product table with an IsActive flag set to 1 indicating this is the live one, and the process can go on.

    so now i have two record for the same product with two different product ids going into my product dimension, i can track the scd2 changes on the products using the surrogate keys, and also report on them individually but how would i report on them as the one product which really they are? or do i have to accept if they go through this process they have to be treated separately?
    Thursday, June 05, 2014 7:30 PM

All replies

  • >so now i have two record for the same product with two different product ids going into my product dimension,

    Why is the product ID different? It should be the same.

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, June 05, 2014 8:25 PM
  • I dont know why it has been done like this but it has.

    when the product is marked as draft it is actual getting its IsActive flag set to 0 in the products table and the row is then is then inserted into a staging table. When the product is then marked back to live, this record in the staging table is then inserted back into the product table with its IsActive flag set to 1. so the INSERT is why it gets a new productid.

    if the original record in the product table was updated i wouldnt have a problem but it isnt

    Thursday, June 05, 2014 8:36 PM