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?
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
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.