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

Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?
there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

Many to Many Look alike Dimensions

Hi all,

I have this design scenario that I need to validate if this is the best approach and that there are no other alternatives that I have not looked at.

OLTP 3 Tables:

Loans (PK-->LoanID, LoanSequence)

Borrower (PK-->BorrowerID, columns LoanID, LoanSequence are also there)

BorrowerAddress (PK--> BorrowerAddressID, columns BorrowerID, LoanID, LoanSequence are also there)

1. The OLTP system does not do update, every update is inserted as a new record. So we practically have type 2 change on OLTP system side. This is why Primary Key in Loan table is a com

2. One Loan record can have many borrowers (Primary, Co-borrower, Other Borrrower)

3. One Borrower can change his/her address many time during the course of the application process. So this implies many BorrowerAddresses for Each Borrower.

OLAP Tables:

table FactLoanBorrower and FactBorrowerAddress are the middle table for the many-to-many relationship.

FactLoans (TimeKey,FactLoanBorrowerKey)

FactLoanBorrower (FactLoanBorrowerKey, LoanID, LoanSequence, BorrowerID, BorrowerTypeKey)

FactBorrowerAddress (FactLoanAddressKey, BorrowerAddressID, BorrowerID, LoanID, LoanSequence)

DimBorrowerType (BorrowerTypeKey)

DimBorrower (BorrowerKey with Natural Keys (BorrowerID, LoanID, LoanSequence))

DimBorrowerAddress (BorrowerAddressKey, BorrowerID, LoanID, LoanSequence)

Basically the relationships look like this:

FactLoans<--FactLoanBorrower-->DimBorrower<--FactBorrowerAddress-->DimBorrowerAddress.

Is this design valid?

thank ahead.

Yes, this may work, however please measure performance on the predicted data volumes you're likelt to have and for a representative query workload.

Thank you

Manually defining aggregations

The Aggregation Wizard designed the following aggregation, on 2 dimensions.
I understand it is aggregating at the Country level in the StoreGeography dimension. However, in the Time dimension, since it didn't list any attributes, I am wondering where it is aggregating.

Is it at the All level, or is it at the leaf level ?

Thanks for any help.

<Aggregation>
<ID>Aggregation 7</ID>
<Name>Aggregation 7</Name>
<Dimensions>
<Dimension>
<CubeDimensionID>Store Geography</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Country</AttributeID>
</Attribute>
</Attributes>
</Dimension>
<Dimension>
<CubeDimensionID>Time</CubeDimensionID>
</Dimension>
</Dimensions>
</Aggregation>

That's right: In case and if no attribute specified for a dimension in the definition of the aggregation, the aggregation contains data for dimension on level "All" for the dimension.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.