June 5, 2014 at 1:25 pm
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?
June 5, 2014 at 1:34 pm
ps_vbdev (6/5/2014)
So i came across my first spanner in the works in building my product dimensionour 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?
Given that all we know of your process is what you posted....who knows?
Please provide us some details so we know what you are trying to do. Please take a few minutes and read the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 2:14 pm
Sorry i thought i had listed the process clearly.
Im trying to build a Product dimension that has both type 1 and type 2 scd. Most product tables i have work on will have one unique product key for each product so that if any type 2 scd occur i can pick them up and insert them into my product dimension i can look at the change history of that product in my dimension using the natural key(productid) as it is unique for each product.
MY problem with this products table is because the product can be moved between a live and draft status (which actually marks the record inactive in the products table, moves it to a staging table) when the product is moved back to live (it is then moved from the staging table back to the products table) a new record is inserted into the products table which gives it a new productid (if the original record was updated then i wouldn't have a problem)
so now when i run my etl although this is the same product, just that it has been moved to draft to be edited and then made live again, because of the insert it now has a new Product id, in my product dimension it looks like an entirely new product.
So source product table with a live product
ProductID| Name | Color | IsActive
12345 a1 Red 1
source product table after that product is moved to draft (into a staging table)
ProductID| Name | Color | IsActive
12345 a1 Red 0
after that product is moved back to live (inserted back into product table)
ProductID| Name | Color | IsActive
12345 a1 Red 0
12346 a1 Green 1
its really like my actual sorce products table is tracking the changes to a degree before it gets to my etl. hope this make it clearer.
July 2, 2014 at 8:11 am
MMh... It depends what you are trying to do really. If you want to treat this as the same product but just track it as SCD2 then you need to find out how you can figure out that you're dealing with the same product. You need some sort of business rule to help you with that. For instance, is it the product name, or perhaps another candidate key (e.g., UPC, etc.)? This will allow you to reconcile these differences in your ETL and handle these cases any way you want.
If you track an aggregate fact with those you'll also need to reconcile across products though. For instance, add up the sales of your "old product" with the "new" one.
Anyway it's feasible, you just need to derive a rule and then do the work. If you use the SCD SSIS component I don't know how it works, but from a T-SQL or a Script task perspective, it's not complicated. You just need to have a rule you can rely on to do this.
Good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply