May 30, 2019 at 10:11 pm
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:
MonthTenureGroup1TenureGroup2TenureGroup3TenureGroup4
10-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
20-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
30-3 Mth 0-6 Mth 0-1 Yr 0-2 Yr
44-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
54-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
64-6 Mth 0-6 Mth 0-1 Yr 0-2 Yr
77-9 Mth 7-12 Mth 0-1 Yr 0-2 Yr
87-9 Mth 7-12 Mth 0-1 Yr 0-2 Yr
97-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
)
May 30, 2019 at 10:22 pm
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.
May 30, 2019 at 10:49 pm
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.
May 31, 2019 at 1:16 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply