3 Replies Latest reply: Oct 13, 2015 12:26 AM by Vishnu Musani RSS

    SCD 2 Implementation with multiple tables

    Vishnu Musani

      HI,

       

      I am able to build a model with 2 Dimension Tables with a fact table. but when i am trying to follow similar approach for combining 3 Dimension table it is causing issues. My Employee table is dependent on both other table information.

       

      Below is the table information

       

      Employee:

      EID, OrgID, PosID, From_Date,  To_Date

       

      Organization:

      OrgID, From_Date, To_Date

       

      Position

      PosID, From_Date,To_Date

       

      Could some one please suggest a way to accomplish this,

       

      Regards,

      Vishnu.

        • Re: SCD 2 Implementation with multiple tables
          Sergey Pokasov

          Hello!

           

          It depends on your task.

          If Organization and Position are only for Employee then you can join them to Employee.

           

          What fields are in the fact table?

            • Re: SCD 2 Implementation with multiple tables
              Vishnu Musani

              Hi Sergey,

               

              Thanks for responding.

               

              I do have total 5 Master data tables and all those are time dependent. In Employee table i have all other MD tables unique key and also all unique keys of these are available in my Transaction table.

              MD Tables: Employee, Organization, Person, Position, Job.

              Transaction : One Table

               

              Could you please let me know what could be the approach to build the model by considering the time dependencies.

               

              Employee:

                        

              EmployeeVValid toValid fromEmployee GroupEmployee SubgroupEmployment StatusPosition     JobOrganizational Unit  Person
              Emp1A29.03.196901.01.1000
              Emp1A07.10.199330.03.1969
              Emp1A28.02.200708.10.1993AXZ3Pos3 Per1
              Emp1A31.12.200801.03.2007AXZ3Pos2 Per1
              Emp1A18.01.201101.01.2009AXZ3Pos1 Org1Per1
              Emp1A31.10.201119.01.2011AXZ3Pos1 Org1Per1
              Emp1A31.03.201201.11.2011AXZ3Pos1 Org1Per1
              Emp1A31.10.201201.04.2012AXZ3Pos1 Org1Per1
              Emp1A04.01.201301.11.2012AXZ3Pos1 Org1Per1
              Emp1A31.03.201305.01.2013AXZ3Pos1Job1Org1Per1
              Emp1A30.09.201301.04.2013AXZ3Pos1Job1Org1Per1
              Emp1A31.10.201301.10.2013A5B3Pos1Job1Org1Per1
              Emp1A31.12.201301.11.2013A5B3Pos1Job1Org1Per1
              Emp1

              A

              31.03.201401.01.2014A5B3Pos1Job1Org1Per1

              Position:

                      

              PositionVValid toValid from JobOrganizational UnitJob FamilyFunctional Area
              Pos1A31.12.200801.01.1000
              Pos1A15.10.200901.01.2009Org12hm
              Pos1A31.12.201116.10.2009Org12nnvd
              Pos1A30.06.201301.01.2012Job12Org12ddcbcs
              Pos1A31.12.201301.07.2013Job12Org12sfwqqewddv
              Pos1A31.03.201401.01.2014Job12Org12wwdvbd
              Pos1A31.03.201501.04.2014Job64Org12wwbcbd
              Pos1A03.05.201501.04.2015Job12Org12wttwdbd
              Pos1A31.12.999904.05.2015Job34Org12wsyhrvdv
              Pos2A28.02.201501.01.1000Job45ddv
              Pos2A31.03.201501.03.2015Job22Org23ydbddbd
              Pos2A31.12.999901.04.2015Job23Org23yrdbd

               

              Person:

                      

                PersonVValid toValid fromLocationDate of BirthPostal CodeRegion (State, Provi
              Per1A07.10.201501.01.2014
              Per1A29.01.201208.10.201130.03.19690
              Per1A31.12.201330.01.2012Loc1230.03.196985504-5000

               

              Job:

                    

                   JobVValid toValid fromChange flag ( I inserted / D deleted )Job Group
              Job1A31.12.201301.01.2013XJJ
              Job1A31.12.201201.01.2012YJK
              Job2A31.12.201401.01.2014ZHG

               

              Organzation:

                    

              Organizational UnitVValid toValid fromCO Area of MCCtrMaster Cost Center
              Org1A31.12.201401.01.2014
              Org1A12.02.201301.01.201350MC1
              Org1A31.12.201213.02.201250MC2
              Org2A28.02.201501.01.2015MC4
              Org2A31.12.201501.03.201550MC5

               

              Transaction:

                            

              Employee PersonNationalityPosition     JobOrganization UnitDAYYearMONTHMONTHYEARTerritoryCountryRegionEmployee Group
              Emp1Per1BRPos1 Org131.08.20122012088201245DD3A
              Emp1Per1BRPos1 Org130.06.20122012066201245DD3A
              Emp1Per1BRPos1 Org129.02.20122012022201245DD3A
              Emp1Per1BRPos1 Org131.12.201220121212201245DD3A
              Emp1Per1BRPos1 Org131.03.20122012033201245DD3A
              Emp1Per1BRPos1Job1Org131.12.201420141212201445DD3A
              Emp1Per1BRPos1Job1Org130.06.20142014066201445DD3A
              Emp1Per1BRPos1Job1Org131.08.20142014088201445DD3A
              Emp1Per1BRPos1Job2Org128.02.20142014022201445DD3A
              Emp1Per1BRPos1Job1Org101.08.20142014088201445DD3A
              Emp1Per1BRPos1Job2Org131.03.20142014033201445DD3A
              Emp1Per1BRPos1Job2Org130.06.20132013066201345DD3A
              Emp1Per1BRPos1Job2Org131.12.201320131212201345DD3A
              Emp1Per1BRPos1Job2Org131.03.20132013033201345DD3A
              Emp1Per1BRPos1Job2Org131.08.20132013088201345DD3A
              Emp1Per1BRPos1Job2Org128.02.20132013022201345DD3A
            • Re: SCD 2 Implementation with multiple tables
              Vishnu Musani

              Re Posting Original Employee table as some fields are missed

               

                        

              EmployeeVValid toValid fromPosition     JobOrganizational Unit  PersonEmployee GroupEmployee Subgroup
              Emp1A29.03.196901.01.1000
              Emp1A07.10.199330.03.1969
              Emp1A28.02.200708.10.1993Pos3 Per1AXZ
              Emp1A31.12.200801.03.2007Pos2 Per1AXZ
              Emp1A18.01.201101.01.2009Pos1 Org1Per1AXZ
              Emp1A31.10.201119.01.2011Pos1 Org1Per1AXZ
              Emp1A31.03.201201.11.2011Pos1 Org1Per1AXZ
              Emp1A31.10.201201.04.2012Pos1 Org1Per1AXZ
              Emp1A04.01.201301.11.2012Pos1 Org1Per1AXZ
              Emp1A31.03.201305.01.2013Pos1Job1Org1Per1AXZ
              Emp1A30.09.201301.04.2013Pos1Job1Org1Per1AXZ
              Emp1A31.10.201301.10.2013Pos1Job1Org1Per1A5B
              Emp1A31.12.201301.11.2013Pos1Job1Org1Per1A5B
              Emp1A31.03.201401.01.2014Pos1Job1Org1Per1A5B