Problem & Purpose
Recently I had a project come up in my work, basically requiring a way to scrape metadata on Reports and other items my Team has stored in our Workspaces of the Power BI cloud service.
Note before continuing; I work primarily in an Office 365 government cloud (GCC) environment, so my documentation below will be tailored directly for other GCC customers. In my research to get this work over several days, I found most google-able samples and examples to be tailored for the public environment sector of Power BI service, with minimal to no documentation relating to GCC customers..
This project prompted my first real look into the Power BI REST API. The Power BI REST API provides service endpoints for embedding, administration, governance and user resources, letting you manage Power BI content, perform admin operations, and embed Power BI content elsewhere on the web.
To make API calls, it is recommended to setup and make authorization through a method called “Service Principal”. The basic idea is to create an authorized generic-entity who has the correct permissions to perform the calls and operations we desire without being tied to an actual personal account of a team member. This setup can be done via PowerShell or the Microsoft Azure Portal, but for this entry we will focus on the few simple steps in the Azure portal.
Setting up Service Principal
1. Create an Azure AD app and new Client Secret.
Though Microsoft documentation tends to switch the terminology, this new “App” in our situation will serve as the “Service Principal” entity for authorization. Make note of your {Application Client ID}, {Directory (tenant) ID}, and {Client Secret – Value} as you follow the linked documentation steps.
Note: Remember to copy the {Client Secret – Value} when you create it, as the full value will not be accessible once you navigate away from the screen, and will require you to create a new one to get a full value.
2. Create an Azure AD security group
Add the just created App Registration as a Member of the new Security Group:
3. Edit Power BI Tenant Settings for API authorization
For the next step, it is necessary for you to have the Fabric Administrator role assigned to your user and active.
Follow Enable the Power BI service admin settings to enable Non-Admin and/or Admin API calls for your Service Principal inside the new security Group. These settings are in the Admin Portal of the Power BI service:
4. Add the Security Group as Admin to chosen Workspace(s)
Add the service principal (via new Group) to your workspace as an Admin member of the Workspace.
O365 GCC – Power BI REST API call configurations
Now we should have everything setup we need to utilize a Power BI Service Principal and make (Non-Admin/Admin) Power BI REST API calls using it. For testing and executing these API calls, we will need those (3) important values noted earlier:
- {Application (client) ID} – ex. 43b6…
- {Directory (tenant) ID} – ex. a8ca…
- {Client Secret – Value} – ex. Opd*******
For this exercise, I will verify using the Postman API platform directly.
1. Our first step is to create a [Post] call to our tenant utilizing the Service Principal and get an authorized “Bearer” token. First, we setup a [Post] call for an oauth2 authorization to our Power BI tenant:
https://login.microsoftonline.com/{Tenant ID}/oauth2/v2.0/token/
2. Next, we form the Body of the [Post] call with a few Key/Value pairs:
- client_id – {Application (Client) ID}
- grant_type – client_credentials
- scope – https://analysis.usgovcloudapi.net/powerbi/api/.default
- client_secret – {Client Secret – Value}
3. Third, if we have entered all values correctly for the [Post] body key-value pairings, we should get a server “200” OK response and a returned “Bearer” access token value starting in “ey…”. This will allow us authorization as the Service Principal to make API [Get] calls. Be sure to copy the entire “access_token” value from beginning with “ey” to the end of the string.
More on managing access tokens for API requests can be found here.
4. Now that are Service Principal has authenticated with a valid and active token (for now), we can attempt many possible Power BI REST API calls for data. We will start with a non-Admin API call. Open a new tab in Postman and configure a [Get] API call with the following example URL: https://api.powerbigov.us/v1.0/myorg/groups
Click on the [Authorization] section for this API call, select “Type: Bearer Token”, and paste the full value into the “Token” section.
This will return all Workspaces/Groups – Microsoft documentation uses the term interchangeably – that the Service Principal via the Security Group has been given access to. In my example below, this comprises currently only a Development and Test workspace.
Note that the returned “Bearer” token is active by default up to one hour after login or renewal calls, though depending on your security concerns can be reduced to 5 minutes or extended up to 24hrs. If the token is expired when you try to make an API call, you will get a guided response:
5. If we have enabled Admin read-only API calls through our Power BI tenant Admin Portal for the same Security Group holding our Service Principal, we can also utilize Admin API operations by changing the [Get] call to a URL like the following to get all Reports the authorized Service Principal has access to: https://api.powerbigov.us/v1.0/myorg/admin/reports
Conclusion
And that is a quick demonstration of setting up and configuring a Service Principal to utilize Power BI REST API calls for the government cloud (GCC).
In my next post I will cover how we can utilize these API calls with parameters in Power Query to pull this metadata into Power BI for ETL operations and utilization.