Many to Many Relationship Query

Question

Hi All,

I am building a ssas multidimensional cube and I want to get some idea about disadvantages of using many-to-many relationships.

Say there is a dimension Employee (EmpNo, Name) and a Fact Table.

If i browse a Fact table with EmpNo. I get the following.

Name	Salary
Emp 1	100
Emp 2	120
Emp 3	140
Emp 4	160

Now if i have a new table called EmpSets(SetName, EmpNo)

SetName	EmpNo
Set 1	Emp 1
Set 1	Emp 2
Set 2	Emp 2
Set 2	Emp 3
Set 2	Emp 4

After adding all the relationships required for many-to-many relationships I can browse the cube like following.

SetName	EmpNo	Salary
Set 1		220
	Emp 1	100
	Emp 2	120
Set 2		420
	Emp 2	120
	Emp 3	140
	Emp 4	160

Now my question is.

1. Would the value 120 be computed/aggregated twice. Am I adding extra overhead?

2. The only purpose for me is to have the SetName in the Filter so that I can browse EmpNo using Sets. Hence can i disable the subtotals. (Like 220 and 420). I know that we can disable the GrandTotal/All.

Many Thanks.

---

Please specify your SQL Server version while posting questions
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue