This is not very difficult, but it takes some scripting.
First of all, you need to save the historical records - the records that tell you which Product Segments a Product used to belong to.
Then you need to load both your dimension table (in your case, the products with the validity intervals) and the transaction table (sales, inventory, etc.). Finally, you need to link the two tables using a table with exactly two keys: Product + Date (linking to the transaction table) and Product + Validity interval (linking to the dimension table). This"bridge table" can be created using a while loop (see blog post about looping over intervals)
My example below is about Salespeople changing regions/departments, but it is essentially the same problem.
This is very useful post and I'll definitely use this logic at the second stage. Sorry that was my fault I haven't explained the problem properly.
I've got a source file without scd_start and scd_end fields. So I have to set them while I'm loading the source file first time and then update scd_start and scd_end dates in future reloads in case if there were changes in the source file. So I've been trying to find any methods on web but with no result. If I could get any idea that would awesome.
Now I'm on this stage where COGS_X_DATES has exactly one record per combination of _Key(PIG&PRODUCT_SEGMENT) and Date.
What is the best way to link the SALES table to this schema If I want to see right COGS values at the time when order was made? And I don't realy understand how to build this Bridge table.
SALES table contain the fields like:
_DateKey,OrderID, ProductID, Product Segment, PIG
and have mltiple lines per orderid.
This is the code I'm using to create the schema above:
upper(replace(PIG&PRODUCT_SEGMENT,' ','')) as _Key,
PIG as H_PIG,
PRODUCT_SEGMENT as H_PRODUCT_SEGMENT,
round(TOTAL_VARIABLE,0.0001) as H_TOTAL_VARIABLE,
round(TOTAL_FIXED,0.0001) as H_TOTAL_FIXED,
round(FULL_UNIT_COST,0.0001) as H_FULL_UNIT_COST,
num(date(VALID_FROM,'DD/MM/YYYY')) as H_VALID_FROM,
num(date(VALID_TO,'DD/MM/YYYY')) as H_VALID_TO
(ooxml, embedded labels, table is COGS);
//,UPPER(REPLACE(_Key&num#(date(H_VALID_FROM+ IterNo()-1,'YYYYMMDD')),' ','')) as _KeySalesRef
,H_PIG as PIG
,H_PRODUCT_SEGMENT as PRODUCT_SEGMENT
,date(H_VALID_FROM + IterNo()-1) as Date
,num#(date(H_VALID_FROM+ IterNo()-1,'YYYYMMDD')) as _CogsDateKey
,H_FULL_UNIT_COST as FULL_UNIT_COST
,H_TOTAL_VARIABLE as TOTAL_VARIABL
,H_TOTAL_FIXED as TOTAL_FIXED
,H_VALID_FROM as VALID_FROM
,H_VALID_TO as VALID_TO
,num#(date(H_VALID_FROM,'YYYYMMDD')) as VALID_FROM_KEY
,num#(date(H_VALID_TO,'YYYYMMDD')) as VALID_TO_KEY
While IterNo() <= H_VALID_TO - H_VALID_FROM + 1
Can you suggest anything about this?
Thank you very much in advance.