Troubleshooting OData feeds in Project Server 2013
Project Server 2013 has a problem that is resolved in the June 2015 CU that you will want to load. This is the public guidance from Microsoft in the June CU notes, but it has been observed to apply to more than just a lookup table with graphical indicators.
- When you import external data from an OData data feed to a workbook in Microsoft Excel 2013, you receive the following error message:
"The content of the data feed is not valid for an Atom feed."
This issue occurs if the data feed has a custom field that has a lookup table and a graphical indicator.
Sometimes there are legitimate OData feed problems that are data related, rather than code related that can break. For instance, Projects with custom fields that have orphaned Lookup Table references, which can be fixed by simply opening them and republishing. Troubleshooting them to isolate the specific projects can be difficult, but I'd like to share one strategy I've found that works well. It also applies to any consumer of OData feeds, not just Excel.
- (Using Chrome, since it has a record count feature built into the Find feature) Open your base OData feed to get the first 100 records.
- Hit Control-F to bring up the Find window in Chrome. Search for “ProjectName”. If there are no failures, you will get “1 of 201” as a record result.
- If you find a failure, scroll to the bottom, and find the ProjectName of the last project that loaded. This will be the last good project. Sort the Project Center view by project name with no filters in a separate window, to find the name of the next project, and that will be the problem project.
- Open and publish the project. Once the publish is complete, test again, and repeat steps 3-4 until you successfully get all 201 records from the first set.
- Now open the OData feed for the next 100 records by injecting the SKIP verb.
- Control-F again on the result set, and verify 201 results on the “ProjectName” search. If there are no failures, you will get “1 of 201” as a record result.
- If you find a failure, again scroll to the bottom, and find the ProjectName of the last project that loaded. This will be the last good project. Sort the Project Center view by project name with no filters in a separate window, to find the name of the next project, and that will be the problem project.
- Open and publish the project. Once the publish is complete, test again, and repeat steps 5-7 until you get all 201 records from the first set.
- Once that set returns clean, increment the SKIP verb value by 100 and repeat steps 6-8.
Keep repeating at step 9 until the entire project list loads correctly.
I highly recommend Chrome for this purpose because it loads the XML feed of the OData stream. IE gives you a summary that does indeed have a total record count, but does not list project names unless you View Source on the resulting page, and the Find works better in Chrome for this purpose. You can turn off the Atom View in IE by going to Internet Options, Content tab, select the setting for "Feeds and Web Sites" and turn off "Turn on feed reading view" checkbox. But this doesn't give you the nice visual row counts of found objects that Chrome has.
Pulling the MSP_PROJECTS_USERVIEW table and dropping it in Excel to get an index also helps identify the next project in the list, if you halve the X/200 record count in the find window. (one ProjectName value in the open xml brackets, another in the close brackets, so two per 100 records.)
Keep in mind, this technique of publishing won’t fix the issue with the OData feed being broken between the March and June CU’s. You still need to load the CU.
Hat tip to Dan MacDonald at Sensei Project Solutions for initially putting me on the track of querying with OData verbs to narrow the problem down.