none
Creating multiple PowerPivot relationships

    Question

  • Say you have 4 tables: 1 Fact and 3 related tables.

    Fact Table:
    - Plan Group ID
    - Date ID
    - Others

    Plan Group table:
    - Plan Group ID
    - Date ID
    - Others

    Plan Bridge table:
    - Plan GroupID
    - Date ID
    - Plan ID
    - Others

    Plan table:
    - Plan ID
    - Plan Name
    - Others

    Many (Plan Group ID + Date ID) in Fact table link to one (Plan Group ID + Date ID) in Plan Group table.

    One (Plan Group ID + Date ID) in Plan Group table link to one (Plan Group ID + Date ID) in Bridge table.

    Many (Plan Group ID + Date ID) in Bridge table link to one (Plan Group ID + Date ID) in Plan table.

    While I can create the relationships in PowerPivot, I was expecting that when creating a slicer from the Plan table (selecting one Plan) the facts would be filtered.

    I can selected an item from a slicer filter in the Plan Group and the fact gets filtered properly. When I select an item from the Plan, nothing happens on the Pivot. Is that level of relationship not supported in the slicer?

    Saturday, April 20, 2013 7:59 PM

Answers

  • David, thanks for posting the model. I recreated my own version from your data as I'm on 2010 at work.

    My solution isn't particularly glamorous but it seems to solve the particular problem you've highlighted. It uses a technique featured in this piece by Marco and Alberto.

    I used the 'Bridge' as a look-up table to add the Group reference to the 'Dim' table which isn't actually related to the 'Fact' or 'Group' table. The DAX takes care of the counting of the rows in the fact based on the selection of plan and the presence of the measure brings in the group dimensions expected.

    The DAX (where [Fact Count] is a basic measure that counts the rows in Fact) :

    =CALCULATE( 
               [Fact Count],
                     FILTER( ALL(fact[GroupID] ), 
                     CONTAINS( VALUES( 'Dim'[Group] ), 'Dim'[Group], Fact[GroupID])
                            )
               )

    The relationships look like this:

     

    I posted the sample model on SkyDrive. Note I simplified the thing by making the relationships on GroupID and I created a second group to help test but hopefully this gets you on the way.

    Let me know how you get on.

    Jacob

    Monday, April 22, 2013 4:03 AM

All replies

  • David,

    I think this concept should work! Chain relationships must all be in the same direction to work i.e. Many to One - Many to One.

    On that basis I suspect that if you swap your middle relationship around you should see the desired result.

    Let me know if that helps.

    Jacob

    Saturday, April 20, 2013 11:54 PM
  • Hi Jacob,

    I was wondering if PowerPivot supported more than just one level down of relationships.  I can't really swap the relationship since PowerPivot determines the direction based on the one-to-many relationship, no?  Maybe I didn't understand your suggestion.

    Sunday, April 21, 2013 2:27 AM
  • Provided all the relationships are in the same direction then more than one level down is definitely supported.

    Although knowing the outline structure of your model helps, without some sample data its impossible to know for sure but I took your statement:

          'One (Plan Group ID + Date ID) in Plan Group table link to one (Plan Group ID + Date ID) in Bridge table'

    To mean you had unique set of values in each, obviously if that's not the case then the relationship will only work one way and a change to the structure may be necessary. 

    It's probably going to take a representative snippet of data for each table to work out exactly what's going on.

    Jacob


    Sunday, April 21, 2013 4:07 AM
  • Interesting comment about the relationships being in the same direction ...

    The Fact to Group table is many (in Fact) to one (in Group) relationship.

    The Group to Bridge is one (in Group) to many (in Bridge) relationship.

    Wouldn't this make the Fact to Bridge many to many?  Maybe that's the problem.

    When I select a value from the Bridge table in the slicer, all values are still shown in the pivot table.

    I tried uploading a screenshot but my account has not been "validated" so I got an error ... not sure how/when they do that.

    But something is really messed up.

    If I put a column from the bridge in the pivot I can see values that are NOT available in a slicer I created using the same column.

    Sunday, April 21, 2013 4:46 AM
  • You are right in that Fact to Bridge is a Many to Many when the middle relationship is that way round which just isn't supported natively. There are a number techniques you could use, a good read is the Many to Many Revolution by Marco Russo and Alberto Ferrari.

    If you were to create a fake sample model and post it to GoogleDrive/DropBox/SkyDrive I'm sure someone would be happy to have a look - these things are near impossible to solve without some data.

    Sunday, April 21, 2013 6:06 AM
  • Thanks for the reading material.  I'm getting a good cup of coffee!

    Here's the link to some sample files: h t t p s:// skydrive .com live. com /redir?resid = ADB88CD6E2906F89!122&authkey=!AF1CDPfCKemLUvs

    Sorry about the spaces in the link ... can't post a link until my identity is verified ... :-(

    Sunday, April 21, 2013 1:57 PM
  • David, thanks for posting the model. I recreated my own version from your data as I'm on 2010 at work.

    My solution isn't particularly glamorous but it seems to solve the particular problem you've highlighted. It uses a technique featured in this piece by Marco and Alberto.

    I used the 'Bridge' as a look-up table to add the Group reference to the 'Dim' table which isn't actually related to the 'Fact' or 'Group' table. The DAX takes care of the counting of the rows in the fact based on the selection of plan and the presence of the measure brings in the group dimensions expected.

    The DAX (where [Fact Count] is a basic measure that counts the rows in Fact) :

    =CALCULATE( 
               [Fact Count],
                     FILTER( ALL(fact[GroupID] ), 
                     CONTAINS( VALUES( 'Dim'[Group] ), 'Dim'[Group], Fact[GroupID])
                            )
               )

    The relationships look like this:

     

    I posted the sample model on SkyDrive. Note I simplified the thing by making the relationships on GroupID and I created a second group to help test but hopefully this gets you on the way.

    Let me know how you get on.

    Jacob

    Monday, April 22, 2013 4:03 AM