Project Online and OData refresh performance

This post is for those of you already familiar with setting up and using OData and Excel Services in your Project Online environments.

I have received some questions about why the default OData data connections does not provide the custom fields available in the PPM Online. For this reason I decided to do a little research.

1. The default OData  connections in PPM Online uses a Select Statement (meaning the fields you get a predefined and only Project standard fields)

2. By simply opening the OData connection e.g. in Notepad, you can easily remove all the field names from the “select statement”. Removing all the fields will now enable the data connection to capture all fields available including the custom fields you have created

3. WARNING

Having removed the select statement will slow down the performance of doing a “refresh” – especially from Excel Services in PPM Online.

In order to fix this problem, you will Again have to use the “select statement” but in a way so that only the fields needed in your report(s), are loaded and refreshed.

In my test environment I reduced the loadtime of a report by 52% after configuring the “select statement” to only include specific fields.

4. Remember

Being able to do an Excel Services “refresh” in PPM Online, is only possible when the Excel report is opened in a full page view. This means you cant refresh an Excel report if it used in the Excel Services web part.