By: Mason Prewett
Introduction
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
Feature Comparison
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.
Questions?
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!
Very nicely explained, Mason.
I am working with a client who wants to implement Power BI Reports, preferably on-prem because their data is on-prem.
They would like to source the data from a Tabular model, which is on-prem.
Do you recommend doing a Live connection and having reports on the PBI Report Server ?
I believe if we do an import with SSAS Tabular, then the model is in the PBIX file.
Would be interested in knowing your thoughts.
Many thanks.
Hi Ganesh,
Thank you for your comment and great point! A PBIX file does convert all data to an SSAS tabular model in the background and stores it in the report. This makes live connection ideal in your scenario since the data that you are sourcing is already a tabular model. Again, this is only recommended for Report Server reports connecting to on-prem data.
– Mason Prewett, Key2 Consulting
I have an oracle cloud infrastructure and my customer want a reporting with power Bi and he need to publish in the same environment cloud
What power bi can I use (server or server)
Hi Yousfi,
This should be possible for publishing in either Power BI Service or Power BI Report server. The connection is set up in the development of report in Power BI Desktop and either system can handle that. The only concern I would have would be the need of a gateway from cloud to cloud (Azure to Oracle). I just checked and Oracle is listed as a gateway connection type but there may be set up required on the Oracle side. One thing to note is that there will be some set up required on the development workstation for Power BI Desktop to support Oracle connections (https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-oracle-database).
Thanks,
Mason Prewett
PowerBI RS , once deployed , you dont need any additional user licensing, however the PowerBI service (Cloud) will need each user with some licenses to view the reports
Hi Azeezulla,
Thank you for your comment, and you are correct, Power BI Report Server requires no additional license after installation. Power BI service has licensing models that allow developers to share their reports either with other users with Pro Licenses or with everyone by using a Premium Capacity Workspace. This article goes in depth on this subject:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-license
Power Bi RS don’t support auto refresh with direct query model and this is a big handicap for any on-premise implementation with power bi
Hi Mehtout,
Thank you for your comment. Direct query does not require auto refresh functionality because it gets fresh data from the data source every time the report is loaded or refreshed. You can read more about how this functions here: https://social.technet.microsoft.com/wiki/contents/articles/53078.power-bi-import-mode-vs-directquery-mode.aspx.
Hi, do you know if I would need to use a data gateway if my data sources are all in the Oracle cloud? If so, can you please share a link to the resource? Thanks.
Hi Alex,
Thank you for your comment. I think you will need a gateway for an Oracle cloud data source, but I have had an extremely tough time finding information on this. Here is how a gateway is set up for an on premises Oracle data source (https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-manage-oracle). I can relate this to a Salesforce cloud data source which I know has to have a gateway set up. You can think of it as a bridge from one authentication source to another, whether it is on premises AD to AzureAD or AzureAD to an Oracle cloud system. To my knowledge, a gateway is needed anytime a connection is made outside of Azure, which is where Power BI is hosted.
– Mason Prewett, Key2 Consulting
Greetings from Spain!
I’m quite lost about the ideal solution for this service:
I have a report template that I want to hire (monthly fee) to different (and independant) customers/users. Their DB are stored locally, and shouldn’t go to the cloud. The DBs share the same structure. The user (one per client, the CEO himself) will not be able to edit nothing, just update (whenever they want or at regular/programmed intervals, doesn’t matter) the report with his own data and view/navigate it.
What would be the best strategy (and cheaper) to implement such scenario? What is the ideal way to distribute it? Embedding/integrate it in a App or WPF is possible?
Thanks in advance, and sorry for “abusing” of your time, but it’s so confusing to me at this moment all the options and licensing available… and the docs really don’t help too much to understand everything. Muchas gracias.
Hi Fernando,
Thank you for your comment! Storing the Power BI reports in the cloud service or on premises is still viable. If you choose to store them on premises, then each client will have their own instance of Power BI Report server installed locally. This may be cheaper, but will create multiple environments that need to be managed. If you choose to store these reports in the cloud, it would be just one central location where all client reports can be hosted and separated by workspaces. Choosing the cloud will also require that a gateway be setup for on premises to cloud communication, which would also require some VM’s on premises to operate. For cheapest and easiest, Power BI Report Server would be best. But as the article points out, not all features are available in Power BI Report Server. There are a bit of trade offs with both.
Hope this helps!
– Mason Prewett, Key2 Consulting
Hello Mason,
very usefull post.
One question, to manage reports/users in On-premise solution… would you need Microsoft Office365 licenses?
Thanks a lot!
Saul,
Thank you for your comment! We are glad you found this blog post useful. Power BI Report Server licenses can be acquired by purchasing Power BI Premium or SQL Server Enterprise with Software Assurance. Once this is installed, there are no additional costs for anything like managing reports or users. Hope that helps!