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

[myal]

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!

[eligr · Accepted solution]

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.

[gowra1v]

Is it possible to create datasets with Datasource type File using Power BI API? If possible what metadata information we need to feed in request body

[Anonymous]

@eligr, could you please provide an example about how to create a dataset to Analysis Services?

I got the same "FailedToParseConnectionStringError" error.

I tried many combinations of properties and different connection string formats.

This is my request:

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

{
  "name": "ApiTestAasDataset1",
  "isRefreshable": false,
  "isEffectiveIdentityRequired": true,
  "isEffectiveIdentityRolesRequired": false,
  "isOnPremGatewayRequired": false,
  "datasources": [
  	{
      "datasourceType": "AnalysisServices",
      "connectionDetails": {
        "server": "asazure://australiasoutheast.asazure.windows.net/myserver:rw",
        "database": "mydb"
      },
      "connectionString": "Provider=MSOLAP;Data Source=asazure://australiasoutheast.asazure.windows.net/myserver;Initial Catalog=mydb;User ID=myuser@mydomain.com.au;Password=mypassword;Persist Security Info=True;Impersonation Level=Impersonate"
  	}
  	],
  "defaultMode": "AsAzure"
}

[Anonymous]

I've been struggling with this as well. I also tried your request, adding 'Cube=Model' to your connectionstring gets you (or at least me with your request body) to the point where I'm stuck - receiving 401 errors...

I've create separate thread for this issue here that will hopefully see us resolve both our problems.

[Anonymous]

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

[eligr]

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]

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.

[eligr]

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.

[alfdagos]

Thanks a lot @eligr!

Suppose once I've created the dataset with PowerBI Desktop, I want to duplicate and modify it to point to other tables of the same database or same HDInsight instance (naturally with the same schema..). Can you explain me the correct steps to do with the Rest API if it's possible?

Thank you very much for the fast replay!

Best regards,

    Alfredo.

[Vrushali]

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]

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=jdbc:postgresql://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.

[nishhtg]

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=jdbc:postgresql://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.

[gregorybor]

HI,

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

But not when defining AsOnPrem datasets.

Greg.