Forum Discussion
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}/datasetswith 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!
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.
13 Replies
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.
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 [email protected];Password=mypassword;Persist Security Info=True;Impersonation Level=Impersonate" } ], "defaultMode": "AsAzure" }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.
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;"
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.
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
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.
