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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
akashjain29
New Member

Automatic Creation Workspace and Publish report from a template

We have craetd the app with dot net and want to create different app workspace for each customer and also publish the power bi reports for the user automatically .

 

As of now - we have create the workspace manually and then publish the report in that workspace - this things has to be done manually,

 

Data are stored in Azure Sql database which syncs with my application.

 

Please guide,

 

Thanks

Akash Jain

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@akashjain29 wrote:

We have craetd the app with dot net and want to create different app workspace for each customer and also publish the power bi reports for the user automatically .

 

As of now - we have create the workspace manually and then publish the report in that workspace - this things has to be done manually,

 

Data are stored in Azure Sql database which syncs with my application.

 

Please guide,

 

Thanks

Akash Jain


@akashjain29

You can develop an automation application with the Power BI REST APIs. See a demo as below.

 

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

namespace PBIGettingStarted
{

    //Sample to show how to use the Power BI API
    //  See also, http://docs.powerbi.apiary.io/reference

    //To run this sample:
    //Step 1 - Replace {Client ID from Azure AD app registration} 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)

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

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

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

        private static string pbixPath = @"C:\test\template.pbix";
        private static string datasetDisplayName = "testdataset";

        static void Main(string[] args)
        {

            AccessToken();

            createGroup("myTestGroup1");

            string groupID = getGroup("myTestGroup1"); 
            //Import sample at the first time publish
            string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}", PBIAPIUri, groupID, datasetDisplayName), pbixPath);
            //append  parameter nameConflict=Overwrite when trying to replace an existing dataset, if the dataset doesn't exists, you would get an error
            //string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}&nameConflict=Overwrite", PBIAPIUri,groupID,  datasetDisplayName), pbixPath);


            if (importResponse == "Accepted")
            {

                Console.WriteLine("pbix import accepted");
                //add you sending mail code here
            }


            Console.ReadLine();

        }

        private static string getGroup(string groupName)
        {
            string groupid = "";

            string responseStatusCode = string.Empty;
            string url = "https://api.powerbi.com/v1.0/myorg/groups";


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

            request.Method = "GET"; 
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));
             


            try
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {


                    string responseText;
                    var encoding = ASCIIEncoding.ASCII;

                    using (var reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
                    {
                        responseText = reader.ReadToEnd();
                        //Console.WriteLine(responseText);

                       var groups = JsonConvert.DeserializeObject<dynamic>(responseText.ToString());

                        foreach (var group in groups.value) {

                            if (group["name"] == groupName) {

                                groupid = group["id"];

                            }


                        }


                    }




                }

            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

            return groupid;

        }

        static void createGroup(string groupName)
        {
            string responseStatusCode = string.Empty;
            string url = "https://api.powerbi.com/v1.0/myorg/groups";
             

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
             
            request.Method = "POST";
            request.ContentType = "application/json";
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            using (Stream rs = request.GetRequestStream())
            {
                string grpName = "{\"name\":\"" + groupName + "\"}";

                byte[] headerbytes = System.Text.Encoding.UTF8.GetBytes(grpName);
                rs.Write(headerbytes, 0, headerbytes.Length); 
            }


            try
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {
                    responseStatusCode = response.StatusCode.ToString();
                    Console.WriteLine("group created", responseStatusCode);
                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

             
        }

        /// <summary>
        /// Use AuthenticationContext to get an access token
        /// </summary>
        /// <returns></returns>
        static void 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;
            }

            
        }

        public static string Import(string url, string fileName)
        {
            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}", token));

            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
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {
                    responseStatusCode = response.StatusCode.ToString();
                    Console.WriteLine("imported pbix ", responseStatusCode);
                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

            return responseStatusCode;
        }

    }
}

 

View solution in original post

1 REPLY 1
Eric_Zhang
Employee
Employee


@akashjain29 wrote:

We have craetd the app with dot net and want to create different app workspace for each customer and also publish the power bi reports for the user automatically .

 

As of now - we have create the workspace manually and then publish the report in that workspace - this things has to be done manually,

 

Data are stored in Azure Sql database which syncs with my application.

 

Please guide,

 

Thanks

Akash Jain


@akashjain29

You can develop an automation application with the Power BI REST APIs. See a demo as below.

 

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

namespace PBIGettingStarted
{

    //Sample to show how to use the Power BI API
    //  See also, http://docs.powerbi.apiary.io/reference

    //To run this sample:
    //Step 1 - Replace {Client ID from Azure AD app registration} 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)

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

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

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

        private static string pbixPath = @"C:\test\template.pbix";
        private static string datasetDisplayName = "testdataset";

        static void Main(string[] args)
        {

            AccessToken();

            createGroup("myTestGroup1");

            string groupID = getGroup("myTestGroup1"); 
            //Import sample at the first time publish
            string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}", PBIAPIUri, groupID, datasetDisplayName), pbixPath);
            //append  parameter nameConflict=Overwrite when trying to replace an existing dataset, if the dataset doesn't exists, you would get an error
            //string importResponse = Import(string.Format("{0}/groups/{1}/imports?datasetDisplayName={2}&nameConflict=Overwrite", PBIAPIUri,groupID,  datasetDisplayName), pbixPath);


            if (importResponse == "Accepted")
            {

                Console.WriteLine("pbix import accepted");
                //add you sending mail code here
            }


            Console.ReadLine();

        }

        private static string getGroup(string groupName)
        {
            string groupid = "";

            string responseStatusCode = string.Empty;
            string url = "https://api.powerbi.com/v1.0/myorg/groups";


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

            request.Method = "GET"; 
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));
             


            try
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {


                    string responseText;
                    var encoding = ASCIIEncoding.ASCII;

                    using (var reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
                    {
                        responseText = reader.ReadToEnd();
                        //Console.WriteLine(responseText);

                       var groups = JsonConvert.DeserializeObject<dynamic>(responseText.ToString());

                        foreach (var group in groups.value) {

                            if (group["name"] == groupName) {

                                groupid = group["id"];

                            }


                        }


                    }




                }

            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

            return groupid;

        }

        static void createGroup(string groupName)
        {
            string responseStatusCode = string.Empty;
            string url = "https://api.powerbi.com/v1.0/myorg/groups";
             

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
             
            request.Method = "POST";
            request.ContentType = "application/json";
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            using (Stream rs = request.GetRequestStream())
            {
                string grpName = "{\"name\":\"" + groupName + "\"}";

                byte[] headerbytes = System.Text.Encoding.UTF8.GetBytes(grpName);
                rs.Write(headerbytes, 0, headerbytes.Length); 
            }


            try
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {
                    responseStatusCode = response.StatusCode.ToString();
                    Console.WriteLine("group created", responseStatusCode);
                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

             
        }

        /// <summary>
        /// Use AuthenticationContext to get an access token
        /// </summary>
        /// <returns></returns>
        static void 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;
            }

            
        }

        public static string Import(string url, string fileName)
        {
            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}", token));

            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
            {

                using (HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse)
                {
                    responseStatusCode = response.StatusCode.ToString();
                    Console.WriteLine("imported pbix ", responseStatusCode);
                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }

            return responseStatusCode;
        }

    }
}

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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