FM - modeling people facts

[patakya]

hi,

I'm modeling a talent mgmt process.
So, our fact table is 1 row per job requisition.
It can be applied to by candidates (dimension).

However, candidates have some attributes that they may have MULTIPLES of, like Education, or prior experience. Thus, in 3NF world, I have this:

CANDIDATE (1) ----(N) Candidate Education Rows
and
CANDIDATE (1) ----(N) Candidate Experience Rows

Due to the multiple rows of experience, for example, it's not really a "snowflake". It seems to really be a "people fact".

>>>How have any of you handled this situation? In this way or another? 
I wouldn't prefer to add fields to Candidate like "School Name 1, school name 2, school start year 1, school end year 1", etc, as that is not scalable and ugly.

But, I also realize that this situation can confuse the Cognos query engine. The stitch queries don't always come out how I want them...

[blom0344]

Firstly, this has nothing to do with snowflaking as you have one dimension and 2 facts.
The facts themselves are 'factless facts' since they store relationships rather than measures.
Denormalizing (flattening) the data is probably not an option as there may be a limitless number of relationships for each education / experience.

I think it is possible to build a  union object on this set and then use a subtype to indicate for education/experience.

That would be ideal for using a crosstab in your report. If you need help with this setup, than you can also send me a message.