cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

Re: Power BI API Integration with C#

@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); 
                }
Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

I am getting the following exception :

 

Operation returned an invalid status code 'BadRequest'

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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.

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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.

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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.

Highlighted
Helper I
Helper I

Re: Power BI API Integration with C#

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

 

Thanks, Shilpi

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors