Many-to-many Relationship Issue/Question

Hello All,

Here's what I got so far:

- Employee fact table with 10 dimensions connected to it

- A Factless Bonus table and the Employee fact table are connected through a common dimension of Time.

- The Factless Bonus table connects to a Bonus Dimension

- added a many-to-many relationship with the Bonus Dimension to the Employee fact table

How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?

Thank you.

-Gumbatman

"How can I slice and dice by both the other 10 dimensions and the Bonus Dimension?" - but aren't you already able to slice and dice the measures from the Employee fact table this way? If not, could you explain what the problem is in more detail? It seems strange that Bonus should only be a function of time, and not of any other dimension; but maybe that's in the nature of your problem.|||

Deepak,

I apologize for not expressing this as well as I could. I just used the Time dimension as an example. Sorry about that...

Here's the situation:

I have a fact table that has one row per employee, per month. I now need to add bonuses that were received by the employees. The issue is that employees can received more than one bonus per month and sometimes those bonuses can be the same type.

So the question is: How can I have multiple bonuses for an employee in a single month when they only have one row in that month?

When I tried connecting the Fact tables, I didn't know what made the most logical sense to have as that intermediate dimension. When I tried using Time, I could get that they got a bonus and when they got it, but I could not do any additional slicing across other dimensions (the 10 I mentioned) that the Employee Fact table is connected to.

My results would be that Employee John Doe got five bonuses this month, but it would not show that he was in the Southeast. It would place those bonuses only in the Midatlantic area.

Thank you so much for the help. This has been driving me crazy.

-Gumbatman

p.s. Another thing I need to do is show which employees when to which school. Again, employees may have attended more than one school.

|||So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension. But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)? In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension. The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).|||

Deepak,

I couldn't get the quoting to work, but here are my answers:

So there are apparently 2 fact tables/measure groups here: EmployeeMonthly and BonusMonthly, with the latter having an associated "BonusType" dimension.

- Yes

But are the other 10 dimensions all directly related to EmployeeMonthly, or are some referenced via a separate "Employee" dimension (ie. each employee has a single associated member)?

- The other 10 dimensions all directly related to EmployeeMonthly,

In either case, these dimensions would have referenced relationships to the BonusMonthly measure group, either via the Employee or EmployeeMonthly fact dimension.

- I haven't tried that yet, but I will. I think that is one of the points I am missing.

The only obvious many-to-many relation is between the BonusType dimension and the EmployeeMonthly measure group, via the BonusMonthly measure group (I'm assuming that EmployeeMonthly has a composite key like {Employee, Month}, which is a foreign key in BonusMonthly).

- That makes perfect sense, but I am still confused on the exact relationship between EmployeeMonthly and BonusType. How do they connect to each other? Is that with a composite key or with BonusMonthly? Or is BonusMontly a table with the composite key?

When you are referring to a composite key, do you mean selecting the EmployeeKey, BonusKey, Date, etc. and make that a key? Or concatenating those keys into a single field for the row, which is now a Primary Key?

The way I picture this is:

EmployeeMonthly Table

Employee Monthly Key Employee Key Date Key Bonus Composite Key 1 1 20061001 0 2 2 20061001 2_20061001 3 1 20061101 0 4 2 20061101 0

BonusMonthly Table

Employee Key DateKey Bonus Key Bonus Composite Key 2 20061001 1 2_20061001 2 20061001 1 2_20061001 2 20061001 2 2_20061001

Here Employee #2 got three bonuses on 10/1/2006. Two of the bonuses were the same type and that is what confuses me about the composite keys, don't I need a Primary key in BonusMontly?

Also, I may be thinking in terms of a relational database, but when I connect EmployeeMonthly and BonusMonthly (in the setup above) I would expect three rows of duplicated data. Is that correct?

Thank you so much for your help with this.

-Gumbatman

|||Based on the sample data, EmployeeMonthlyKey could be added as a Foreign Key to BonusMonthly, allowing the EmployeeMonthly fact dimension to directly relate to BonusMonthly measure group. Then BonusType could have a many-to-many relation to EmployeeMonthly measure group, with BonusMonthly being the intermediate measure group. You might not need a primary key in BonusMonthly, unless you're also setting it up as a fact dimension (eg: for drillthrough).|||

Deepak,

I got this to work, almost...

The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table.

For example,

- EmployeeMonthy is related to BonusType dimension through BonusMonthly measure group.

- EmployeeMonthy also has dimensions directly related to it, such as a Geographic dimension, JobLevel dimension, Product dimension, etc.

When I want to see Bonus data by Geographic Area, I am getting strange results. The numbers are correct, but the bonuses are not properly breaking down by Geographic Area. It appears that it is only taking the first member of the Geographic dimension.

I tried setting them (BonusType and Geographic) as many-to-many, but that didn't work.

How is it supposed to work? Is there a way around this? I am thinking of just duplicating all the dimensions on the EmployeeMonthly onto the BonusMonthly fact table.

I really appreciate the time and energy you've put into helping me with this.

-Gumbatman

|||"The problem I am still having is with the Dimensions that are related only to the EmployeeMonthly fact table" - as I mentioned earlier, you could configure these dimensions with referenced (not many-to-many) relationships to the BonusMonthly measure group, via the EmployeeMonthly fact dimension (which you should have already configured as directly related to the BonusMonthly measure group). These referenced relationships could be materialized, for better performance. Or you could, as you suggest, directly relate these dimensions to the BonusMonthly measure group, but this might involve adding a join to the fact table named query.|||

Deepak,

Thank you so much, I finally got it to work!

I didn't really catch it when you spoke about referenced dimensions. Plus, my lack of understanding made me gloss over it.

I really appreciate all the time and effort you put into helping me with this. I would never of have gotten to the solution without your expertise.

-Gumbatman