Fixing Powerpivot Table Relationships for Odata

Are you a vlookup veteran?  Were you thrilled when table relationships and Powerpivot became available in Excel? Oh yes.

So this week I’m using Powerpivot to create a really cool report in Project Online, where I associate projects in the red with their high level risks.  I love this!  Perfect way to use Project Online’s capability to view across the portfolio. 

Except…..when I pull the Projects and the Risks tables and pivot them, every project has every risk there is.  Yes, my many-to-one relationship (many risks to one project) has turned into a many-to-many relationship.  Not good.

After much searching and testing I found this solution.  If you find yourself in the same situation, give it a try.

1. Install the Powerpivot add-in if you haven’t already.
2. In PWA, navigate to your Reports.  Select English.
3. Click on New and select Excel workbook.
4. Click on Open in Excel.
5. You will get a message your browser is trying to close the page. Just say no.
6. Excel will open.
7. Click on Data/From Other Sources
8. Select Odata
9. Enter the URL for your Odata (this will be <your pwa address>/_api/ProjectData/).
10. Click Next.
11. Select the tables you need and click Next
12. You will be asked to name your connection file AND give it a friendly name.  I suggest using the names of the tables you selected in each to help identify the data.
13. Select Table from the Import Data popup and click OK.
14. Click on the Powerpivot tab.
15. In the Powerpivot window, click on Manage/Design/Create Relationships.
16. Select the relationships between each of your tables.  In this screen, the first field is the “many” field, and the second is the “one”.
17. In each tab, remove the fields you don’t need (leave the key fields you require for the relationships).
18. Open the lowest-level tab. This would be the most of the many-to-one relationships.  For instance, if I have Projects, Tasks and Assignments, this would be Assignments.
19. Click on Insert Function.
20. Choose the RELATE function. This will provide a wizard to help you add the fields you need.
21. For the first function, add a column you need from another table (hint: start typing the table name, the fields will appear once you choose the table).
22. Add a closing parenthisis to the function before clicking on OK.
23. Repeat for each field you need in your report.
24. When all the fields you need are in this table, click on Home/Create Pivot Table.
25. If you choose this table within the pivot table, all your fields will be available, properly related!

Thanks to Mike of Mike’s Notebook for this great blog post:  http://mikesnotebook.wordpress.com/2014/06/03/reporting-with-project-online-attempt-2/

At MS Project Now we help clients solve reporting and data challenges on a regular basis.  If you’d like to hear more, contact us.