1 Reply Latest reply on Jun 30, 2012 2:05 PM by Russell Christopher

    How to deal with Slowly Changing Dimension (SCD)?

    Thai Huynh

      Here is a simple scenario of SCD (Slowly Changing Dimension). I would like to know how you manage them.

       

      Jane and Jill work for Manager Billy during 2011

      In January, Jane recorded sales of $0.5 and Jill recorded $1

       

      In December of same year, Jill transferred to Manager Bob and recorded $2 of sales

       

      So... year end report:

      1. Did Manager Bob's group record $3 of sales (Jill's $1 in January and $2 in December) or should only be $2?
      2. Did Manager Billy's group record $0.5 or should be $1.5 ($0.5 from Jane and $1 from Jill) ?

       

      Total sales for 2011 is $3.50 but how to NOT under-report for Manager Billy and NOT over-report for Manager Bob?

       

      So what's your best practices to handle situation like this?

      What about creating a calculated field to hold the manager name for each sales transaction? To do this I would use the date stamp on sales table with a table which has manager-employee reporting relationship and also an effective date. 

        • 1. Re: How to deal with Slowly Changing Dimension (SCD)?
          Russell Christopher

          HI there, Thai -

           

          Are you working with a data source in which the SCD has already been modeled? Generally, the way the data warehouse designer chose to model the slowly changing dimension will influence how you work with it in Tableau.

           

          In the scenario you mention, it is not uncommon for the "original" employee record for "Jill working for Bill" to be "expired" as of January with a combination of two fields in the employees table:

           

          - A true/false field indicating active/expired status

          - A "date of expiration" field which contains the date the employee left/moved or is NULL for an active record

           

          ...So when Jill moved in Jan, her original employee record would be expired, and a brand new one would be for Bob.

           

          If you go this route, Jill would show up "automatically" under both Bill AND Bob during the correct time periods she worked for them.

           

          I took a guess at how your DBA chose to set this up since the approach I mentioned is considered by many a best practice - if I guessed wrong, please tell us how it is setup?