Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Scd 3

  1. #1

    Default Scd 3

    Hi everybody,

    I would like to now if there is a way to implement "Slowing Changing Dimension" type 3 (history in columns) in Kettle ?

    For exemple, I hava a table with 4 columns (for Q1, ... Q4). If I extract my data in Feb I want to use column Q1. If I extract in April I want to use column Q2 (keeping value in Q1 !), ...

    Thx

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I think your definition of Type 3 is strange. (http://en.wikipedia.org/wiki/Slowly_...mension#Type_3)
    We don't do this the way you asked, but obviously you can add the logic yourself in the steps before the "Dimension lookup/update" step.
    I would strongly advice you NOT to store factual data like that in an SCD though.

    Matt
    Last edited by MattCasters; 04-04-2008 at 12:43 PM.

  3. #3

    Default

    Thanks Matt for your response.

    I know that it's not the best practice to store data in fact table like this, but it's for a specific need and I must do like that !

    Quote Originally Posted by MattCasters View Post
    you can add the logic yourself in the steps before the "Dimension lookup/update" step.
    You say I can add the logic, but it was my question I don't see how I can do that !
    For exemple if in my table I have a row :

    Year Month Q1 Q2 Q3 Q4
    2006 01 100 0 0 0

    In Q2, I have

    2006 01 0 120 0 0

    If I use a "Dimension lookup/update" I will have a row :
    2006 01 0 120 0 0 and not
    2006 01 100 120 0 0

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Bonjour Dam,

    I think you mix up some concepts. A SCD (slowly changing dimension) is NOT a fact table. If it would be a fact table, I would not have a problem with what you're doing :-)

    The type of dash-board / analytical fact table you're building typically involves some clever logic, probably in the form of JavaScript.

    The way you do it is do a lookup of the existing data in the (fact- but in your case dimension-) table. That leaves you 4 fields: Q1_lookup, ..., Q4_lookup.
    Then you calculate the new data in say monthly_total and the quarter_number (from the date dimension).

    JavaScript does the rest:

    Code:
    var q1 = Q1_lookup;
    var q2 = Q2_lookup;
    var q3 = Q3_lookup;
    var q4 = Q3_lookup;
    
    switch (quarter_number)
    {
    case 1 : q1 = q1 + monthly_total ; break;
    case 2 : q2 = q2 + monthly_total ; break;
     case 3 : q3 = q3 + monthly_total ; break;
     case 4 : q4 = q4 + monthly_total ; break;
     }
    q1, ..., q4 can then be used to store in the fact table. (or in your perverted case the dimension table :-)),

    Something like that should do it, didn't really test this sample myself, but I have been doing similar things previously.

    Cheers,
    Matt

  5. #5

    Default

    Thx a lot for your quick reply,

    I thought to use JavaScript but I wanted to know if it had another way to do this. So, I will use JavaScript.

    Thx Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.