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 🙂
