programmatic data refresh using api

---

Anonymous wrote:

I have found that with the new PBI API there is an option to trigger a dataset refresh programatically.  I was looking at implementing this into a powershell script that could be called at the end of our nightly job loads.  I found a open source powershell script that uses the API: https://github.com/Azure-Samples/powerbi-powershell

 

I filled in the script with the info needed and it works.  However when i run the script I am forced to manually sign in via AAD to generate the required token used in the API call.  The plan is to run this powershell script in a job automatically so I would need the login to be handled automatically.

 

How can I make the login automatic so that I do not need to manually log in each time i run this script?

 

Without knowing much about powershell scripting I'm assuming that the following code needs to be changed:

       $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

Rather than passing in "Auto", I would assume that there is some way to pass in user credentials instead.

 

Any help would be appreciated.

 

Thanks!

---

Anonymous

Based on my test, I can get the access token in a silent way without any popup login window. Just ensure that the account used in this demo is granted permission in that AAD application.

 static string getAccessTokenSilently()
        {
            string resourceUri = "https://analysis.windows.net/powerbi/api";
            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp("https://login.windows.net/common/oauth2/token");
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/x-www-form-urlencoded"; 

            NameValueCollection parsedQueryString = HttpUtility.ParseQueryString(String.Empty);
            parsedQueryString.Add("client_id", clientID);
            parsedQueryString.Add("grant_type", "password");
            parsedQueryString.Add("resource", resourceUri);
            parsedQueryString.Add("username", username);
            parsedQueryString.Add("password", password);
            string postdata = parsedQueryString.ToString();


            //POST web request
            byte[] dataByteArray = System.Text.Encoding.ASCII.GetBytes(postdata); ;
            request.ContentLength = dataByteArray.Length;

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {
                writer.Write(dataByteArray, 0, dataByteArray.Length);
                var response = (HttpWebResponse)request.GetResponse();
                var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
                return responseJson["access_token"];
            }


        }

Thank you for your advices, but I getting this error too:

New-Object : Cannot find an overload for “PSCredential” and the argument count: “2”

Use the following scripts which work perfect,

# Parameters - fill these in before running the script!

# =====================================================

# An easy way to get group and dataset ID is to go to dataset settings and click on the dataset

# that you'd like to refresh. Once you do, the URL in the address bar will show the group ID and

# dataset ID, in the format:

# app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID}

$groupID = "me" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace

$datasetID = "d9b4fd0c-7ac9-4e5d-be42-76686ce6b2db" # the ID of the dataset that hosts the dataset

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# To get the sample to work, ensure that you have the following fields:

# App Type: Native app

# Redirect URL: urn:ietf:wg:oauth:2.0:oob

# Level of access: all dataset APIs

$clientId = "Client ID"

# End Parameters =======================================

# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD

# update the dll files path if the versions are different like 5.1.2

function GetAuthToken

{

$adal = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Automation\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

$adalforms = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Networking\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"

[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null

[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null

$redirectUri = "urn:ietf:wg:oauth:2.0:oob"

$resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

$authority = "https://login.microsoftonline.com/common/oauth2/authorize";

$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

$UserCred = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList “[email protected]“, “myPassword”

$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $clientId, $UserCred).Result

return $authResult

}

# Get the auth token from AAD

$token = GetAuthToken

# Building Rest API header with authorization token

$authHeader = @{

'Content-Type'='application/json'

'Authorization'=$token.CreateAuthorizationHeader()

}

# properly format groups path

$groupsPath = ""

if ($groupID -eq "me") {

$groupsPath = "myorg"

} else {

$groupsPath = "myorg/groups/$groupID"

}

# Refresh the dataset

$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"

Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbose

# Check the refresh history

$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"

Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET –Verbose