Project Online: Updating OData connections in Excel for different PWA instances

Interesting question came in from one of our good partners, Campana and Schott, concerning the update of the connection string for Excel reports based on OData when moving the Excel books between different Project Online tenants.  This works just fine when just using the normal Excel features such as PivotTables – but you run into issues when you use add-ins such as the PowerPivot add-in for Excel 2013 – ( See http://office.microsoft.com/en-us/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx for details of this interesting add-in ).

I’ll walk through the steps with a few screenshots just so it makes it clear what I’m talking about.

First I open Excel 2013, then on the DATA tab I select the From Other Sources option on the Get External Data section – and choose the OData option.  In the dialog I enter a link to my OData feed of “https://blogfodder.sharepoint.com/sites/pwa/_api/ProjectData/Projects

Showing the Url used for the OData feed - https://blogfodder.sharepoint.com/sites/pwa/_api/ProjectData/Projects

Then choose my Projects table (the only one showing as my Url was direct to my Projects)

Showing the Select tables option and the Projects table selected

And click Finish on the following dialog:

Showing the file name and friendly name of the odc file

At this point I could just create a connection but I’ll choose a PivotTable report, as I don’t have much data so it won’t take long.

Showing that I have slected a PivotTable report

Selecting Project Name and Project Work gives me my simple list of names and total of work – and show how imaginative I am with my project naming on my test server.

At this point I could save the Excel workblook to the document library of my site – but I’ll just save locally as this step doesn’t really matter.

I now wanted to use this against a different Project Online PWA instance – so I can just open the Connections option on the DATA tab,  select the datafeed and click Properties.

I have selected the DataFeed by name and can click the properties option to the right

Under the Definition tab of the Connection Properties I can then change the Url, both in the Source section, and in the Base Url section and all will be good!  I might also want to change the name – as it picks up the name from the original Url – but it doesn’t matter if you leave it the same (it will just confuse you and your users – but not Excel!)

Higlighting the position of the Url that can be changed in the Connection string

I get a message when I click OK telling me that the connection in this workbook will no longer be identical to the connection defined in the external file located in my …DocumentsMy Data SourcesDataFeed_1_blogfodder-sharepoint-com.odc file – and it will be removing the link to the file.  I’m ok with that – so click Yes, and then close the Workbook connections dialog (when it has stopped thinking…)

Telling me that the connection in this workbook will no longer be identical to the connection defined in the external file

The page refreshes and I see the list of Projects from the new instance I am referencing – though the Project names are just as unimaginative!

Showing my name and work columns and the PivotTable fields pane to the right

So no problem to change the data connection in this simple case.  However, lets introduce PowerPivot into the mix.

I’ll follow the same steps – but this time I have activated the PowerPivot add-in for Excel 2013 – through the COM add-ins option in FILE, Options, Add-ins and Manage – selecting COM add-ins from the drop down:

Showing that I have Microsoft Office PowerPivot for Excel 2013 selected

I then have a POWERPIVOT tab and its own ribbon commands. 

Showing the POWERPIVOT tab and ribbon

First I’ll follow the steps above to create the data connection, which I can then use for PowerPivot.  I’ll not create the PivotTable this time.  Once I have this data connection I can click the Manage option which opens the PowerPivot window, and I can see it has pulled in my data

Showing the grid populated with columns from my Projects list

 

I am going to make a simple edit – just to simulate some customization of my data – and change the heading EnterpriseProjectTypeDescription to EPTDesc (Right-click column heading – Rename Column) then save my workbook.

Showing the grid populated with columns from my Projects list with my column renamed

If I now want that PowerPivot to point somewhere else I go back to the sheet and the original connection and it has a message for me – Some properties cannot be changed because this connection was modified using PowerPivot add-in – the connection string area is greyed out.

Highlighting the message - Some properties cannot be changed because this connection was modified using PowerPivot add-in:

And if I try and change in PowerPivot itself then all ways to change either directly in the feed Url or in the Advanced section just gives the same error – Cannot connect to the specified feed. Verify the connection and try again.  Reason: The remote server returned an error: (500) Internal Server Error.

Highlighting the error - Cannot connect to the specified feed. Verify the connection and try again.  Reason: The remote server returned an error: (500) Internal Server Error

The PowerPivot folks have confirmed this is a limitation when using PowerPivot and are putting together some documentation.

There are possible alternatives – Trutz Stephani at Campana and Schott used a combination of tables and additional calculated columns to make it easier to have a re-deployable workbook.  I took a look at Power Query too – part of the latest set of Power BI tools (Find out more at http://blogs.msdn.com/b/powerbi/) – but the connection string is not in any form where you’d know what to change to move between instances – so no help there.

Thanks to Trutz from Campana and Schott, and to Jules from our support team for suggesting this might make a useful blog posting.