I've got a problem of creating slowly changing dimension in qv. So I've got the source file with product_segments. And each product segment should have scd_start and scd_end dates. Initially these dates are predefined by the business people, but sometimes the names of the product segments are being changed and I have to log this change in my qvd. How can I get this done in the loadscript?
I've attached the file with an example of what I'm trying to achieve.
Any help would be much appriciated. Thank you in advance.
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.