3 Replies Latest reply: Mar 22, 2013 10:53 AM by Sergejs Kazakevics RSS

    How to Create Slowly Changing Dimension

    Sergejs Kazakevics



      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.



        • Re: How to Create Slowly Changing Dimension
          Henric Cronström

          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.





            • Re: How to Create Slowly Changing Dimension
              Sergejs Kazakevics

              Hi Henric,


              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.


              Best Regards,


              • Re: How to Create Slowly Changing Dimension
                Sergejs Kazakevics

                Hi Henric,


                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);



                Load _Key

                    //,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

                Resident COGS_HISTORY

                While IterNo() <= H_VALID_TO - H_VALID_FROM + 1



                Can you suggest anything about this?

                Thank you very much in advance.