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
send2prasan
Helper I
Helper I

Power BI API Integration with C#

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.

8 REPLIES 8
Eric_Zhang
Employee
Employee

@send2prasan

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.

Anonymous
Not applicable

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:

 

  1. From Power BI desktop publish your report. this requires gateway installation. Please follow the instructions from the link below: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/
  2. Your report is published once this done. see you are able get the report on web.
  3. Now use the Power BI API code this should render the report/ dashboard without any problem.
  4. Getting your report on power bi web is important in direct query.

Thanks,

Prasanna V.

Anonymous
Not applicable

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.

Anonymous
Not applicable

 Thanks Prasanna, hopefully they'll update the APIs soon.

 

Thanks, Shilpi

Anonymous
Not applicable

I am getting the following exception :

 

Operation returned an invalid status code 'BadRequest'

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.