none
Many-To-Many Relationship cannot be identified.

    Question

  • I created a PowerPivot table from 3 tables - a company table, a industry table, and a reference table with CompanyKey and IndustryKey. 

    When I created the pivot table, I got a "Relationship may be needed" warning even though I have already created the relationships (two relationships).  The output shows same number through out (an indication that the relationship has not been set).

    These three tables were used in a SSAS project with a many-to-many relationship setting and the cube returns the data without any issue.  For the test, I created a short version (with mock up data) using the same schema and the pivot table result also has no issue.  

    Does anyone know what cause it?  I know I may not provide enough info because the issue may be a very generic one.  


    BI Analyst

    Sunday, March 24, 2013 5:08 AM

Answers

All replies

  • PowerPivot (and SSAS Tabular) do not support directly M2M relationships.
    However, you can workaround this limitation by writing a proper DAX formula.
    You can find a long description here (see Tabular section):
    http://www.sqlbi.com/articles/many2many/

    In order to write a simpler DAX formula, also read this article:
    http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

    Marco

    Sunday, March 24, 2013 3:25 PM
  • Hi Marco,

    Thank you for your reply.  I am not quite sure what you meant by "directlyM2M relationships, but I have successfully created the relationship using the following 3 mock up table.  The result looks fine.

    dimCompany
    CompanyKey Company
    1 Sony
    2 Apple

    ref_Comp_Ind
    CompanyKey IndustryKey Revenue
    1 1              10.00
    1 3              20.00
    1 4              30.00
    1 7              40.00
    2 1              50.00
    2 2              60.00
    2 3              70.00
    2 4              80.00

    dimIndustry
    IndustryKey Industry
    1 Computer hardware
    2 Computer software
    3 Consumer electronics
    4 Digital distribution
    5 Media and Entertainment
    6 Video games

    Result:

    Row Labels Revenue
    Apple $260.00
    Computer hardware $50.00
    Computer software $60.00
    Consumer electronics $70.00
    Digital distribution $80.00
    Sony $100.00
    Computer hardware $10.00
    Consumer electronics $20.00
    Digital distribution $30.00
    Video games $40.00
    Grand Total $360.00

    I just do not know why it does not work on my live data.



    BI Analyst

    Sunday, March 24, 2013 5:57 PM
  • Hi Marco,

    I just took out the Revenue measures from the pivot table, and I got the following:

    Row Labels
    Apple
    Computer hardware
    Computer software
    Consumer electronics
    Digital distribution
    Media and Entertainment
    Video games
    Sony
    Computer hardware
    Computer software
    Consumer electronics
    Digital distribution
    Media and Entertainment
    Video games
    Grand Total

    It shows that M2M relationship is not working.  The reason why the M2M relationship seemed to work was because of the Revenue is in the intermediate table.  

    I think you may be right and I will need to read the articles you referred.


    BI Analyst

    Sunday, March 24, 2013 8:50 PM
  • your Scenario is actually not a M2M Scenario - well, at least not in the classical definition

    your Revenue-table is your fact-table and holds combinations of Companies and Industries

    you do not have a direct relationship between Companies and Industries but they are only connected via your fact-table
    (e.g. a factrow with "Apple" and "Vide games" would be possible even "Apple" does not produce any "Video Games")
    if you do not use any measure/column from your fact-table the tables are not related in any way and thats why u see a crossjoin of all values

    so what you see is the expected behaviour

    what would you expect to see?


    - www.pmOne.com -

    Friday, March 29, 2013 8:10 AM
    Answerer
  • Gerhard,

    I realized that the scenario is not a "true" M2M relationship.  I made that scenario up and failed to demonstrate my real issue (which is more complex).

    You are correct on that "if you do not use any measure/column from your fact-table the tables are not related in any way and thats why u see a crossjoin of all values".

    So the bottom line is that PowerPivot cannot perform a "direct" M2M relationship query.  

    What I am looking for is this - by taking out the measure in the immediate table:

    dimCompany
    CompanyKey Company
    1 Sony
    2 Apple

    ref_Comp_Ind
    CompanyKey IndustryKey
    1 1              
    1 3              
    1 4              
    1 7              
    2 1              
    2 2              
    2 3              
    2 4              

    dimIndustry
    IndustryKey Industry
    1 Computer hardware
    2 Computer software
    3 Consumer electronics
    4 Digital distribution
    5 Media and Entertainment
    6 Video games

    The pivot table can show:

    Apple
    Computer hardware
    Computer software
    Consumer electronics
    Digital distribution

    Sony
    Consumer electronics
    Digital distribution
    Media and Entertainment
    Video games

    Is that doable in PowerPivot without the workaround mentioned in Marco's post (which I have not had the chance to go over it yet)?


    BI Analyst

    Saturday, March 30, 2013 10:30 PM
  • you could create calculated columns in your ref_Comp_Ind-Table that get the text-values from the other tables

    =RELATED(dimIndustry[Industry])

    and

    =RELATED(dimCompany[Company])

    if you use that 2 new columns in your Pivot-table you will get the desired result without using any measure

    hth,
    gerhard


    - www.pmOne.com -

    Tuesday, April 02, 2013 8:55 AM
    Answerer