topic Re: Power-BI REST API use Azure SQL Server as datasource in Developer

Power-BI REST API use Azure SQL Server as datasource

mforte — Thu, 04 Jul 2024 15:36:10 GMT

Hello everyone,

I use Power BI Embedded to provide services to customers. Currently, we use a SQL Server on our VPS.

Each customer has a database, and each customer has an associated PBIX file that was associated to the correct DB throw the on-premises data gateway software.

Currently, the process to associate a PBIX file to the database is as follows:

Upload PBIX file via API: https://api.powerbi.com/v1.0/myorg/groups/{targetWKS}/imports?datasetDisplayName={newPBIXName}&nameConflict=CreateOrOverwrite.replace("{targetWKS}", targetWKS).replace("{newPBIXName}", newPBIXName)
Update datasource via API: https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/Default.UpdateDatasources.replace("{datasetId}", datasetId);
Create datasource in my gateway via API: https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources.replace("{gatewayId}", gatewayId);
Bind datasource to gateway via API: https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/Default.BindToGateway.replace("{datasetId}", datasetPBId);

After completing this procedure, the pbix dataset was refreshed correctly .

We are considering switching to an Azure Sql Server Managed Instance.

So, I am testing the integration without using the On-Premises data gateway software.

Through the web interface (Power BI services), it is possible to create a cloud connection (see the screenshot "CloudConnection.PNG").

How can I create the same type of connection via API?

I have checked the outgoing web requests when creating the CloudConnection, and the interface calls this API: https://api.powerbi.com/v2.0/myorg/me/gatewayClusterCloudDatasource, but I cannot find it among the available APIs on https://learn.microsoft.com/en-us/rest/api/power-bi/ .

Thank you all for your support.

Re: Power-BI REST API use Azure SQL Server as datasource

Anonymous — Fri, 05 Jul 2024 01:33:27 GMT

Hi @mforte,

If you do not clear how to create similar connection based on REST API, you can build a template version on power bi side at first.
Then you can use REST API to get this connection string and use this template as refence to modify to the fact versions.

Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Datasets - Get Gateway Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Regards,

Xiaoxin Sheng

Re: Power-BI REST API use Azure SQL Server as datasource

mforte — Fri, 05 Jul 2024 08:27:57 GMT

Hi @Anonymous

Thanks for your reply.

I'm looking for something that need no changes on pbix files.

I just want to connect the report on powerbi service, dinamically whit new Azure SQL server whitout using the On-Premis Data gateway