[Tutorial] Power BI Data Refresh

 Power BI Data Refresh

Reference

In this tutorial, we will see what is data refresh in Power BI and how to schedule refresh in Power BI. And also we will discuss

• What is schedule refresh in Power Bi
• How to do schedule refresh in Power Bi
• Power bi auto-refresh SharePoint list
• How to automatically refresh power bi report when it gets data from the SharePoint list( using Power automate)
• Power BI Scheduled refresh greyed out
• Power bi scheduled refresh 15 minutes
• Power bi scheduled refresh turns off
• Power Bi schedule refresh limit
• Different types of automatic page refresh in power bi
• Different types of Power bi refresh

What is schedule refresh in Power Bi?

• When we set up a scheduled refresh in Power Bi, it gets connected directly to the data source.
• To get connected to the data source, use the connection information and credential in the dataset to query for updated data. Then it loads the updated data into the dataset.

How to set schedule refresh in the Power Bi

Here we will see how to set schedule refresh in the power bi.

For that, we have created a power bi report using an excel sheet. Then, we will publish the report online in the power bi service.

• Open Power Bi desktop, Now you can see the report page on the power bi desktop.

report in power bi desktop

• Now we will publish the report on the web. To publish the report, click on the publish icon on the ribbon on the power bi desktop.

Publish the report online

• In the Publish to power bi dialog box, under select a destination, click on the My workspace. Click on the Select

my workspace in power bi

• Then in the Publishing to Power Bi dialog box, you can see that your power bi report(financial report) successfully published on the power bi web. Then click on Got it.

power bi report publish to web

Now we will schedule a refresh in the power bi service, when we make a change in the report on the power bi desktop, it gets updated on the web.

• In the office 365 admin center, Click on the app launcher, then select power bi.
• Click on the My Workspace from the navigation in power bi service.

My workspace in power bi service

• Then go to Dataset+Dataflows on the My workspace page in the power bi service.
• Next to the name of the dataset, there is a refresh now icon, by clicking on the refresh icon, you can refresh the report immediately.

refresh now power bi

• Next to the refresh now icon, click on the schedule refresh in the My workspace page in power bi service. Here we are using the financial report to set the schedule refresh.

power bi scheduled refresh

• Under dataset in the Setting for financial report page in Power bi.

Gateway connection:

• In the gateway connection, you can see a different option, whether you have personal, enterprise, gateway online, and available.
• If there is no gateway is available, then you see the gateway connection is disabled.
• You need to install the gateway, and also you can see the message indicating how to install the personal gateway connection.

personal gateway power bi

Data source credential

• Here we are using the personal gateway, to refresh data, you must supply the credential to connect to the end data source by clicking on the Edit credential link.
• The credential you entered to connect is carried over for scheduled refresh.

data source credentials power bi service

Scheduled Refresh

• The scheduled refresh section in the power bi service is where you set the frequency and time slot to refresh the data set.
• Some of the data sources in the power bi do not require a gateway to be configurable for data refresh and other resources need a gateway for data refresh.
• Turn on the keep your data up to date slider to configure the setting.
• Under refresh frequency, you have two options i.e daily and weekly.
• You can set the time zone
• Under the Time, click on the Add another time link to add time, in what time data get a refresh
• Under Send request failure notification, you can send a failure notification to the dataset owner or you can send a failure notification to the email. Then click on Apply.

power bi auto refresh data from excel

Note:

When no user visited any dashboard or report built on the dataset within 2 months, a dataset considered as inactive, and schedule refresh on your dataset get paused. The scheduled refresh in power bi for the dataset is then displayed as disabled. To resume this scheduled refresh in power bi, simply revisit any report or dashboard build on the dataset.

Power bi auto-refresh SharePoint list

Here we will see the Power bi auto-refresh SharePoint list. If we make changes in the SharePoint list then changes reflect in the power bi report or how reports get refresh automatically.

And also we will see how on-demand refresh and Schedule refresh, refresh the datasets and visuals in power bi report.

I have a SharePoint list which I will use to create a report in power bi desktop. And then I will publish that report to the web.

• Open the Power bi Desktop
• In the Power bi report, Click on the Get data-> More->Online Service->SharePoint Online list-> Connect.

How to get data from SharePoint Online on Power BI?

• In the SharePoint Online dialog box, paste the SharePoint site URL. Then click on Ok.

SharePoint Online list Url

• A navigator page will open, select the SharePoint list. On the right side of the page, you can preview the SharePoint List.
• Click on Transform data

navigator in power bi

• Now power bi query editor will open, here remove all the unnecessary column, except the column which are from SharePoint list.
• Once removing unnecessary column is over, the click on Close and Apply from the ribbon.

Close and apply in power bi query editor

• Create a Power bi report. I have a created a report, you can see below:

Power bi report

• Click on Publish. To publish the report on the web.

publish report in power bi

• In the Publish to power bi dialog box, under select a destination, click on the My workspace. Click on the Select.

my workspace in power bi

• Then in the Publishing to Power Bi dialog box, that your power bi report(sales analysis report) successfully published on the power bi web. Click on Got it.

power bi report successfully published

• Go to power bi service, by using the URL: https://app.powerbi.com/.
• Click on the My Workspace from the navigation in power bi service

My workspace in power bi service

• Under the Dataset+Dataflows go to your power bi report datasets (Sales analysis). Then click on the Schedule refresh icon.

Schedule refresh in power bi

• Under data source credential, click on the Edit credential link, to log into your credential, if you are not logged in.
• Configure credential dialog box, under Authentication method->OAuth2, and under privacy level setting for this data source->Organizational. Click on Sign in.

data credential in power bi service

• Once you Sign in, go to the Schedule refresh section, turn on the Keep your data up to date.
• Under refresh frequency, you have two options i.e. daily and weekly.
• You can set the time zone, Under the Time, click on the Add another time link to add time, in what time data get a refresh
• Under Send request failure notification, you can send a failure notification to the dataset owner or you can send a failure notification to the email. Then click on Apply.

Now on every day 2:00 PM, the dataset of the sales analysis report get auto-refresh.

Schedule refresh in power bi service

• Once you click on apply, you can see when the report auto-refresh will happen on the top of the Refresh history link.
• If you want to see the history of the scheduled refresh, you can see it in the refresh history

auto refresh in power bi report

How to automatically refresh power bi report when it gets data from the SharePoint list ( using Power automate)

Here we will see how to automatically refresh a power bi report when it gets data from the SharePoint list by using power automate.

Now I will use the above report which we have created, published, and scheduled the refresh in the above topic.

• Open Power automate by using URL: https://flow.microsoft.com/
• Click on Create->Automated Cloud Flow.

Automated cloud flow in power apps

• Under Flow name, add a name for the flow such as Auto-refresh dataset
• Select When an item is created in SharePoint. Click on Create.

When an item is created in sharePoint using power apps

• Select your site address and the SharePoint list.
• Click on the Next step.

When an item is created in SharePoint in flow

• Select Refresh a dataset in power bi under Action.

refresh a dataset in power bi

• In workspace select the My workspace, and then select the dataset present in the Power bi. Click on Save.

how to automatically refresh power bi report

• Click on Test icon-> manually-> Test.

test flow in power automate

• Now you can add items to the SharePoint list to run the flow. When you add an item to the SharePoint list, it will automatically refresh the power bi report and dataset

how to auto refresh power bi report

Power bi schedule refresh greyed out

Here we will see how to solve the Power Bi schedule refresh greyed out.

Power BI Scheduled refresh greyed out

To solve this Power BI Scheduled refresh greyed out follow the below steps:

Step 1: Gateway connection

• First, we have to install the Gateway connection. You can see a different option, these are personal, enterprise, gateway online, and available.
• Here I have already installed the Personal Gateway connection.

scheduled refresh power bi greyed out

Step 2: Data Source Credential

• Then, we have to provide the data source credential to connect to the end data source.
• To log in with the Data source credential, click on the Edit credential link.

power bi scheduled refresh not working

• Under privacy level setting for the data source, select organizational level. Click on Sign in
• Then add your organizational credential and password, login.

Microsoft Power bi schedule refresh greyed out

Step 3: Schedule Refresh

• Now Turn on the Schedule Refresh, and you can schedule the refresh as per your requirement.
• In the below Screen-short, you can see the Power bi Schedule refresh.

Power bi schedule refresh greyed out

Power bi scheduled refresh 15 minutes

Here we will see how to set power bi schedule refresh every 15 minutes using power automate.

I have already created a report on the power bi desktop and I will use the report to show the Power Bi schedule refresh every 15 minutes.

Power bi schedule refreshes every 15 minutes

• After creating a report in power bi desktop, then publish the report to the Power bi service by clicking on Publish icon.
• Once you published the power bi service, you can see the report in the power bi service, by using the app.powerbi.com link to open the power bi service.
• Then to set the schedule refresh in 15 minutes, we will use the power to automate.
• So open your Power automate using flow.microsoft.com, and click on the Schedule cloud flow.

Schedule cloud flow in power automate

• Under Flow name, add a name. Then under Run this flow, give a start date and time and also set the repeating time i.e. 15 minutes. Then click on Create.

Power bi schedule refreshes

• Click on the next step to add the next action. Select Refresh Dataset in power bi under Action.

Microsoft power bi schedule refresh

• In Refresh a dataset, select My Workspace as the Workspace in Power bi and Select a report which you want to schedule.

Microsoft power bi schedule refresh in 15 minutes

• Click on Save to save the flow. Then click on the Test icon -> Manually -> Test button, to test the flow manually.
• After that, you can see the flow run successfully.

Microsoft power bi schedule refresh

• And also in power bi service, you can see the schedule Refresh, in Refresh history of that report.

Microsoft power bi schedule refresh in every 15 minutes

Power bi scheduled refresh turns off

Here we will see the Why power bi schedule refresh turn off.

• Power Bi schedule refresh gets disabled automatically after the four consecutive errors or refresh fails.
• Whenever the gateways go down because it is not connected to the internet, then the Power Bi schedule refresh switched off automatically.
• If a power bi dataset is considered inactive when no user has visited the dashboard or report built by the dataset. At that time, the power bi dataset owner sent an email that the power bi schedule refresh is paused.
• To resume the paused schedule refresh, revisit the dashboard or report built on the dataset.

Power Bi schedule refresh limit

Here we will see the Power Bi schedule refresh limit.

• Any member, with the following roles like Admin, member, or contributor, will see the refresh now option. But for the viewer role refresh now the option is not available.
• The data refresh limits based on the power bi capacity:
  • Datasets on shared capacity, you can schedule up to.
  • Datasets reside on premium capacity, you can schedule up to 48 daily refreshes in the dataset settings.
• If the user wants to refresh the data immediately, then they can use the refresh now an option in the dataset menu. Refresh now is not included in the refresh limitation and also it does not affect the next scheduled refresh time.

Different types of automatic page refresh in power bi

Here we will see the different types of automatic page refresh in power bi.

There are two different types of automatic page refresh are available:

• Fixed interval
• Change detection

Fixed interval

• The fixed interval refresh types allow users to update all visuals in a report page based on constant intervals like in 1 sec or five minutes.
• When a specific interval of time is reached, all visuals on that page in Power Bi send an update query to the data source, and then it gets updated accordingly.

Change detection

• The change detection refresh type allows users to refresh the visuals on a page based on detecting changes in the data rather than a specific refresh interval.
• Specifically, in the change detection refresh type, this measure polls for changes to your direct query sources.
• Besides the measure, users also have to select how frequently will check the changes in power bi desktop.
• When the user publishes to the power bi service, the change detection refresh type is only supported in the workspace, which is part of the premium capacity.
• The Live connect sources like analysis service and power bi dataset are not supported.

Different types of Power bi refresh

The Power bi refresh consist of 5 different types of refresh

• Data refresh
• OneDrive refresh
• Query caches
• Tile refresh
• Report visual

Data refresh

• The refreshing data typically means for the power bi user is importing data from the original data source into a dataset, either based on the schedule refresh or on-demand refresh.
• The underlying source data changes frequently, the user might need to refresh multiple datasets daily.
• In the on-shared capacity, the limit of datasets in power bi is 8 refreshes daily.
• The quota of 8 refreshes daily in datasets gets renew daily at 12:01 AM local time.
• In the premium capacity, the user can schedule up to 48 refreshes per day in a dataset set.
• The shared capacity limitation for daily refreshes applies to both scheduled refresh and API refresh in the power bi.
• You can select the on-demand refresh i.e Refresh now, which is not included in the refresh limitation.

OneDrive refresh

• If the user created datasets and reports based on a power bi desktop file like excel, or a comma-separated value file on OneDrive or SharePoint Online, power bi performs another type of refresh is called OneDrive refresh.
• When a dataset refresh, during which power bi imports data from the data source into a dataset, while one drive synchronizes datasets and reports with their source file.
• The power bi checks about every hour if a dataset is connected to a file on one drive or SharePoint online requires sync.
• In OneDrive power bi performs refresh based on the item ID.
• When the user sets a OneDrive file as a data source, then power bi references the item ID of the file when it performs the refresh.
• To review the past synchronization, the user can check it from the refresh history in the power bi service.
• Users can deactivate the Onedrive refresh in the datasets setting.
• If the user doesn’t want the datasets and reports in Power Bi to pick up any changes from the source file automatically, then deactivating the one-drive refresh is useful.
• When the dataset is connected to a file in OneDrive or SharePoint Online, then the dataset setting page only shows the OneDrive credential and OneDrive refresh.
• If the user deactivates the OneDrive refresh datasets, still user can synchronize the dataset on demand by selecting the Refresh Now.
• If the OneDrive refresh is enabled for OneDrive and SharePoint Online connected dataset, then make sure to configure the schedule so that power bi perform the refresh before the OneDrive gets a refresh.

Query caches

• On a premium capacity, if the datasets reside, then you might be able to improve the performance of any associated Power Bi reports and dashboards by enabling the query caching.
• The Power Bi query caching instructs the premium capacity user to use its local caching service to maintain query results, and also avoid having the underlying data source compute those results.

Tile Caching

• This happens for both scheduled refresh and on-demand refresh in power bi.
• You can forcefully do tile refresh by clicking on the More option in the upper right of the dashboard and then click on the Refresh dashboard tile.
• You can consider the tile refresh in power bi as an instinct part of data refresh because it happens automatically.
• You might notice that the refresh duration increases with the number of tiles in power bi.
• The power bi maintains a single cache for every tile, based on the user roles you can use dynamic security to restrict data access.
• Whereas the number of tiles caches multiplies the number of roles.
• And this gets more involved if your datasets use a live connection to an Analysis service data model with RLS(row-level security).
• Then the Power Bi must maintain and refresh a cache for every tile and every user who viewed the Power bi dashboard.

Refresh of report visual

• In the Power Bi Refresh of report visual is less important because it is only applicable for live connection to Analysis service.
• For live connection to analysis service, the power bi caches the last state of the report visual, when you view the report again, and the power bi does not have to query the Analysis service, tabular model.
• When the user interacts with the power bi report, by changing a report filter, Power bi queries the tabular model and updates the report visuals automatically.
• If the user suspects that the power bi report is showing stale data, then the user can click the refresh button of the report to trigger a refresh of all report visual in power bi.