HR Recruitment data model subtype conflict

Greetings,

I am running into a modeling logjam and was wondering if anyone could help. I am modeling a BI solution that handles HR (Human Resources) , which includes recruitment and other related areas as well. As you might guess, the Person Dimension is critical. I have modeled it as a subtype-supertype that looks like this:

Supertype: PersonDim

SubTypes: ApplicantDim, CandidateDim, EmployeeDim, RecruiterDim....

This was done as a subtype-supertype because each of the subtypes have different attributes and we are also interested in having visibility on people that change from one subtype to another (ex: a person starts off as an external recruiter and then we hire him and he becomes an employee).

We have a requirement to analyze employee hires by the sources of the candidates. This is the means by which the candidates came into contact with us, and can include Online job boards, or actual "human" recruiters such as external or internal recruiters (the latter of which are, of course, employees).

So, I think that CandidateSourceDim is also a Subtype - Supertype, containing OnlineJobBoardDim, RecruiterDim, and EmployeeDim as subtypes

I can't include the CandidateSourceDim as another subtype of PersonDim because some candidate sources, such as Online Job boards, are not persons.

So this is where things get messy.....according to my thoughts the EmployeeDim and RecruiterDim need to be subtypes of the CandidateSourceDim, but they already members of another subtype cluster for Person Dimension, so how would this be modeled properly? My modeling application (ER/Studio) doesn't even let me do this, saying that an entity can only belong to 1 subtype cluster.

Any ideas?

Super/Sub types are ER modeling techniques, not dimensional modeling techniques. Conformed dimensions are a dimensional modeling technique that may apply to your problem. In the dimensional world, we don't subtype entities. If a person can be an applicant and a candidate, and we want to use all three of the entities as entry points to interrogate our fact table, then we create three dimensions. Since the dimensions are conformed, the SK value for the person dimension will equal the SK value of the applicant dimension which will equal the SK value for the candidate dimension.

CandidateSource is not a subtype. It probably is a hierarchy. In the dimensional world we flatten out hierarchies.

Hopefully this will provide some clarity to your design as well as vocabulary for how we communicate in the dimensional modeling world.

So instead of formalizing the subtype-supertype all you would do is to make sure the Surrogate Keys are aligned between the dimensions, using something like a Sequence object? I can see how this would solve the problem.....we could create a Sequence object that generates the surrogate keys for the RecruiterDim and OnlineJobBoardDim dimensions, and then create the CandidateSourceDim which gets the rows, with the surrogate keys, from these other 2 dimensions that are fed by the sequence. Is this what you meant? Any downsides to this?

Thanks!