Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
Please let me know if anybody worked on changing the database dynamically using Power BI API. If you have a sample code please pass it on.
The requirement is like this. We are having a multi-tenant C# application (Different Dbs on single Azure SQL server). We are storing all the database name in session.
I have downloaded the sample application by following the link for Power BI dashboard embeding.
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embed-sample-app-owns-data/
The embeded dashboard is appearing fine. I would like to pass the database name dynamically based on the user login, rest of the connection parameters remains same.
data source=prasanna\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=yeats;password=yeats;persist security info=False;encrypt=True;trustservercertificate=True;
Thanks for your help.
Prasanna V.
Solved! Go to Solution.
@send2prasan wrote:
Dear All,
Please let me know if anybody worked on changing the database dynamically using Power BI API. If you have a sample code please pass it on.
The requirement is like this. We are having a multi-tenant C# application (Different Dbs on single Azure SQL server). We are storing all the database name in session.
I have downloaded the sample application by following the link for Power BI dashboard embeding.
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embed-sample-app-owns-data/
The embeded dashboard is appearing fine. I would like to pass the database name dynamically based on the user login, rest of the connection parameters remains same.
data source=prasanna\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=yeats;password=yeats;persist security info=False;encrypt=True;trustservercertificate=True;
Thanks for your help.
Prasanna V.
You can only change the connection string of the datasource in DirectQuery mode. See below demo setting connection string or a Azure SQL Server.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; //Install-Package Microsoft.PowerBI.Api -Version 2.0.2 using Microsoft.PowerBI.Api.V2; using Microsoft.PowerBI.Api.V2.Models; //Install-Package Newtonsoft.Json using Newtonsoft.Json; using Microsoft.Rest; using System.Net; using System.IO; namespace SetCredential { class Program { static string accesstoken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USIsImtpZCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0LyIsImlhdCI6MTUwMzM5NDAxNiwibmJmIjoxNTAzMzk0MDE2LCJleHAiOjE1MDMzOTc5MTYsImFjciI6IjEiLCJhaW8iOiJBU1FBMi84RUFBQUFwMmZob2UrMzdhTHhhWVZCVXJWc3ppbEdPdDJSSTZsNHJXRmVQcjBLdWY4PSIsImFtciI6WyJwd2QiXSwiYXBwaWQiOiI4NzFjMDEwZi01ZTYxLTRmYjEtODNhYy05ODYxMGE3ZTkxMTAiLCJhcHBpZGFjciI6IjIiLCJlX2V4cCI6MjYyODAwLCJmYW1pbHlfbmFtZSI6IlpoYW5nIiwiZ2l2ZW5fbmFtZSI6IkVyaWMiLCJpcGFkZHIiOiIxNjcuMjIwLjI1NS4xMiIsIm5hbWUiOiJFcmljIFpoYW5nIiwib2lkIjoiNzYzNGI5MzYtNjk1Yy00M2IzLWJkMTYtOWUxNDJkYjZjYjZkIiwib25wcmVtX3NpZCI6IlMtMS01LTIxLTMwMTc0MzkxODUtMzI3NTMyODQxNi0xMTI3NTIxMzg1LTcwMzc0IiwicGxhdGYiOiIzIiwicHVpZCI6IjEwMDM3RkZFOTc5NzczM0MiLCJzY3AiOiJ1c2VyX2ltcGVyc29uYXRpb24iLCJzdWIiOiJpd3NWZE5iLU1YTmlITlB1MTJqWGZ4cnhmaGxSdE4zWUw5N3pRSnR4dGpBIiwidGlkIjoiMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0IiwidW5pcXVlX25hbWUiOiJlcmljemhAd2ljcmVzb2Z0LmNvbSIsInVwbiI6ImVyaWN6aEB3aWNyZXNvZnQuY29tIiwidmVyIjoiMS4wIn0.PPPYobO52n71mQhbdRkuG9WgYymhLrLvadZCGkJLgTVjbJ6kEvZs6yYCJ5E9sd0a1cl6rfJWkz6gCI3Vi2caexCUBj38NGR5BYXPK1kTm-_DOKOpkOtusd-HAJmL4Zqgv6Cooia9f3mX_-92kSQUYlsaiZ7iVYL4pUkNMheZeIvBCAs2xU0LUgTg8B3bCL1Ezgr_iBKF_jbZcz7SNrq93K12YiL2xjmoJbBSi2LYOeCP4bUTo1tb2LRnmyCTR8pK8JvhM19u22VfmFOL0SyAL-11UBjfDp9Uk6pYwRzNtCApMsW73kmuIi52AkADZ4048tZNhqoWZsFz2lqmrg0CYw"; static void Main(string[] args) { var tokenCredentials = new TokenCredentials(accesstoken, "Bearer"); string datasetid = "0894c31c-1425-4a3c-a1fc-57f8fbef0e88"; try { using (var client = new PowerBIClient(new Uri("https://api.powerbi.com/"), tokenCredentials)) { ConnectionDetails connDetail = new ConnectionDetails(); connDetail.ConnectionString = "data source = linl3.database.windows.net; initial catalog = AdventureWorksLT; persist security info = True; encrypt = True; trustservercertificate = False"; client.Datasets.SetAllDatasetConnections(datasetid, connDetail); var dataSources = client.Datasets.GetGatewayDatasources(datasetid); /** CredentialDetails credential = new CredentialDetails(); credential.CredentialType = "Basic"; //I WAS STUCK ON what is the credentials string like credential.Credentials = "{\"credentialData\":[{\"name\":\"username\",\"value\":\"LinLeng\"},{\"name\":\"password\",\"value\":\"Password01!\"}]}"; credential.Credentials= "{\"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\" }}"; UpdateDatasourceRequest udr = new UpdateDatasourceRequest(credential); var a = client.Gateways.UpdateDatasource(dataSources.Value[0].GatewayId, dataSources.Value[0].Id, udr); **/ string credential = " {\"credentialType\": \"Basic\", \"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\" } }"; updateCredential(credential, dataSources.Value[0].GatewayId, dataSources.Value[0].Id); } } catch (HttpOperationException ex) { Console.WriteLine(ex.Response.Content); } Console.ReadKey(); } public static void updateCredential(string credential, string gatewayid, string datasourceid) { string responseStatusCode = string.Empty; string url = "https://api.powerbi.com/v1.0/myorg/gateways/"+gatewayid+"/datasources/"+datasourceid; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.ContentType = "application/json"; request.Method = "PATCH"; request.KeepAlive = true; request.Headers.Add("Authorization", String.Format("Bearer {0}", accesstoken)); //POST web request byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(credential); request.ContentLength = byteArray.Length; //Write JSON byte[] into a Stream try { using (Stream writer = request.GetRequestStream()) { writer.Write(byteArray, 0, byteArray.Length); var response = (HttpWebResponse)request.GetResponse(); Console.WriteLine(string.Format("credential is updated {0}", response.StatusCode.ToString())); } } 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 } } } } } } }
What is the alternative of "SetAllDatasetConnections" as it is deprecated? I want to change the DB server name, DB name, DB user name, and password.
@send2prasan wrote:
Dear All,
Please let me know if anybody worked on changing the database dynamically using Power BI API. If you have a sample code please pass it on.
The requirement is like this. We are having a multi-tenant C# application (Different Dbs on single Azure SQL server). We are storing all the database name in session.
I have downloaded the sample application by following the link for Power BI dashboard embeding.
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embed-sample-app-owns-data/
The embeded dashboard is appearing fine. I would like to pass the database name dynamically based on the user login, rest of the connection parameters remains same.
data source=prasanna\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=yeats;password=yeats;persist security info=False;encrypt=True;trustservercertificate=True;
Thanks for your help.
Prasanna V.
You can only change the connection string of the datasource in DirectQuery mode. See below demo setting connection string or a Azure SQL Server.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; //Install-Package Microsoft.PowerBI.Api -Version 2.0.2 using Microsoft.PowerBI.Api.V2; using Microsoft.PowerBI.Api.V2.Models; //Install-Package Newtonsoft.Json using Newtonsoft.Json; using Microsoft.Rest; using System.Net; using System.IO; namespace SetCredential { class Program { static string accesstoken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USIsImtpZCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0LyIsImlhdCI6MTUwMzM5NDAxNiwibmJmIjoxNTAzMzk0MDE2LCJleHAiOjE1MDMzOTc5MTYsImFjciI6IjEiLCJhaW8iOiJBU1FBMi84RUFBQUFwMmZob2UrMzdhTHhhWVZCVXJWc3ppbEdPdDJSSTZsNHJXRmVQcjBLdWY4PSIsImFtciI6WyJwd2QiXSwiYXBwaWQiOiI4NzFjMDEwZi01ZTYxLTRmYjEtODNhYy05ODYxMGE3ZTkxMTAiLCJhcHBpZGFjciI6IjIiLCJlX2V4cCI6MjYyODAwLCJmYW1pbHlfbmFtZSI6IlpoYW5nIiwiZ2l2ZW5fbmFtZSI6IkVyaWMiLCJpcGFkZHIiOiIxNjcuMjIwLjI1NS4xMiIsIm5hbWUiOiJFcmljIFpoYW5nIiwib2lkIjoiNzYzNGI5MzYtNjk1Yy00M2IzLWJkMTYtOWUxNDJkYjZjYjZkIiwib25wcmVtX3NpZCI6IlMtMS01LTIxLTMwMTc0MzkxODUtMzI3NTMyODQxNi0xMTI3NTIxMzg1LTcwMzc0IiwicGxhdGYiOiIzIiwicHVpZCI6IjEwMDM3RkZFOTc5NzczM0MiLCJzY3AiOiJ1c2VyX2ltcGVyc29uYXRpb24iLCJzdWIiOiJpd3NWZE5iLU1YTmlITlB1MTJqWGZ4cnhmaGxSdE4zWUw5N3pRSnR4dGpBIiwidGlkIjoiMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0IiwidW5pcXVlX25hbWUiOiJlcmljemhAd2ljcmVzb2Z0LmNvbSIsInVwbiI6ImVyaWN6aEB3aWNyZXNvZnQuY29tIiwidmVyIjoiMS4wIn0.PPPYobO52n71mQhbdRkuG9WgYymhLrLvadZCGkJLgTVjbJ6kEvZs6yYCJ5E9sd0a1cl6rfJWkz6gCI3Vi2caexCUBj38NGR5BYXPK1kTm-_DOKOpkOtusd-HAJmL4Zqgv6Cooia9f3mX_-92kSQUYlsaiZ7iVYL4pUkNMheZeIvBCAs2xU0LUgTg8B3bCL1Ezgr_iBKF_jbZcz7SNrq93K12YiL2xjmoJbBSi2LYOeCP4bUTo1tb2LRnmyCTR8pK8JvhM19u22VfmFOL0SyAL-11UBjfDp9Uk6pYwRzNtCApMsW73kmuIi52AkADZ4048tZNhqoWZsFz2lqmrg0CYw"; static void Main(string[] args) { var tokenCredentials = new TokenCredentials(accesstoken, "Bearer"); string datasetid = "0894c31c-1425-4a3c-a1fc-57f8fbef0e88"; try { using (var client = new PowerBIClient(new Uri("https://api.powerbi.com/"), tokenCredentials)) { ConnectionDetails connDetail = new ConnectionDetails(); connDetail.ConnectionString = "data source = linl3.database.windows.net; initial catalog = AdventureWorksLT; persist security info = True; encrypt = True; trustservercertificate = False"; client.Datasets.SetAllDatasetConnections(datasetid, connDetail); var dataSources = client.Datasets.GetGatewayDatasources(datasetid); /** CredentialDetails credential = new CredentialDetails(); credential.CredentialType = "Basic"; //I WAS STUCK ON what is the credentials string like credential.Credentials = "{\"credentialData\":[{\"name\":\"username\",\"value\":\"LinLeng\"},{\"name\":\"password\",\"value\":\"Password01!\"}]}"; credential.Credentials= "{\"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\" }}"; UpdateDatasourceRequest udr = new UpdateDatasourceRequest(credential); var a = client.Gateways.UpdateDatasource(dataSources.Value[0].GatewayId, dataSources.Value[0].Id, udr); **/ string credential = " {\"credentialType\": \"Basic\", \"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\" } }"; updateCredential(credential, dataSources.Value[0].GatewayId, dataSources.Value[0].Id); } } catch (HttpOperationException ex) { Console.WriteLine(ex.Response.Content); } Console.ReadKey(); } public static void updateCredential(string credential, string gatewayid, string datasourceid) { string responseStatusCode = string.Empty; string url = "https://api.powerbi.com/v1.0/myorg/gateways/"+gatewayid+"/datasources/"+datasourceid; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.ContentType = "application/json"; request.Method = "PATCH"; request.KeepAlive = true; request.Headers.Add("Authorization", String.Format("Bearer {0}", accesstoken)); //POST web request byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(credential); request.ContentLength = byteArray.Length; //Write JSON byte[] into a Stream try { using (Stream writer = request.GetRequestStream()) { writer.Write(byteArray, 0, byteArray.Length); var response = (HttpWebResponse)request.GetResponse(); Console.WriteLine(string.Format("credential is updated {0}", response.StatusCode.ToString())); } } 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 } } } } } } }
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |