Microsoft’s Power BI is the go-to tool for organizations looking to quickly gain deep insight into their data. Accessible from pretty much anywhere – desktop, mobile or tablet – the business analytics solution brings data to life with brilliant visuals. During BlueGranite’s interactive monthly Power BI Office Hours, open to all, we explore simple ways to pilot new features with this dynamic platform.
This time around from Power BI Office Hours, there wasn’t a January release for Power BI Desktop (although there was a release for the Power BI Report Server version), and we took December off for the holidays, so today, we’re looking back at the December 2018 release.
It offers a lot of great new highlights (a complete list can be found here), including improvements for accessibility features, Live Connect support for Q&A, and support for Top N matches in the Fuzzy Merge.
BlueGranite’s January Office Hours focused on three features – Smart Guides, Tab Order, and DAX Formula Bar updates. You can follow along with our recorded presentation here.
Smart Guides “snap” your visualizations on the page with other visualizations in a PowerPoint-like experience.
Tab Order allows you to specify a custom tab order for items on the report page. This is especially useful in accessibility scenarios, such as where a user may be unable to employ a mouse.
DAX Formula Bar updates include zooming in and a larger view. Prior releases included tab stops and line numbers.
In our Use Case section this month we talked about Gateways. If you are publishing your reports to PowerBI.com and are using Live Connection/Direct Query data sources, or want to schedule your data refreshes, you’re going to need a Gateway.
When we talk about data refresh for published Imported datasets there are two options:
Manual Refresh refers to manually opening the PBIX file in Power BI Desktop and clicking the Refresh button. Once all the data sources are refreshed you then re-publish the PBIX to PowerBI.com manually.
Scheduled Refresh is configured in PowerBI.com and allows you to automate the refresh of imported datasets by setting a refresh schedule (8 times a day for standard users and 48 times per day for Premium users).
For the Scheduled Refresh using on-premises data (and/or Live Connection/Direct Query on-premises data sources) a Gateway is required. A Gateway is not required to schedule a refresh for cloud-based data sources, such as Azure SQL Database or Dynamics 365.
A gateway is essentially a program running on a machine on your network (usually inside the firewall) that PowerBI.com acts as the data access proxy for PowerBI.com. When PowerBI.com kicks off a scheduled refresh, it contacts the Gateway and requests the data required. The Gateway has stored credentials and manages the connection from the data source to PowerBI.com.
There are two versions of the On-premises data gateway:
Personal Mode is generally installed on a user’s individual machine (ideally a machine that is always on) and can only be used by PowerBI.com.
Enterprise Mode is generally installed on a server and managed by IT; it can be leveraged by not only PowerBI.com but PowerApps, Flow, and Azure Analysis Services for data access.
For a detailed deep-dive into On-premises Data Gateways see the “Planning a Power BI Enterprise Deployment” whitepaper by Melissa Coates and Chris Webb.
Once your Gateway is installed (while Multi-Geo support is still in preview, be sure to install your Gateway in the same region as your PowerBI.com tenant) you add and configure your data sources in PowerBI.com under the Manage Gateways section.
After you have configured your data sources you can configure a scheduled refresh.
When using Gateways there are a few considerations to keep in mind:
- The account used for the Gateway should be a service account, ideally one where the password never expires.
- The Gateway cannot access mapped drives (i.e. H:\, S:\) for flat file data sources, so the UNC path (\\servername\share\folder\etc\file.ext) must be used.
- Consider using separate gateways for scheduled refreshes and Direct Query/Live Connections. That way, your scheduled refreshes don’t slow down the queries for Direct Query/Live Connection reports.