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