CODEWITHSUNDEEP
data-warehousedimensional-modeling
MattB
MattB

Reputation: 3

Type 2 Dimension changes relating to another dimension

I'm trying to model a recruitement data warehouse with a variety of type 2 dimensions, but I'm not sure if I'm modelling this particular scenario correctly. Here's what I'm doing so far:

I have 2 dimensions: Dim_Person and Dim_Client

These two dimensions are joined via a factless fact table Fact_PersonEmployer, which contains FKs to both dimensions, as well as a valid from and to date.

If a person moves to a different business, I close off the valid to date on the fact row that links them to their old employer, and insert a new record into the fact table with the new company.

That seems pretty straightforward, however as that person has now moved to a new employer I would think it warrants a type 2 change in the person dimension as the person is now fundamentally different to the user (a recruiter/recruiment manager).

From my perspective, it almost seems as if the client is a type 2 attribute of the person dimension, so I've been considering modelling it that way. I'm just not sure if it's acceptable to join dimensions together without using a factless fact table (i'm trying to stick to Kimball's methodology as much as possible).

Should I:

a) Keep the ID of the company they work for as an attribute in the person dimension so it can generate type 2 changes

or

b) Continue to use the fact table to associate the two dimensions with each other?

Hope this makes sense...

Thanks in advance!

Upvotes: 0

Views: 302

Answers (2)

Wes H
Wes H

Reputation: 4439

If your fact is showing the hiring relationship between DimPerson & DimClient, you shouldn't have any client information in DimPerson. The DimPerson record would not need to change because the relationship is contained in the Fact (and it should have the date/time of the event).

However, if you consider the employer to be an attribute of the employee, you do not need the Fact table because the relationship is contained in the Dim. In that situation, you should denormalize your Client information into DimPerson. The datetime would be used as the effective date of the dimension record and the expiration date of the previously active dimension record.

On a separate note, best practices dictates that your fact table describe the event. I find the name Fact_PersonEmployer confusing. What is the event it captures? Is it the employment event?

Upvotes: 0

Ravi
Ravi

Reputation: 667

Couple of Points:

  1. Store the From and to Dates in the Dimension Table itself, there is no need to store it in Factless Fact table.

  2. When a person moves to a different Business, only the dimension table needs to be changed i.e. closing the record and inserting a new one.

  3. When a person moves to another Company, there are few things that you need to be clear of

    1. Do you want to track that person after he has left the organizaton?
    2. Should the record be closed completely?

If you want to keep the history and record of the person, then retaining the ID would be appropriate.

Upvotes: 0

Related Questions