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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lakshmi_alla
Frequent Visitor

Deploy same PBIX file to Power BI online with different data source connections programmatically

Hi All,

 

Can you please let me know a way to deploy a single PBIX file to Power BI online workspace with different source connections programmatically.

 

Currently we are doing this manually by opening the PBIX file and updating the connection string and deploying it. We need to do this procedure for 15 to 20 times as we need to deploy the same report with different source connecitons string.

 

Please let me know if there is any way to do this programmatically.

 

Thanks in advance !!

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee


@lakshmi_alla wrote:

Hi All,

 

Can you please let me know a way to deploy a single PBIX file to Power BI online workspace with different source connections programmatically.

 

Currently we are doing this manually by opening the PBIX file and updating the connection string and deploying it. We need to do this procedure for 15 to 20 times as we need to deploy the same report with different source connecitons string.

 

Please let me know if there is any way to do this programmatically.

 

Thanks in advance !!


@lakshmi_alla

You can upload the pbix file by calling the REST API Create Import and updating connection string by Set All Connections(only wokrs in DirectQuery mode). See a demo to import.

 

using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.IO;
using Microsoft.Rest;

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 = "{client id}";

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

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

        //Uri for Power BI datasets
        private static string datasetsUri = "https://api.powerbi.com/v1.0/myorg";
        private static string datasetsBetaUri = "https://api.powerbi.com/beta/myorg";

        //Example dataset name and group name 
        private static string groupId = "{group id}";

        static void Main(string[] args)
        {
            //Import sample 
            string pbixPath = @"C:\test\test.pbix";
            string datasetDisplayName = "mydataset2";

            string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}", datasetsBetaUri, groupId, datasetDisplayName), pbixPath, AccessToken());
              
            Console.WriteLine(importResponse);

        }

        public static string Import(string url, string fileName,string bearerToken)
        {
            string responseStatusCode = string.Empty;

            string boundary = "---------------------------" + DateTime.Now.Ticks.ToString("x");
            byte[] boundarybytes = System.Text.Encoding.ASCII.GetBytes("\r\n--" + boundary + "\r\n");

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            request.ContentType = "multipart/form-data; boundary=" + boundary;
            request.Method = "POST";
            request.KeepAlive = true;
            request.Headers.Add("Authorization", String.Format("Bearer {0}", bearerToken));

            using (Stream rs = request.GetRequestStream())
            {
                rs.Write(boundarybytes, 0, boundarybytes.Length);

                string headerTemplate = "Content-Disposition: form-data; filename=\"{0}\"\r\nContent-Type: application / octet - stream\r\n\r\n";
                string header = string.Format(headerTemplate, fileName);
                byte[] headerbytes = System.Text.Encoding.UTF8.GetBytes(header);
                rs.Write(headerbytes, 0, headerbytes.Length);

                using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    byte[] buffer = new byte[4096];
                    int bytesRead = 0;
                    while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)
                    {
                        rs.Write(buffer, 0, bytesRead);
                    }
                }

                byte[] trailer = System.Text.Encoding.ASCII.GetBytes("\r\n--" + boundary + "--\r\n");
                rs.Write(trailer, 0, trailer.Length);
            }
            try
            {
                HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse;
                  
                    responseStatusCode = response.StatusCode.ToString();
                
            }
            catch (HttpOperationException ex)
            {
                //Bad Request
                var content = ex.Response.Content;
                Console.WriteLine(content);
            }
            return responseStatusCode;
        }



        static string AccessToken()
        {
            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

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee


@lakshmi_alla wrote:

Hi All,

 

Can you please let me know a way to deploy a single PBIX file to Power BI online workspace with different source connections programmatically.

 

Currently we are doing this manually by opening the PBIX file and updating the connection string and deploying it. We need to do this procedure for 15 to 20 times as we need to deploy the same report with different source connecitons string.

 

Please let me know if there is any way to do this programmatically.

 

Thanks in advance !!


@lakshmi_alla

You can upload the pbix file by calling the REST API Create Import and updating connection string by Set All Connections(only wokrs in DirectQuery mode). See a demo to import.

 

using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.IO;
using Microsoft.Rest;

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 = "{client id}";

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

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

        //Uri for Power BI datasets
        private static string datasetsUri = "https://api.powerbi.com/v1.0/myorg";
        private static string datasetsBetaUri = "https://api.powerbi.com/beta/myorg";

        //Example dataset name and group name 
        private static string groupId = "{group id}";

        static void Main(string[] args)
        {
            //Import sample 
            string pbixPath = @"C:\test\test.pbix";
            string datasetDisplayName = "mydataset2";

            string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}", datasetsBetaUri, groupId, datasetDisplayName), pbixPath, AccessToken());
              
            Console.WriteLine(importResponse);

        }

        public static string Import(string url, string fileName,string bearerToken)
        {
            string responseStatusCode = string.Empty;

            string boundary = "---------------------------" + DateTime.Now.Ticks.ToString("x");
            byte[] boundarybytes = System.Text.Encoding.ASCII.GetBytes("\r\n--" + boundary + "\r\n");

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            request.ContentType = "multipart/form-data; boundary=" + boundary;
            request.Method = "POST";
            request.KeepAlive = true;
            request.Headers.Add("Authorization", String.Format("Bearer {0}", bearerToken));

            using (Stream rs = request.GetRequestStream())
            {
                rs.Write(boundarybytes, 0, boundarybytes.Length);

                string headerTemplate = "Content-Disposition: form-data; filename=\"{0}\"\r\nContent-Type: application / octet - stream\r\n\r\n";
                string header = string.Format(headerTemplate, fileName);
                byte[] headerbytes = System.Text.Encoding.UTF8.GetBytes(header);
                rs.Write(headerbytes, 0, headerbytes.Length);

                using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    byte[] buffer = new byte[4096];
                    int bytesRead = 0;
                    while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)
                    {
                        rs.Write(buffer, 0, bytesRead);
                    }
                }

                byte[] trailer = System.Text.Encoding.ASCII.GetBytes("\r\n--" + boundary + "--\r\n");
                rs.Write(trailer, 0, trailer.Length);
            }
            try
            {
                HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse;
                  
                    responseStatusCode = response.StatusCode.ToString();
                
            }
            catch (HttpOperationException ex)
            {
                //Bad Request
                var content = ex.Response.Content;
                Console.WriteLine(content);
            }
            return responseStatusCode;
        }



        static string AccessToken()
        {
            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;
        } 
    }
}
Anonymous
Not applicable

@Eric_Zhang

Hi,

I am in a similar situation as I need to deploy same version of report for different countries (different database).

Is there any way out as the report is in import mode. we can set the connection in power bi service and refresh from service.

Otherwise we will up creating many versions of pbix files with different connection settings. That is a nightmare for maintenance and not recomemmended.

Please suggest.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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