Push Dataset with Datasource

[ShawnHerring]

I am trying to use the Push Dataset API specifically Dataset PostDataset. I am attempting to use the API Documentation and the Try It feature. I have been able to create a push dataset in My Workspace in Power BI Service using Try It for the Push Dataset - Post Dataset. However, when I try to incorporate a datasource (Azure SQL Database) into my JSON I get a generic error message below.

I have scoured the internet and forums and can't find an example of JSON snytax for a Push API with datasource attached. I am aware of using Azure Registation App and that is working in my envirnoment for other things. In this situation, I am trying to Try It before I incorporate it into my Azure Registration App. I am trying to get a working JSON file up and tested to use as an example file. Does ANYONE have a working JSON file with a datasource attached? 

According to the documenation attaching a datasource to a Push API is possible

Push Datasets - Datasets PostDataset - REST API (Power BI Power BI REST APIs) | Microsoft Docs

{
  "error": {
    "code": "FailedToDeserializeDatasetError",
    "pbi.error": {
      "code": "FailedToDeserializeDatasetError",
      "parameters": {},
      "details": [],
      "exceptionCulprit": 1
    }
  }
}

[ShawnHerring · Accepted solution]

Thanks for all the suggestions. However, push datasets API does not allow for the datasource change.

"With a push dataset, data is pushed into the Power BI service. When the dataset is created, the Power BI service automatically creates a new database in the service to store the data". Real-time streaming in Power BI - Power BI | Microsoft Docs

Importanly, you can not override or change the database that is created by the service. My use case does not fit using a push API. Another suggestion was using XMLA endpoint to programmatically create datasets. I have not explored that suggestion. However, now I understand how push dataset work even if the API documentation is misleading. The existing of the datasource object in the documentation would lead one to believe it can be used, but alas that is not the case currently.

[v-yangliu-msft]

Hi  @ShawnHerring ,

You can try to use power bi desktop to connect Azure Sql Server and Json files and merge them in power query.

This is the related document, you can view this content:

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

https://www.sqlshack.com/importing-data-from-json-files-and-power-bi-rest-apis-into-power-bi/

https://community.powerbi.com/t5/Developer/Rest-API-error-creating-datasets-for-AzureAS/m-p/323604

https://docs.microsoft.com/en-us/azure/azure-sql/database/json-features

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.