Many to Many Relationship Query RRS feed

  • 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

    Wednesday, August 19, 2015 4:06 PM


  • Hi Liquidloop,

    According to your description, you have a cube with many to many relationship defined. Now you want to know how the aggregation works. Right?

    In Analysis Services, if you define many to many relationships in your cube, when aggregating the fact data, then grand total will not double-count the duplicate members' value. The 120 will not be aggregated twice.

    For hiding the subtotal, you can go to Subtotals under Design tab in Excel, then choose "Do Not Show Subtotals". Please see: MS EXCEL 2007: REMOVE SUBTOTALS ON PIVOT TABLE ROWS

    Grand Total with many-to-many


    Simon Hou
    TechNet Community Support

    Thursday, August 20, 2015 8:53 AM