Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Data Refresh by using API: Need Steps

Hi,

 

I read in this article about data refresh thru API

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

 

but I do not know how to proceed further. I do not know anything about APIs, REST APIs etc.

 

Can you please help me out here to proceed further. What are the steps that I need to follow to execute below URL. 

 

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

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@Anonymous wrote:

Hi,

 

I read in this article about data refresh thru API

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

 

but I do not know how to proceed further. I do not know anything about APIs, REST APIs etc.

 

Can you please help me out here to proceed further. What are the steps that I need to follow to execute below URL. 

 

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


@Anonymous

The APIs are development stuff so I hope you would have some coding skill. See a demo in C#. Do note that the REST API also has the schedule refresh limitation(8 times per day, if you'd like to lift this limitation, you may have to buy a premium license(48 times per day), see this link).

 

using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Text;
//Install-Package Newtonsoft.Json 
using Newtonsoft.Json;
using System.IO;
using System.Web;
using System.Collections.Generic;
using System.Net.Http;
using System.Collections.Specialized;

namespace ConsoleApplication39
{

    class Program
    {

        //Step 1 - Replace {client id} with your client app ID. 
        //To learn how to get a client app ID, see Register a client app (https://msdn.microsoft.com/en-US/library/dn877542.aspx#clientID)
        private static string clientID = "49df1bc7-XXXXX9XXX0d1a4";

        //RedirectUri you used when you registered your app.
        //For a client app, a redirect uri gives AAD more details on the specific application that it will authenticate.
        private static string redirectUri = "https://login.live.com/oauth20_desktop.srf";

        //Resource Uri for Power BI API
        private static string resourceUri = "https://analysis.windows.net/powerbi/api";

        //OAuth2 authority Uri
        private static string authority = "https://login.windows.net/common/oauth2/authorize";

        //the account used to login Power BI
        private static string username = "MYACOUNT";
        private static string password = "MYPASSWD";

        private static AuthenticationContext authContext = null;
        private static string token = String.Empty;

        //The power bi app workspace id(the GUID after /groups/ in below link
        //when viewing a dataset in Power BI Service, the link is like
        //https://msit.powerbi.com/groups/dc581184-XXXXf16b6c15/datasets/1f6285a5-7XXX7b7ae5637d6
        private static string groupId = "dc581184-XXXXX-5432f16b6c15";

        //The target datasetId that is to refresh(the GUID after datesets/ in above link
        private static string datasetId = "1f6285a5XXXXXae5637d6";

        static void Main(string[] args)
        {

            //token = getAccessTokenWithLoginPopUp();
            token = getAccessTokenSilently();
            refreshDataset(groupId, datasetId);

            //wait 5 seconds for the last refresh
            System.Threading.Thread.Sleep(5000);

            getRefreshHistory(groupId, datasetId);

            Console.ReadKey();

        }



        static void getRefreshHistory(string groupId, string datasetId, int lastNRrefresh = 10)
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/refreshes/?$top={2}", groupId, datasetId,lastNRrefresh));
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "GET";
            request.ContentLength = 0;

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Write JSON byte[] into a Stream
            using (var response = (HttpWebResponse)request.GetResponse())
            {
                 
                var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
                foreach (var refresh in responseJson.value) {
                    Console.WriteLine("Dataset {0} refreshed is {1}",datasetId,refresh["status"]);
                    Console.WriteLine("starttime at {0} endtime at {1}", refresh["startTime"], refresh["endTime"]);
                    Console.WriteLine("");
                } 
            } 
        }


        static void refreshDataset(string groupId, string datasetId)
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/refreshes", groupId, datasetId));
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {

                var response = (HttpWebResponse)request.GetResponse();
                Console.WriteLine("Dataset refresh request {0}", response.StatusCode.ToString());
            }


        }

        static string getAccessTokenSilently()
        {

            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";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            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"];
            }


        }


        static string getAccessTokenWithLoginPopUp()
        {
            if (token == String.Empty)
            {
                //Get Azure access token
                // Create an instance of TokenCache to cache the access token
                TokenCache TC = new TokenCache();
                // Create an instance of AuthenticationContext to acquire an Azure access token
                authContext = new AuthenticationContext(authority, TC);
                // Call AcquireToken to get an Azure token from Azure Active Directory token issuance endpoint
                token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri), PromptBehavior.RefreshSession).AccessToken;
            }
            else
            {
                // Get the token in the cache
                token = authContext.AcquireTokenSilent(resourceUri, clientID).AccessToken;
            }

            return token;
        }
    }
}

 

View solution in original post

8 REPLIES 8
Eric_Zhang
Employee
Employee


@Anonymous wrote:

Hi,

 

I read in this article about data refresh thru API

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

 

but I do not know how to proceed further. I do not know anything about APIs, REST APIs etc.

 

Can you please help me out here to proceed further. What are the steps that I need to follow to execute below URL. 

 

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


@Anonymous

The APIs are development stuff so I hope you would have some coding skill. See a demo in C#. Do note that the REST API also has the schedule refresh limitation(8 times per day, if you'd like to lift this limitation, you may have to buy a premium license(48 times per day), see this link).

 

using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Text;
//Install-Package Newtonsoft.Json 
using Newtonsoft.Json;
using System.IO;
using System.Web;
using System.Collections.Generic;
using System.Net.Http;
using System.Collections.Specialized;

namespace ConsoleApplication39
{

    class Program
    {

        //Step 1 - Replace {client id} with your client app ID. 
        //To learn how to get a client app ID, see Register a client app (https://msdn.microsoft.com/en-US/library/dn877542.aspx#clientID)
        private static string clientID = "49df1bc7-XXXXX9XXX0d1a4";

        //RedirectUri you used when you registered your app.
        //For a client app, a redirect uri gives AAD more details on the specific application that it will authenticate.
        private static string redirectUri = "https://login.live.com/oauth20_desktop.srf";

        //Resource Uri for Power BI API
        private static string resourceUri = "https://analysis.windows.net/powerbi/api";

        //OAuth2 authority Uri
        private static string authority = "https://login.windows.net/common/oauth2/authorize";

        //the account used to login Power BI
        private static string username = "MYACOUNT";
        private static string password = "MYPASSWD";

        private static AuthenticationContext authContext = null;
        private static string token = String.Empty;

        //The power bi app workspace id(the GUID after /groups/ in below link
        //when viewing a dataset in Power BI Service, the link is like
        //https://msit.powerbi.com/groups/dc581184-XXXXf16b6c15/datasets/1f6285a5-7XXX7b7ae5637d6
        private static string groupId = "dc581184-XXXXX-5432f16b6c15";

        //The target datasetId that is to refresh(the GUID after datesets/ in above link
        private static string datasetId = "1f6285a5XXXXXae5637d6";

        static void Main(string[] args)
        {

            //token = getAccessTokenWithLoginPopUp();
            token = getAccessTokenSilently();
            refreshDataset(groupId, datasetId);

            //wait 5 seconds for the last refresh
            System.Threading.Thread.Sleep(5000);

            getRefreshHistory(groupId, datasetId);

            Console.ReadKey();

        }



        static void getRefreshHistory(string groupId, string datasetId, int lastNRrefresh = 10)
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/refreshes/?$top={2}", groupId, datasetId,lastNRrefresh));
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "GET";
            request.ContentLength = 0;

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Write JSON byte[] into a Stream
            using (var response = (HttpWebResponse)request.GetResponse())
            {
                 
                var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
                foreach (var refresh in responseJson.value) {
                    Console.WriteLine("Dataset {0} refreshed is {1}",datasetId,refresh["status"]);
                    Console.WriteLine("starttime at {0} endtime at {1}", refresh["startTime"], refresh["endTime"]);
                    Console.WriteLine("");
                } 
            } 
        }


        static void refreshDataset(string groupId, string datasetId)
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/refreshes", groupId, datasetId));
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {

                var response = (HttpWebResponse)request.GetResponse();
                Console.WriteLine("Dataset refresh request {0}", response.StatusCode.ToString());
            }


        }

        static string getAccessTokenSilently()
        {

            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";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            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"];
            }


        }


        static string getAccessTokenWithLoginPopUp()
        {
            if (token == String.Empty)
            {
                //Get Azure access token
                // Create an instance of TokenCache to cache the access token
                TokenCache TC = new TokenCache();
                // Create an instance of AuthenticationContext to acquire an Azure access token
                authContext = new AuthenticationContext(authority, TC);
                // Call AcquireToken to get an Azure token from Azure Active Directory token issuance endpoint
                token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri), PromptBehavior.RefreshSession).AccessToken;
            }
            else
            {
                // Get the token in the cache
                token = authContext.AcquireTokenSilent(resourceUri, clientID).AccessToken;
            }

            return token;
        }
    }
}

 

i have implemented the above cited example, everything went smoothly, then i'm facing 

"Exception has been thrown by the target of an invocation. System: The remote server returned an error: (401) Unauthorized."

I'd made an azure function where my code is hosted and whenever i run that piece of code, above error occurs. 

Basically i'm refreshing the power bi dataset through Power BI REST - API 

 

Any suggestion / help would be appreciated. 

I was also facing the same issue. The issue was i have installed latest version whereas version 2.21.301221612 was to be installed.

//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612

 Thanks

Anonymous
Not applicable

If we are using the getAccessTokenSilently() function , do we really need to install the Microsoft.IdentityModel.Clients.Active Directory?

 

 

Did you figure this out? I am getting the same issue.

Thanks


wrote:

i have implemented the above cited example, everything went smoothly, then i'm facing 

"Exception has been thrown by the target of an invocation. System: The remote server returned an error: (401) Unauthorized."

I'd made an azure function where my code is hosted and whenever i run that piece of code, above error occurs. 

Basically i'm refreshing the power bi dataset through Power BI REST - API 

 

Any suggestion / help would be appreciated. 


 

@kritika_goel 

I used postman to see api call header and body but still status was same "

error 401 unauthorised. "

Then i added client_secret as parameter and was able to obtain token via postman and status code was 200 (Connection established ), didn't tried via code. 

Screenshot (208).png

 

 

 

zq
Frequent Visitor

Can this be used to refresh the reports that are imported to the Azure workspace i.e. using Power BI Embedded?

Anonymous
Not applicable

What program is this code developed in?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.