Update: Power BI Personal Gateway is now GA. You can download the latest version of the Gateway clicking the download icon on the Power BI service. If you have a preview version installed, uninstall it and then re-install the GA version.
One of the most awaited and requested feature is now available in Power BI. Starting today, in Power BI you can refresh datasets connecting to your on-premises sources such as SQL Server. You can refresh a dataset that has been created from a Power BI Designer file or an Excel workbook with data imported into the workbook using Power Query or Power Pivot. An Excel workbook with only worksheet data (without data model) cannot be refreshed in Power BI. For these datasets the Power BI Personal Gateway enables automated refreshes against on-premises data sources in your organization, all via a single gateway you can install directly on your desktop machine.
All common data sources are supported:
- SQL Server
- Oracle
- Teradata
- IBM DB2
- PostgreSQL
- Sybase
- MySQL
- SharePoint List (on-premises)
- File (CSV, XML, Text, Excel, Access)
- Folder
- Custom SQL/native SQL
In addition to on-premises sources refresh, we have enabled two new features for refresh from cloud or on-premises:
- More frequent refreshes – you can now set up a schedule to refresh more than once a day
- Refresh failure notification/history – you can be notified via email when a refresh fails and also find detailed information in the service for the cause of the failure. You can also view your refresh history and see any failures/successes.
In this blog post, we’ll introduce these new features.
In order to refresh a dataset connecting to an on-premises data source you need to install and configure the Power BI Personal Gateway.
Getting started is easy. Head to the download section and download the installer. The Gateway should get installed on a machine that is online continuously so that the service can reach it to get the new data. Your desktop will work great, you don’t even need Administrator privileges (read the docs for more details). Once installed, the Gateway runs as the user who configured it.
Run the installer
Next log into your Power BI account, so we know how to connect to your Personal Gateway.
Now you are all ready to refresh some data!
Log into PowerBI.com and upload your Power BI Designer file or Excel workbook that uses an on-premises data source. Once the resulting dataset is visible in the portal. In the Navigation pane, in Datasets, select the dataset that uses an on-premises data source and click on the “…” to open the Menu.
Select Schedule Refresh to access the settings page for that data set. For example, the settings for a dataset with on-premises data source will look like the below screen.
You should see that your Gateway is online
Now configure authentication for the data sources, if you see the exclamation point next to a data source name. Depending on the authentication type needed for your data source, you’ll get different options, in many cases there is no need to select anything, simply confirm that Windows Auth should be used for this data source (and the Gateway will use your Windows Credentials since the Gateway is configured to run as you. Windows Credentials you configured it with).
Your dataset can now refresh against on-premises data. That was easy, right?
You can read in depth about the Personal Gateway here
Refresh more than once a day
Up until today, you could only schedule a refresh for a once a day. Also, you could only choose a time range for the refresh schedule.
Starting today, you can set up multiple refreshes in a day and can also choose a time to which refresh should get triggered.
In the refresh settings page, you click on “add another time” option to add multiple times in your refresh schedule. At the moment, we support adding eight time slots in the schedule.
Refresh history and email notifications
Previously, when refresh failed we would not show the failure details. This experience has been updated to now show the failure details when refresh fails. In addition, we also send emails to the user who set up the refresh when the refresh fails.
Often, you may not have the refresh failure email handy or just want to check the refresh success rate for your dataset. For this, you can now access the ‘Refresh History’ from the settings page.
Clicking on the Refresh History, will bring up a view with the last ten refreshes.
We are continually improving the experience of these features as we receive feedback from our users. So keep sharing your feedback and look out for regular updates.
Update 2015/07/08
As some of you noticed the link for the download didn’t appear for everyone. This was due to a deployment issue which has now been corrected. We apologize for any confusion this created. As of 8am PST on 07/08/2015 the issues is resolved and the download should now be available in all regions.
Update II 2015/07/09
Some users may notice that the refresh schedules are delayed and are not running at the scheduled time. This is a known issue and a fix is in progress. Refresh Now is not impacted by the issue.
Note: As of 07/22/2015 the issues is resolved and the refresh schedules are running at the scheduled time.