Unable to delete Data Source in PowerBI Service using PowerBI API

[coder_andy]

Hi,

I have uploaded a pbix file that uses Mixed Storage Mode (Direct Query and Data Import) to the PowerBI Service. The file has only one Azure SQL data source, say Dev-DataSource. 

After the upload I need to update the connection string/credentials so that I can setup scheduled refresh from another source, QA-DataSource. I am able to add the new data source using parameters, but when I try to delete the old one (DevDataSource) it does not work. I have tried deleting the old data source in the following ways:

• delete the data source from PowerBI Service > Dataset > Settings > (X), [clicking (X) does not do anything]
• using API call (https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/deletedatasource), . API returns 200 but the data source is not deleted

Is there a way to delete the old data source and replace it with a new one in PowerBI service? I am trying to avoid doing this in PBI Desktop as I need to automate this step, and need to provision the pbix for multiple clients.

[Jayendran]

Hi @coder_andy ,

The update data source/parameters itself meaning it will repalce the old data souces. so your old DEV will be replaced by QA. So why you are trying to delete the dev source , which already got replaced with QA.

[coder_andy]

@Jayendran So, it looks like Update Parameters call created a new data source instead of replacing the existing one. I am using the "datasets/{datasetId}/Default.UpdateParameters" API call to update parameters.

Below is a screenshot of what I see in Dataset Settings:

Could it be because of the composite data model? Since both Direct Query and Data Import tables point to the same data source, one of them is updated while other is not?

[coder_andy]

I confirmed from the data in the report, that while all Data Import tables are pointing to "QA-DataSource", Direct Query tables in the model are still pointing to the "Dev-DataSource", and did not get updated by the UpdateParameters call. 

How can I update the data source for Direct Query tables in a composite data model? Is there another API call for this?

[Jayendran]

Hi @coder_andy ,

From the screenshot it seems you are having 2 different data source credentials, in other words your both query like import and direct using 2 different data sources and credentials.

I'd recommed to update/create both the queries (import mode and direct query mode ) to get the values from the parameters like database and server names 

Ideally you should have a single parameter(s) (servername/database) for your both queries (import and direct query )

[coder_andy]

Hi @Jayendran ,

As I mentioned in in my first message, the pbix file has only ONE data source "Dev-DataSource" for both direct query and import queries. It is using parameters to populate the connection string for this data source. I am trying to update the data source in PowerBI Service using REST APIs after the pbix has been uploaded. 

The screenshot above is after I ran the following APIs to update parameters and credentials for "QA-DataSource":

• datasets/{datasetId}/Default.UpdateParameters POST
• gateways/{gatewayId}/datasources/{dataSourceId} PATCH

Running above APIs creates a second data source (credentials) in the service. So, now I see both QA-DataSource and Dev-DataSource both.

By running the report I found out that the Data Import queries were updated to point to QA-DataSource, but the ones using Direct Query still point to Dev-Datasource. 

Is this scenario not supported?

[Jayendran]

Hi @coder_andy ,

Ah I gotcha ! The update parameters not supported for direct queries, due to this you having some problem.

Solution:

Instead of using parameters you should use Update Data Source API