How to refresh PBI dataset with Azure Data Factory

Question

Greetings,

I have a task to refresh published datasets on Power BI online using a pipeline on Azure Data Factory.

In short, the pipeline in question fetches data from MongoDb and pass the data with POST web request as described in the documentation here:
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset
The ADF pipeline works fine, in the sense that all processes terminate successfully:

However, there is an issue on PBI side, where the datasets are unable to update:

The datasets in question were all built on Power Bi desktop with the data being imported from mongoDb with the ODBC connector as well as some excel files as data
sources. I am not able to edit the datasource credentials on pbi online unless I have a local gateway running, then I can access it. However those credentials point to the ODBC connector and other files that are present on other coworker machines, thus I cant modify nothing.
Finally, I have a PRO PBI license/workspace and the resource groups from azure are already added as admins of the workspace. Also, the workspace works fine with the PBI Embedded feature (we use the Master User authentication method).

Given this context, how can I successfully refresh these datasets?
Is it possible to do it if they were imported from PBI desktop and built with the ODBC connector?

Possible workarounds? If I could create an empty dataset on pbi online and post the updated data with ADF it also would be fine, as long as I could keep updating the dataset with ADF.

Best Regards,

Answer 1

Hi Alan Pitthan,

Thank you for posting query in Microsoft Q&A Platform.

Here are some possible solutions to this issue:

1. Use a local gateway: If you are not able to edit the datasource credentials on Power BI online, you can try using a local gateway to refresh the datasets. A local gateway allows you to securely connect to on-premises data sources, such as the ODBC connector and Excel files, from Power BI online. You can install the local gateway on a machine that has access to the data sources and configure the datasource credentials in the gateway. Once the gateway is configured, you can use it to refresh the datasets from Power BI online.
2. Use a different data source: If you are not able to use the ODBC connector and Excel files as data sources, you can try using a different data source that is supported by Power BI online. For example, you can try using a cloud-based data source, such as Azure SQL Database or Azure Cosmos DB, or a different on-premises data source that is supported by Power BI online.
3. Use a different authentication method: If you are not able to use the Master User authentication method, you can try using a different authentication method that is supported by Power BI online. For example, you can try using the OAuth2 authentication method, which allows you to authenticate using a user account or a service principal.
4. Use an empty dataset: If you are not able to refresh the existing datasets, you can try creating an empty dataset on Power BI online and then using ADF to update the dataset with the new data. You can create the empty dataset using the Power BI REST API and then use the same API to update the dataset with the new data.

I hope these solutions help you resolve the issue. If you continue to face issues, you may need to contact Microsoft support for further assistance.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.