how to build and trigger ETL process in powerbi

[Anonymous]

Hi,

I have my powerbi connected to Azure SQL database. I want to refresh the powerbi dashboards when new data files come in. 

The entire process is:

(1) data files uploaded to Azure enviroment

(2) a pre-built ETL process been implemented on the data files in Azure data factory

(3) transformed data landed in Azure SQL database which is data source of powerbi dashboards

my questions are:
1. Is that possible to embed the ETL process in the powerbi?

2. if ETL process won't be able to be embedded in powerbi, is there a way to give the dashboard users a notice when the ETL process has been done whenever new data files uploaded?

Thanks.

[selimovd]

Hey @Anonymous ,

I don't understand your first question. What do you mean with "embed the ETL process in the powerbi"?

To your second question, yes a user can subscribe to a report when there is new data. The first time new data appears in a data set the user will receive an email. Check the article on how to do that:

Subscribe yourself to reports and dashboards - Power BI | Microsoft Docs

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi Denis, thanks for the quick reply. For the first question, what I expect is user upload the data files to Azure SQL database, since my powerbi dashboard is connected to Azure, is that possible to click a button or run something in powerbi to automatically transform the data files in Azure into the format I need and then loaded the transformed data into powerbi to refresh the dashboards?

[selimovd]

Hey @Anonymous ,

small tip for the beginning. When you reply, always tag people with their name like this: @Anonymous 

Otherwise I will not be informed that you answered and never reply for that reason 😉

If you have a process that is loading data into your SQL server, you can at the end of your process, trigger a refresh of the data set. For that you can use the REST API:

Datasets - Refresh Dataset - REST API (Power BI Power BI REST APIs) | Microsoft Docs

If it's Azure SQL you can also trigger a refresh with power automate when new data appears.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks @selimovd . I am using Azure database, could you please explain about how to do using power automate? I am newbie to PowerBI. thanks

[selimovd · Accepted solution]

@Anonymous 

You go to Power Automate and create a new flow. The trigger is "When an item is created" in your SQL table.

Then I personally would add a delay. You don't want the refresh to start every second after each single row. Maybe wait a few minutes and then refresh the data set:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the content in the following blog to set up a schedule refresh for your Azure SQL data source in order to get the latest data.

Schedule Data Refresh

Best Regards

[Anonymous]

Hi Rena, is there a way for users to receive email when data source has been refreshed? from your screenshot I can see a checkbox of receving email when refresh fail but not refresh succeed. Thanks.

[v-yiruan-msft]

Hi @Anonymous ,

As @selimovd suggested, you can subscribe others to your reports and dashboards after data refreshed. You can refer the content in the following blog to get it.

GET POWER BI EMAIL ALERTS FOR REPORTS AND DASHBOARDS DAILY, WEEKLY OR AFTER DATA REFRESH

Best Regards