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

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

2 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

6 days ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

7 days ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

1 week ago

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.…

3 weeks ago

dataMinds Connect 2024 – Session Materials

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

1 month ago