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:
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:
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…
In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…
I wrote a piece of SQL that had some new T-SQL syntax in it: IS…
I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…