By: Mason Prewett
My team and I here at Key2 Consulting are currently working with a client that is implementing a full Power BI service offering. For the project, we are handling both a cloud and an on-premise implementation of Power BI, as the customer’s requirements demand both.
In this article, I will discuss some key differences between on-premise and cloud Power BI implementations, as well as some important lessons learned from my experience. I will also describe some critical factors you should consider if you are deciding between the two routes.
Cloud vs On-Premise Terminology
Below are the official product names that I will be using when referring to the cloud or on-premise versions of Power BI.
- Power BI Service – this is the cloud service offered for Microsoft Power BI
- Power BI Report Server – this is the product name for the on premise installation of Power BI
There are many feature comparison charts for these two versions of Power B available online. For this article, I will be providing the Microsoft documentation for this.
New and Important Power BI Concepts
Power BI Service offers much more than just reports to provide to the end user. Here are the key aspects of a Power BI Service publishing environment in the cloud.
- Report – a PBIX file that pulls data from data sets and allows developers to create meaningful visuals that the end user can interact with and analyze.
- Dashboard – a high-level view of KPIs (key performance indicators). Each KPI represents a single metric that can be used to make business decisions. A user can analyze the KPI further by viewing reports that are linked to the KPI and provide more detailed information. This article by EDUCBA contains an excellent breakdown of the differences in dashboards and reports.
- App – a collection of dashboards and reports that is packaged into one solution and can be shared with end users. Read more about apps.
- Workspace – where reports, dashboards, and apps can be published to and shared with end users.
It is sometimes difficult to know the differences between the resources outlined above, but the general idea flows like this: a workspace contains an app that provides dashboards which can link to more detailed reports.
An app doesn’t have all of these resources and can have only dashboards or reports. The main purpose of an app is to provide a single point of access that contains all of the resources created for a solution.
I have created the diagram below to help visualize an example setup.
Power BI Report Server – Limitations
Power BI Report Server only provides support for reports to be published and shared with end users. There is no support for workspaces, apps, or dashboards. Given that Power BI Report Server is based on SSRS technology, reports can be published and shared similarly to the way that SSRS reports are done so in Native Mode.
Power BI Report Server is Actually SSRS Native Mode
Power BI Report Server is just a separate version of SQL Server Reporting Services Native Mode that supports Power BI reports. This is a different installation than regular SQL Server Reporting Services. This article explains the installation process.
Power BI Report Server has the capability to provide a fully functional version of SSRS and a limited version of Power BI at the same time. It is worth noting that the SSRS functionality cannot be disabled on Power BI Report Server.
Ideal Power BI Infrastructure/Setup
Choosing which version of Power BI to adopt depends on where your data resides, as reporting tools should always follow your data. The ideal scenario is to have your Power BI instance and your reporting data both be either in the cloud or on-premise.
- Power BI Service is best suited if reporting data is hosted in the cloud
- Power BI Report Server is best suited if reporting data resides on-premise
Power BI Hybrid Scenarios (Cloud & On-Prem)
Power BI Service offers the ability to have reports in the cloud that source on-premise data. This is done by using a Gateway Data Source. The Power BI admin portal provides a section where gateway data sources can be configured with an on-premise server name, database, and credentials. (We are currently assisting a client with this very thing today, and the process has proven to be pretty seamless and effective.)
This can be shared with a report developer and used as a data source in the development of a Power BI report. An on-premise server has to be configured with gateway services which will be used to communicate the data request with the Power BI cloud services.
Pulling data from on-premise to the cloud will likely not produce acceptable performance for a live query when using the direct query method for retrieving data. However, the data import method will allow this action to occur on a customized schedule during non-business hours and the end users will be pulling data from the imported data model instead of a live query for each report request.
Power BI Report Server supports cloud data sources but I would only consider this approach when using the import data method as well. Using direct query in this scenario would suffer from the same performance degradations that you would see when sourcing on premise data from Power BI in the cloud.
Power BI Service and Power BI Report Server Use Different Versions of Power BI Desktop
Power BI Service and Power BI Report Server use different versions of Power BI Desktop for developing and publishing reports. Neither version supports the ability to publish outside of its intended environment. It can be confusing if both of these versions are installed on a developer’s workstation. There are 2 ways to easily tell the difference: the color of the application icon and the fact that the on-premis version always contains the month and year of the version in parentheses of the application name. See the screenshot below for an example of the two versions.
PRO TIP – The version of the Power BI server instance should always be at least the same or newer than the version of Power BI Desktop being used by developers. Publishing errors will occur if the version of Power BI Desktop is newer than the server version. This is something to consider when developing an update strategy for Power BI Server and Power BI Desktop versions.
Thanks for reading. We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, Star Wars, or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!