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 was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
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…