Replicate Project Online Reporting Data to a SQL Server database using the OData protocol

This link describes how you can use SQL Server Integration Services (SSIS) to replicate your reporting data from Project Online to a separate SQL Server database, using the Open Data protocol (OData).

It explains how to install the add-on, the high level details of the replication process and how to get started in Visual Studio with the Integration Services project template to create a project that automatically pulls your data based on a schedule.

While Project Online: SSIS package for OData delta sync provides an example SSIS package that demonstrates how to perform a delta sync to reduce download times. The package retrieves Project Web App (PWA) data only for projects that changed since the last sync. This sample applies to Project Online reporting when report data is replicated to a local SQL Server database.

Unable to access ODATA via Excel 2013

On April 9th 2014 a number of updates were applied to my laptop and after that I was unable to access the ODATA feeds.  Excel returned the following error: Invalid pointer array

To resolve the issue I had to apply the following hotfix:

You experience one of the following problems if you have recently applied the October 2013 Cumulative Update for Excel (KB 2825655) or a later version, but you have not updated your version of the PowerPivot add-in:

  • When you try to access data modal from a pivot table, you receive the following error message:
    We couldn’t get data from the Data Model. Here’s the error message we got:
    Invalid pointer array
  • You cannot refresh pivot tables, and no updates seem to occur.
  • You cannot launch the PowerPivot | Manage window from the ribbon.