Categories: Azure Data Factory

Planview Portfolios REST API with ADF – The OData Feed

In the introduction, I explained the setup of the use case and why we need to use the OData feed: to get the list of Project IDs, because the REST API doesn’t have an endpoint for this. Weird, right?

In this part of the series, we’ll dive into the Lookup activity, which uses the OData feed to get the list we need. First, we need to create a new Linked Service:

The URL of the OData feed takes the following format:

https://myaccount.pvcloud.com/odataservice/odataservice.svc

The tricky part is accessing the OData feed. There are two options for authentication:

  • You either have a username and password, where the user is not part of the single-sign-on (SSO). Then you can log in using databasename\username as the username and the password (so make sure you figure out what the name of the database is in your production environment).
  • Or you can use an authentication token that you generate for a user in the Planview portal. You enter this token as the username and leave the password blank. The problem with the token is that it expires after a certain time. The default is 60 days, but can be extended by Planview support.

Both options are clearly explained in the documentation; search for Power BI Desktop because that page explains how to connect to OData. We went with the first option, since we don’t like the idea of manually refreshing tokens from time to time.

Next, we need to create an OData dataset, which points to a certain path of the feed.

As mentioned in the introduction, the WorkDimension table holds a list of all projects and their IDs (named structure_code). We can retrieve those in the Lookup activity (which supports OData).

The preview lets us verify if everything works as expected:

The next part of the series explains how to get the OAuth token to authenticate ADF with the REST API service.

The other parts of this series:


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago