Extracting Data from the Planview Portfolios REST API using ADF – Introduction

I have a client using Planview Portfolios to manage their PMO. As part of the data warehouse, we want to extract the project data from the Planview REST API. There are other methods to get data, such as using OData (which is used in the Power BI connector), SOAP Web Services (2005 called) and a query engine. A bit more info on the last option: Planview runs on a SQL Server database (not sure what kind and what version) and it’s possible for people with the correct permissions to write queries and extract the results.

In our case, we decided to try the REST API. It is fairly well documented (you need access to the customer success portal though), and there’s even a decent swagger page that you can use to test the endpoints. It takes the following format:

https://myaccount.pvcloud.com/myserver/swagger/index.html

But there is one minor problem: you can only extract project or work data for a specific ID. For example, these are the available API endpoints for project:

The first one is a metadata endpoint that returns all the possible project attributes. The third one (POST) is to create a new project, the last one (PATCH) is to update an existing project. That leaves us with the second endpoint, which has a required parameter {id}. But there’s no way to extract a list of existing project IDs. My reaction:

For the work items, we get a similar story:

The first one is again a metadata endpoint, the last one is similar to the project endpoint with the required {id} parameter. The second one is a bit more interesting:

It returns all work for a certain project, and we need to specify a certain URL filter. There’s only one example, and that’s filtering on a certain project ID (which we could not retrieve, remember?). There’s no documentation whatsoever on what possible fields are to filter on and there’s zero documentation on the possible operators. Trying something smart like “project.Id .neq 0” to hopefully get all work for all projects that don’t have Id = 0 doesn’t work.

So how do we get the data out of Planview in an automated fashion? Well, we combine the OData feed and the REST API. Luckily, there’s a table in the OData feed – named WorkDimension – that contains all the projects and their IDs. That’s a good start.

To extract the data, I’m using Azure Data Factory, but you can use Synapse Pipelines, Fabric Pipelines or whatever tool that can connect with an OData Feed or a REST API. The final pipeline looks like this:

The Lookup component reads the WorkDimension path from the OData feed and passes the project IDs to the ForEach loop. The Web activity fetches the OAuth token so we can authenticate with the REST API service. For each project, the relevant work is fetched and saved as JSON in a database using the Copy Activity. Each step of this solution is described in a separate blog post.

The other parts of this series:

• Planview Portfolios REST API with ADF – The OData Feed
• Planview Portfolios REST API with ADF – Fetching the Access Token
• Planview Portfolios REST API with ADF – Retrieving Actual Data