3 Replies Latest reply: Jun 16, 2017 8:33 AM by bill priddy RSS

    Join or Concatenate or Both

    bill priddy

      I have an issue where we some fact tables all associated around Employee Data.  The tables are Actives, Departures, Transfers.  The data has data in in that is repeated in each.  IE Name, ID, Etc but each table represents different data.

       

      Would the recommended way be to use join or concatenate these all into one table?  Should this all be stacked into one fact table or have fields renamed to avoid the synthetic keys?  Open to suggestions/questions.  Thanks!

       

      Here is sample of my script as it is right now:

       

      Actives:

      LOAD *, 'Actives' as SRCTABLE;

      SQL SELECT "EMP_ID",

          "EE_NAME",

          "ORG_HIRE_DT",

          "REHIRE_DT",

          "ADJ_HIRE_DT",

          "EE_TYPE",

          "CST_CTR_ID",

          "DPT_NM",

          "JOB_TITLE",

          "JOB_CODE",

          "PSTN_NUM",

          "SUP_ID",

          "SUP_NM",

          BLDG,

          BU,

          "EML_ADDR",

          MAILCODE,

          YYYYMM

      FROM BDA.dbo."HR_ACTIVES";

       

      Departures:

      LOAD *, 'Departures' as SRCTABLE;

      SQL SELECT YYYYMM,

          "EMP_ID",

          "EE_NAME",

          "ORG_HIRE_DT",

          "REHIRE_DT",

          "DEPART_DT",

          "EE_TYPE",

          "CST_CTR_ID",

          "DPT_NM",

          "JOB_TITLE",

          "JOB_CODE",

          "PSTN_NUM",

          "SUP_ID",

          "SUP_NM",

          BLDG,

          BU

      FROM BDA.dbo."HR_DEPARTURES";