cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
myal Visitor
Visitor

Problems on creating a new dataset with Power BI's REST API

Hello everyone!

 

I have set up Power BI pro subscription and a SQL database on Azure. My goal is to create a dataset with DirectQuery from this SQL Database through the Power BI's REST API and embed reports on a webapp. Report embedding with static data works fine, but I'm stuck on that dataset creation.

 

When I'm calling

POST https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets

with a body (for instance)

{
        'name': 'dataset test',
        'defaultMode': 'AsOnPrem',
        'tables': [],
        'datasources': [
            {
                'name': 'datasource test',
                'connectionString': 'data source={myserver}.database.windows.net;initial catalog={mydb};persist security info=True;'
            }
        ]
}

I get this mysterious error as a response:

{"error":{"code":"FailedToParseConnectionStringError","pbi.error":{"code":"FailedToParseConnectionStringError","parameters":{},"details":[]}}}

I have tried multiple different combinations for the connectionString, changing parameters, changing letter cases, adding parameters and everything, without luck. I'm also unable to find any information about that error. What am I doing wrong?

 

 

 

 

One thing that might interfere with this is that I haven't set up any gateway for the Power BI. If that's necessary on this case (probably is at some point at least), what's a good way to do that? I'm dependent on web apps, because of certain limitations I can't use desktop version of Power BI or Power BI Gateway. 

 

Is that kind of scenario of creating datasets or somehow connecting a correct database to certain report, that I described in the beginning, even posibble through the API? 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Power BI Team eligr
Power BI Team

Re: Problems on creating a new dataset with Power BI's REST API

Hi.

The API you're trying to use creates a datasets that points to Analysis Services.

Your datasource is SQL (Azure).

We don't have support for creating such datasets from the API.

You'll have to move your data to Analysis Services, use the "Push data" API or create the model in PowerBI desktop.

 

link to the API doc:

https://msdn.microsoft.com/en-us/library/mt203562.aspx

 

Eli.

View solution in original post

13 REPLIES 13
Power BI Team eligr
Power BI Team

Re: Problems on creating a new dataset with Power BI's REST API

Hi.

The API you're trying to use creates a datasets that points to Analysis Services.

Your datasource is SQL (Azure).

We don't have support for creating such datasets from the API.

You'll have to move your data to Analysis Services, use the "Push data" API or create the model in PowerBI desktop.

 

link to the API doc:

https://msdn.microsoft.com/en-us/library/mt203562.aspx

 

Eli.

View solution in original post

Vrushali Frequent Visitor
Frequent Visitor

Re: Problems on creating a new dataset with Power BI's REST API

Hi I am trying to create dataset using postgres

 

datasources":[
{
"name":"postgres",
"connectionString":"Data Source=postgresql://localhost/Postgres?user=other&password=secret;Initial Catalog=Postgres;Cube=Model"

}

]

 

And m getting 202 response in postman but in PowerBi desktop getting issue

"This Data source can not be accessed by data gateway server:"postgresql://localhost/Postgres?user=other&password=secret;"

nishhtg Frequent Visitor
Frequent Visitor

Re: Problems on creating a new dataset with Power BI's REST API

Same issue with me as well,

 

even i tried various combinations for the postgres connection string but only 202 is returned instead of 201.

{

   "name":"postgres",

   "defaultMode":"AsOnPrem",

   "tables":

   [

                                {"name": "Postgres","tables":  

                                                [

                                                                {"name": "Product", "columns":  

                                                                                [

                                                                                                { "name": "ProductID", "dataType": "Int64"}, 

                                                                                                { "name": "Name", "dataType": "string"}, 

                                                                                                { "name": "Category", "dataType": "string"}, 

                                                                                                { "name": "IsCompete", "dataType": "bool"}, 

                                                                                                { "name": "ManufacturedOn", "dataType": "DateTime"} 

                                                                                ] 

                                                                } 

                                                ] 

                                } 

   ],

   "datasources":[

      {

         "name":"postgres",

         "connectionString":"Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=root;"

      }

   ]

}

 

 

datasources":[

      {

         "name":"postgres",

         "connectionString":"Data Source=postgresql://localhost/Postgres?user=other&password=secret;Initial Catalog=Postgres;Cube=Model"

                                OR

                                Data Source=jdbcSmiley Tongueostgresql://localhost/Postgres?user=fred&password=secret&ssl=true

                               

      }

   ]

 

 

 "datasources":[
      {
         "name":"postgres",
         "connectionString":"Provider=ODBC;Data Source=postgresql://localhost/postgres?user=postgres&password=12345&ssl=prefer;Initial Catalog = postgres; Cube=Model;"
      }
   ]

 

 

Also no explanation is given any where regarding CUBE, INITIAL CATALOG and Provider Keys regarding postgres connection String.

 

Please let me know if anyone has come acrossed a solution for this.

nishhtg Frequent Visitor
Frequent Visitor

Re: Problems on creating a new dataset with Power BI's REST API

same error for me as well, getting 202 instead of 201 for dataset creation.

 

this is something wrong with the conenction string for postgres.

as it very difficult to understand and findout exactly what needs to be the structure for a postgres datasource.

 

i have tried various patterns for the connection string with no luck.

 

 "datasources":[
      {
         "name":"postgres",
         "connectionString":"Provider=ODBC;Data Source=postgresql://localhost/postgres?user=postgres&password=12345&ssl=prefer;Initial Catalog = postgres; Cube=Model;"
      }
   ]

 

   "datasources":[

      {

         "name":"postgres",

         "connectionString":"Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=root;"

      }

   ]

}

 

 

datasources":[

      {

         "name":"postgres",

         "connectionString":"Data Source=postgresql://localhost/Postgres?user=other&password=secret;Initial Catalog=Postgres;Cube=Model"

                                OR

                                Data Source=jdbcSmiley Tongueostgresql://localhost/Postgres?user=fred&password=secret&ssl=true

                               

      }

   ]

 

Also, the terms CUBE and INITIAL CATALOG, PROVIDER these keys have very less info available on the net for postgres connection string. A detailed explanation for postgres connection string would be helpful here.

 

lcestola Visitor
Visitor

Re: Problems on creating a new dataset with Power BI's REST API

Hi,

 

please @eligr can you clarify if having a gateway it's useful in order to create a datasource via REST API that connects to SQL Azure or Spark on HDInsight in direct query mode?

 

If not, is there any article from docs, blogs or other Microsoft sources that explain that?

 

Thanks in advance.

Luca

 

gregorybor New Member
New Member

Re: Problems on creating a new dataset with Power BI's REST API

HI,

 

One should define Tables when Posting Push,PushStreaming or Streaming datasets.

 

But not when defining AsOnPrem datasets.

 

Greg.

Power BI Team eligr
Power BI Team

Re: Problems on creating a new dataset with Power BI's REST API

Hi Luca.

This question doesn't look related to this thread.

 

Anyway, to connect to SQL Azure or Spark on HDInsight in direct query mode, use Power BI Desktop.

 

After you uploaded the pbix to Power BI, you can use the APIs to change/set the credentials using:

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getgatewaydatasourcesingroup and https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource.

 

Eli.

alfdagos Frequent Visitor
Frequent Visitor

Re: Problems on creating a new dataset with Power BI's REST API

Hello @eligr!

 

If I want to create the direct query dataset for HDInsight with the RestAPI is not really possible?

The RestAPI getDatasource returns this result for a datasource created with PowerBI Desktop:

 

{
    "@ odata.context": "http://api.powerbi.com/v1.0/myorg/$metadata#gatewayDatasources",
    "value": [
{
"id": "8339457f-c99b-4670-b288-d739596863ed",
"gatewayId": "620b029e-4a25-4c9f-8ef5-b94712b62dbd",
"datasourceType": "Extension",
"connectionDetails": "{\"extensionDataSourceKind\":\"Spark\",\"extensionDataSourcePath\":\"Azure/testpbi.azurehdinsight.net\"}",
"credentialType": "Basic",
"credentialDetails": {
"useEndUserOAuth2Credentials": false
}

    ]
}

 

 

Thanks for the support and best regards,

    Alfredo.

Power BI Team eligr
Power BI Team

Re: Problems on creating a new dataset with Power BI's REST API

Hi.

Creating a DQ datasets is not supported through the REST APIs, HDInsight or other.

To create such dataset, you should use Power BI Desktop.

 

Eli.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 118 members 1,233 guests
Please welcome our newest community members: