If you have been looking at Power BI, or are in the process of implementing it, one of the things you have undoubtedly wondered about is whether you can surface your on-premise data in Power BI. The short answer is yes, and in this blog post, I’m going to walk through how that is done within the context of Power BI. I’ll talk about the difference between accessing on-prem data using Power BI Desktop and Power BI Service. I’ll also talk about using the Power BI Personal Gateway and the Power BI Enterprise Gateway to give Power BI Service secure access to your on-prem data.
Before we dive in, I must point out that Power BI is an ever-changing tool, and the information in this guide at some point might go out of date. We do our best to keep this post current, but if you notice that there’s new information that’s missing, let us know in the comments.
Desktop vs. Service
When it comes to Power BI, there are two main ways to use it: Power BI Desktop and Power BI Service. Power BI Desktop is a free desktop app you can download to your computer. In this app, you can connect to data and create reports and dashboards. Power BI Service is a web app within Office 365 that you log into and do the same kinds of things as you did in the Desktop version. In addition to that, you can also publish from Power BI Desktop to Power BI Service and share your dashboards with others. There are some differences between the two products, but that’s beyond the scope of this post. What’s important to know is that there are two pieces, and they can be used separately or together to super-charge your Power BI experience.
In April 2019, Microsoft also released Power BI Report Builder, a new desktop application focused on building paginated reports that can be hosted in the Power BI Service. This is a separate application from Power BI Desktop, and is effectively the SSRS report builder for connecting to Power BI datasets.
On-Premise Data Using Power BI Desktop
If you are already using Excel to connect to these data sources, the idea is the same. When you’re inside your corporate network, you can easily use Power BI Desktop to connect to your data sources. However, once you leave your network, there would need to be external connectivity enabled for the on-prem data source. Sometimes, though, how you connect can change depending on whether you are in the network or not. This means you might need to make some adjustments every time you move. For example, when you are at work, you connect to the sales database by connecting to “SalesDB”, but when you are working from home, you might need to type “sales.apps.contoso.com” to get the same access. What you need to consider when using Power BI Desktop is where the data is stored and how it is accessed.
Power BI Service
If you are already using Office 365’s Excel Online to connect to source data, the procedure you’ll follow for Power BI Service will be the same. There is a shared workspace in the cloud that people can connect to and access files. This data needs to be refreshed for all users, which means the data source needs to be accessible from that server. In Power BI Service, you would use a Power BI Gateway to allow Power BI Service to access your data in a secure fashion. For data sources that are not externally available, you would install a gateway on a machine (typically a server) in your corporate network. Once the gateway is configured, it acts as a go-between for the Power BI Service and your data. When the Power BI Service needs to get data, it asks the gateway for it. This means you don’t need to expose your data to the entire internet or change authentication mechanisms to get at it. The one thing you need to consider with Power BI Service is how you’re going to access data from outside your organization.
Enterprise Gateway vs. Personal Gateway
There are two choices when it comes to gateways: Enterprise Gateway or Personal Gateway. The difference between these two options comes down to governance and data sources you can use.
Let’s start with the Personal Gateway. The user installs the Personal Gateway onto a personal use computer, usually a desktop or laptop machine, and points the gateway to any Excel workbooks or Power BI Desktop files used as sources. An example would be a user with a desktop computer at work who needs to surface a list of third-party vendors. Once the installation is complete, the gateway will refresh data and send it to the Power BI Service to be used in the corresponding reports.
The Enterprise Gateway does all of this and more. With the Enterprise Gateway, you can have an administrator centrally set up connections to data sources in the company (both files like Excel workbooks but also to databases and cubes), and then specify which users get to use that connection. IT can also see statistics about how these connections are being used. Lastly, these connections can be used for live data access. This means that when a dashboard or report is accessed, the data is pulled live from the on-prem data source instead of from a stored version retrieved with a refresh schedule.
You can use both types of gateways in your organization. By doing that, you maintain governance over important or sensitive data sources with the Enterprise Gateway and ensure that users have the flexibility to collaborate by leveraging the benefits of the Personal Gateway.
Power BI Report Server
For some organizations, publishing their reports to the Power BI Service is not practical (or not allowed) even if the data stays on premises (which is the focus of this post). Power BI Report Server is an option in this scenario because it allows to connect on-premises data in published reports without using any cloud services. Power BI Report Server does not have all the functionality of the Power BI Service, nonetheless it’s a useful tool for sharing and securing data and reports for users.
In Summary
In this post, we talked about Power BI Desktop and Power BI Service, and how the two are different when it comes to accessing on-prem data. We also covered the differences between the Power BI Personal Gateway and the Power BI Enterprise Gateway. If you want to know more about Power BI, or you have questions about the material I discussed in this post, reach out to us.
Resources for Power BI Desktop:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-getting-started/
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-sources/
Resources for Power BI Service:
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-get-started/
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-get-data/
https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/
https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/