Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
We are having a multi-tenant C# application. 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 stored in ASP.Net application to Power BI dashboard dynamically when the respective tenant logs into the application. Please see the following code snippet:
// Create a Power BI Client object. It will be used to call Power BI APIs.
using (var client = new PowerBIClient(new Uri(ApiUrl), tokenCredentials))
{
// Get a list of dashboards.
ConnectionDetails cn = new ConnectionDetails();
cn.ConnectionString = "data source=Computer1\\SQLEXPRESS;initial catalog=Db_Tent1;persist security info=True;User Id=Username;Password=Password;encrypt=True;trustservercertificate=False";
//var datsetList = client.Datasets.GetDatasources(GroupId);
var datasets = client.Datasets.GetDatasetsInGroup(GroupId);
var dashboards1 = client.Datasets.SetAllDatasetConnections(datasets.Value[0].Id, cn);
.............
This is throwing error: Operation returned an invalid status code 'NotFound'
Please need your help to solve this problem.
Thanks,
Prasanna V.
I see you're getting the dataset from a group, so when set the connection string, use SetAllDatasetConnectionsInGroup instead.
Also, to get more specific information, please add a try..catch block to capture the response details message.
try { //client.Datasets.PostRowsInGroup(groupId, "dcb49dcc-7392-4277-9258-ec26bd842b42", "RealTimeData", dataObj); ConnectionDetails cn = new ConnectionDetails(); cn.ConnectionString = "data source=Computer1\\SQLEXPRESS;initial catalog=Db_Tent1;persist security info=True;User Id=Username;Password=Password;encrypt=True;trustservercertificate=False"; //var datsetList = client.Datasets.GetDatasources(GroupId); var datasets = client.Datasets.GetDatasetsInGroup(groupId); var dashboards1 = client.Datasets.SetAllDatasetConnectionsInGroup(groupId,datasets.Value[0].Id, cn); } catch (HttpOperationException ex) { //Bad Request var content = ex.Response.Content; Console.WriteLine(content); }
Dear Eric,
Thanks for the reply. I could able to fix the bug partially.
As I have already explained, we are having multiple tenant databases on the same Azure SQL server. Based on the user login I have to change the database on runtime. For this we have created session to store database name. This database name is passes to connection string (refer the code below):
The problem I am facing now is: When I change the connection string dynamically, the dashboard is not able to pull the data from data source. Please see the code below:
// Get a list of dashboards.
ConnectionDetails cn = new ConnectionDetails();
cn.ConnectionString = "data source=Azure link\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=test;password=password;persist security info=False;encrypt=True;trustservercertificate=True";
var datasets = await client.Datasets.GetDatasetsInGroupAsync(GroupId);
try
{
var dbobject = await client.Datasets.SetAllDatasetConnectionsInGroupAsync(GroupId, datasets.Value[0].Id, cn);
}
catch (HttpOperationException ex)
{
//Bad Request
var content = ex.Response.Content;
Console.WriteLine(content);
}
var dashboards = await client.Dashboards.GetDashboardsInGroupAsync(GroupId);
// Get the first report in the group.
var dashboard = dashboards.Value.FirstOrDefault();
if (dashboard == null)
{
return View(new EmbedConfig()
{
ErrorMessage = "Group has no dashboards."
});
}
Please assume that I have designed the report using "Database 1" from power bi desktop and I have published the same. If this report/ dashboard is accessed from the code it works fine. If I change the db to say "Database 2" and pass the connection string to above method it is not working.
Please I need you advice.
Thanks,
Prasanna V.
This is the Error that I get, and the dataset is a DirectQuery dataset.
{"error":{"code":"InvalidRequest","message":"Invalid dataset. This API can only be called on a DirectQuery dataset"}}
Dear shilpikanade,
These are the steps I followed to solve the similar error:
Thanks,
Prasanna V.
Hi Prasanna -
Thanks for your reply.
I was able to publish the report and see it on the wen, (app.powerbi.com), and I was also able to access and embed the report in my web app, but I could not change the connection string of the dataset using the API and that's when I get the error. Were you able to get past that error?
Thanks, Shilpi
Dear Shilpi,
So far I have not seen any one worked on the dynamically changing the data source. I was confident on Power BI API ans MS documentation. but this is not working even for me.
What I could able solve is: if the report is designed on "Database 1" and if that database name is passed dynamically the report seems to be working. the moment I change the database to some other say "Database 2" it threw me error.
All that I am saying is, dynamically changing data source is not working for me either. I am struggling from past 2 weeks. I have raised one more ticket asking for sample if anyone has worked on similar line. Refer this post:
"Power BI API to change Database dynamically"
Thanks,
Prasanna V.
Thanks Prasanna, hopefully they'll update the APIs soon.
Thanks, Shilpi
I am getting the following exception :
Operation returned an invalid status code 'BadRequest'
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |