ADAL Python to Refresh PowerBI dataset

Question

I found a piece of code on Azure documentation that allows getting credentials without MFA. But I'm wondering if is possible to use it to connect to PowerBI API.

The piece of code that I'm using is:

import adal
import requests
from msrestazure.azure_active_directory import AADTokenCredentials

def authenticate_client_key():

    authority_host_uri = 'https://login.microsoftonline.com'
    tenant = 'tenant'
    authority_uri = authority_host_uri + '/' + tenant
    resource_uri = 'https://management.core.windows.net/'
    client_id = 'clientid'
    client_secret = 'client-secret'

    context = adal.AuthenticationContext(authority_uri, api_version=None)
    mgmt_token = context.acquire_token_with_client_credentials(resource_uri, client_id, client_secret)
    credentials = AADTokenCredentials(mgmt_token, client_id)

    return credentials

source: https://azure.microsoft.com/en-us/resources/samples/data-lake-analytics-python-auth-options/

According to the code written on PowerShell, the aim is to insert the access_token into the header of the following POST request

POST https://api.powerbi.com/v1.0/myorg/groups/me/datasets/{dataset_id}/refreshes

Source:https://powerbi.microsoft.com/en-us/blog/announcing-data-refresh-apis-in-the-power-bi-service/

I have tried to use the credentials into the POST request, but seems is not working.

I have tried

url = 'https://api.powerbi.com/v1.0/myorg/groups/me/datasets/datasetid/refreshes'
requests.post(url,data=mgmt_token)

Is it possible to merge this two codes?

Regards,

Answer 1

You can use the pypowerbi package to refresh Power BI datasets or you can check how to do it yourself by inspecting the code. https://github.com/cmberryau/pypowerbi

pip install pypowerbi

import adal
from pypowerbi.client import PowerBIClient

# you might need to change these, but i doubt it
authority_url = 'https://login.windows.net/common'
resource_url = 'https://analysis.windows.net/powerbi/api'
api_url = 'https://api.powerbi.com'

# change these to your credentials
client_id = '00000000-0000-0000-0000-000000000000'
username = '[email protected]'
password = 'averygoodpassword'

# first you need to authenticate using adal
context = adal.AuthenticationContext(authority=authority_url,
                                     validate_authority=True,
                                     api_version=None)

# get your authentication token
token = context.acquire_token_with_username_password(resource=resource_url,
                                                     client_id=client_id,
                                                     username=username,
                                                     password=password)

# create your powerbi api client
client = PowerBIClient(api_url, token)

# Refresh the desired dataset (dataset and group IDs can be taken from the browser URL)
client.datasets.refresh_dataset(dataset_id='data-set-id-goes-here',
                                notify_option='MailOnCompletion',
                                group_id='group-id-goes-here')

Answer 2

Your code for acquiring an access token looks ok, but to use it with Power BI REST API, you must change resource_uri to be https://analysis.windows.net/powerbi/api.

When making a request to Power BI REST API, you must add Authorization header with value Bearer {accessToken}, where {accessToken} is the token acquired. I can't write in python, but you should do something like this:

headers = {'Authorization': 'Bearer ' + accessToken, 'Content-Type': 'application/json'}
url = 'https://api.powerbi.com/v1.0/myorg/groups/me/datasets/datasetid/refreshes'
requests.post(url, headers=headers)

(of course, you need to replace datasetid with actual value in url).

For example, here is how it can be done in C#:

string redirectUri = "https://login.live.com/oauth20_desktop.srf";
string resourceUri = "https://analysis.windows.net/powerbi/api";
string authorityUri = "https://login.windows.net/common/oauth2/authorize";
string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
string powerBIApiUrl = $"https://api.powerbi.com/v1.0/myorg/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes";

AuthenticationContext authContext = new AuthenticationContext(authorityUri, new TokenCache());
var authenticationResult = await authContext.AcquireTokenAsync(resourceUri, clientId, new Uri(redirectUri), new PlatformParameters(PromptBehavior.Auto));
var accessToken = authenticationResult.AccessToken;

var request = WebRequest.Create(powerBIApiUrl) as HttpWebRequest;
request.KeepAlive = true;
request.Method = "POST";
request.ContentLength = 0;
request.Headers.Add("Authorization", String.Format("Bearer {0}", accessToken));
using (Stream writer = request.GetRequestStream())
{
    var response = (HttpWebResponse)request.GetResponse();
}