Best approach to model Tenure Groups

  • KevSSC

    SSC Enthusiast

    Points: 127

    Hello,

    What would be the best approach to model an Employee's Tenure group ?

    We have a fact table for Employee where we will store Employee's tenure ("TENUREMONTH" from dimension below). Users want to group the tenure into different buckets as shown below:

    Month	TenureGroup1	TenureGroup2	TenureGroup3	TenureGroup4
    1 0-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    2 0-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    3 0-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    4 4-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    5 4-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    6 4-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
    7 7-9 Mth 7-12 Mth 0-1 Yr 0-2 Yr
    8 7-9 Mth 7-12 Mth 0-1 Yr 0-2 Yr
    9 7-9 Mth 7-12 Mth 0-1 Yr 0-2 Yr

    Would below design suffice ? Or would there be any better approach ?

    CREATE TABLE DIMTENURE (
    TENUREMONTH INTEGER,
    TENUREGROUP1 VARCHAR(50) NOT NULL,
    TENUREGROUP2 VARCHAR(50) NOT NULL,
    TENUREGROUP3 VARCHAR(50) NOT NULL,
    TENUREGROUP4 VARCHAR(50) NOT NULL
    )
  • Martin Schoombee

    SSCoach

    Points: 19010

    Will it work? Yes, although I'd recommend the use of a surrogate key in the relationship between your dimension and fact.

    Not knowing your exact requirements, I do want to question why you have a fact table for tenure. Is it necessary to show an employee in each of these buckets as their tenure at the company increase? I'd be tempted to just store the tenure as an attribute of a type-2 SCD.

  • KevSSC

    SSC Enthusiast

    Points: 127

    Thanks Martin for the insight.

    I agree on the use of surrogate key the reason I didn't include a surrogate key is because MONTH acts like a durable natural key as well as a surrogate key similar to Calendar date dimension.

    Yes each Employee's tenure would be grouped into these different buckets by the users.

     

    • This reply was modified 1 month, 2 weeks ago by  KevSSC.
  • jonathan.crawford

    SSCertifiable

    Points: 6347

    I think it's odd to have them in four different buckets. You could define everyone under two years by just grouping all the people in the lower buckets together, no need to double count.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • KevSSC

    SSC Enthusiast

    Points: 127

    Yes the requirement from the users is to perform analysis on each of the buckets one at a time so they would need employees to be segregated into those individual groups.

    • This reply was modified 1 month, 2 weeks ago by  KevSSC.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply