Dealing with multiple many to many related type2 SCDs

I am actually new to dw design. I have been reading a lot from these forums and can't appreciate enough for how much I have learned from them.

I have question regarding designing a datamart that deals with employees in my company.

I have three dimensions [employee, team, teamrole] that I am trying to design dimension tables for.

An employee can be part of multiple teams and a team can have multiple employees.
[(Emp1, team1), (Emp2, (team1), (Emp1, team2), (Emp2, team2)]

Within a team, an employee can have only one team role but multiple employees can carry the same team role. But outside the team, employee and team roles are many to many related.
[In in team1--(Emp1, Manager), (Emp2, supervisor)], In team2--(Emp1, supervisor), (Emp2, supervisor)]

All the three dimensions, employee, team and team role are to be modeled as slowly changing dimensions(type2) with a start date and an end date
An employee's role within a single team can change that should be recorded with a team role start date and end date.
An employee's membership with a team can change that should be recorded with a teammembership startdate and end date.
A team can be terminated that should be recorded as a team startdate and end date.

I want to be able to analyze the facts (number of minutes worked, number of accounts processed, etc) over all the three dimensions.

But equally important is that I need to report just the attributes over these three dimensions using joins say for example, on a given day, i need to know the teams that are active and then all the members of a particular role in a particular team and questions of that sort. I guess I need these dimenions to be connected in some sort to be able to do this.

I dont know how many dimension tables I would be needing and how will they be related to the fact table and between each other

I hope I didn't confuse people with my question(first time ever posting a dw question) and hopefully somebody has an answer to my problem.

To me it feels like you can get away with just one dimension here (dimEmployee). Depending on your process that generates your facts, you may have to build a bridge table on dimEmployee. Can you please elaborate on the actual process that generates your facts?

You should have three separate dimensions.
Then have a fact table with a date dimension to give you the relationship between these three dimensions.

Your fact table resolves the many to many relationship between team, employer and role.

Wouldn't "employee-role-team" be treated as a hierarchy within a single dimension here though? You could still break-down your numbers by each dimensional attribute if you wanted to?

If the purpose is to track the state of teams, then you should construct a fact table to do just that. It is basically a membership model with the three dimensions: team, role, and employee with begin and end dates.

jchernev wrote:Wouldn't "employee-role-team" be treated as a hierarchy within a single dimension here though? You could still break-down your numbers by each dimensional attribute if you wanted to?

No, because a hierarchy is a one to many relationship, and this is clearly a many to many relationship.

@ John Simon, ngalemmo & jchernev,
Thanks for your inputs.

@John, Initially thats the direction I was heading. I wanted to join all three employee-team-teamrole dimension tables through facts. But the facts are actually based on the number of accounts an employee processed in a day. An employee can have multiple fact records in a day or he may not have any. If an employee has a fact record for a day, that record tells us the team he belongs to and the team role he is playing in that team for a particular day. What if an employee doesn't process any accounts on a given day. We will lose the relation between all three dimesnions for that day.

I need for reporting purposes to be able to answer questions like what teams an employee is part of on a given day, what role an employee played in a team on a given day and questions of that nature that are not necessarily related to facts. So, I need to join these dimesnion tables among them as well as with the fact table.

Hope I made the problem clear. I appreciate you guys help!!!

Would a coverage factless fact table help here? You would use it alongside your original fact <-> 3 dimensions model.

One of the things I originally struggled with when I first started out with dimensional modeling was the fact that it's ok to have more than 1 fact table to describe a process. In reality, it's more common to have more than 1 fact table to describe, capture all metrics & answer all questions.

@ jchernev... Yes.. I am thinking the same way... but I have another question that I came up with....

I can share all the three dimensions simulatenoeusly between the two fact tables (a foreign key constraint from each of the fact table to every dimension table), one fact table recording all the measures over the three dimensions and the other just has the three foriegn keys to three dimensions giving me the details of all employees, their teams and their roles at a given time.

Or another thing I felt I can do is make foreign key constraints from a bridge table to all the three dimesnions, get a unique id for the bridge table and use that to be referenced as a foreign key in the fact table recording measures. Even though an employee might not have a fact for a given day, the information of his team and team role can still be obtained from the bridge table.

I dont know if both of them work or atleast one works between the two (Hopefully..!). Can someone tell me what will work or If both work, which one is better than the other and if both dont, then let me know where am I going wrong.

Thank you

Both methods would work.... for different purposes and business questions. The bridge table is where I was leaning towards with your original post. However, you do mention that there are days when employees don't generate facts but you still want to know what team/role they are on. You can guarantee that with a factless fact coverage table as you'd be doing a snapshot of all your employees every day/data load at the same time. Also, it would be much easier to get an answer to the questions you have stated in your requirements.

Agree with jchernev, factless fact (coverage) table is the simplest and most effective approach in this case. Since the size is quite manageable, I would just snapshot the relationship daily. Use date key instead of start/end date will make the process simpler and also give you the snapshot on other periodical levels.

@ jchernev and hang.. thank you guys for the suggestions...