Power BI is not only a cloud-based reporting technology. Due to demand for some business for having their data and also reporting solutions on-premises, Power BI also has an option to be deployed fully on-premises. Power BI on-premises is called Power BI Report Server. This post is about using Power BI in a fully on-premises solution with Power BI Report Server.
In this post, you will learn everything you need to know about the on-premises world of Power BI. You will learn how to install Power BI Report Server, you will learn all requirements and configuration for Power BI report server to work correctly, and you will see all pros and cons of this solution. At the end of this post, you will be able to decide if Power BI on-premises is the right choice for your or not, and if it is, then you will be able to set a Power BI on-premises solution up and running easily.
What is Power BI Report Server
Power BI Report Server is a specific edition of SQL Server Reporting Services which can host Power BI reports. For running Power BI Report Server, you don’t need to have SQL Server installation disk; the Report Server comes with its setup files. You can download set up files (explained in the very next section). Power BI report server can host Power BI Reports as well as Reporting Services (SSRS) Reports.
With Power BI report server, there will be an instance of Power BI Desktop installation. The Power BI Desktop edition that comes with the report server should be used to create Power BI reports. Otherwise, reports cannot be hosted on the report server. The good news is that Power BI Desktop report server edition is regularly updated and its experience will be very similar to the Power BI Desktop.
Requirements for Set Up
You need to download the latest edition of Power BI Report Server from this link:
You will have two installation items; Power BI Report Server, and Power BI Desktop Report Server edition (Which comes in 32 and 64-bit versions).
Installing Power BI Report Server
Set up of Power BI report server is simple, just run the setup file, and continue the instructions.
You can choose to have the evaluation edition (valid for six months) or get the licensed version (licensing of Power BI report server comes later in this post).
Earlier in this post, I mentioned that you don’t need to have SQL Server installed to get the Power BI Report Server. However, The SQL Server database engine is needed for the report server to run. If you don’t have SQL Server installed, then don’t worry, this setup will install the database engine for you.
The remaining steps of installation will be easy to go through to get the set up completed.
After completing the setup, you can open configuration section by clicking on “Configure Report Server”. The instruction, however, asks you to restart and then go to the report server. Both options are fine. If you decided to restart your server, then after restart, go to Start -> Programs -> Report Server Configuration Manager.
Configuring Power BI Report Server
To Configure the Report Server, you need to connect to the server that you’ve just installed. Usually, the instance name of this server is PBIRS
After connecting, it is time to configure the server. The very first step is to configure Databases. To configure the database, Click on the Database in the left side tabs, and then click on Change Database.
Database Set Up
In the Change Database wizard; you can create databases for the report server. Select the option to “Create a new report server database” and then click on Next.
In the next step, you need to connect to the SQL Server database instance that report server databases will be created there. If you have only one local instance of SQL Server database, then you can connect to it with a single dot (single dot means the local database server). If not, then you should enter the database server and username and password required for that. You can also test the connection afterward to make sure everything is correct.
In the next step, choose the database name (default is ReportServer) and continue
There will always be a second database called Temp database; you don’t need to configure anything about it. Continue the wizard. In the next step, just set the credentials and continue
After confirming things in the Summary step, the set up will continue and finishes soon after. The window below shows a successfully set up of database creation.
Click on Finish. Create database set up is done now, the next step is to set up URLs.
Web URL Set Up
Report Server needs to web URLs to work; The web service, and the web portal.
Web Service Set UP
To create the web service, click on the Web Service URL in the left side tabs.
You can set up configurations such as the address of the server, the port that this web service will be running and any other configurations. If you go ahead with a basic set up, you don’t need to change anything here, just click on Apply.
Changing the configuration in the above screenshot is only required when you want to set it up on a different port, or different server, with a specific configuration. If you want to do that, it is best to consult with a web admin in your organization.
After a successful set up for this step, you should see messages and a URL that you can click on to open report server’s web service.
If you click on the URL, you should see the web service’s page up and run without any issues or errors.
In the report server’s page, you won’t see anything except the version of the Report server and the name of it. Later when you upload Power BI files, you’ll be able to see the content there.
Web Portal Set Up
To set up the web portal, click on Web Portal URL in the left-hand side tabs, you can do some configuration if you want for the service, and then click on Apply.
Similar to the web server setup, if you want to do the advanced setup, consult your web admin before this action.
If the set up finished successfully, you would see the success message, and you can click on the web portal URL to open it in a browser window.
The web portal should show you the environment of Power BI report server’s admin view. There is no content in the server yet, later on in this post, we’ll be adding content to this server.
Installation and configuration of the Power BI report Server are now finished. You can close the Report Server Configuration Manager.
Installing Power BI Desktop Report Server
Power BI reports that you can host in the report server needs to be developed with a specific edition of Power BI Desktop called Power BI Desktop Report Server. You get this edition of Power BI Desktop from the same link that you download report server from it.
After a successful installation, you can open the Power BI Desktop Report Server. The Power BI Desktop Report Server is very similar to the normal Power BI Desktop with a slight difference.
Developing Reports with Power BI Report Server
You can start creating a report in the Power BI Desktop report server similar to the way that you do it in a normal Power BI Desktop. The report development experience in these two editions is very similar. You can even open a report developed with normal Power BI Desktop in the Power BI Desktop Report Server.
Power BI Desktop Report Server is slightly behind the Power BI Desktop. There are some features for development added in the Power BI Desktop which takes few weeks before it is implemented in the Power BI Desktop Report Server. One of the features you will lose in this edition is preview features of Power BI Desktop. Because report server cannot run the preview features, these items are not available in here.
You can run normal Power BI Desktop and the Power BI Desktop Report Server at the same time on your system.
Publish Report to the Report Server
There are two ways to publish the Power BI report to the report server. One way is from Power BI Desktop Report server edition. First, you need to set up the URL to your report server. Go to File menu, and from Open, select Power BI Report Server.
In this window, you can connect to a report server. Enter the web portal URL from the step of configuring the report server in here.
Similar to the option above, you can go to Save As, and select Power BI Report Server as a destination and save your report there.
After successful deployment, you will see a message with a link to the report.
The report hosted on Power BI Report Server will be a fully interactive report like a Power BI report hosted in the service.
Another way to publish a Power BI report to the report server is the Upload item in Power BI report server; you can use the upload option from the web portal.
Managing Dataset on the Report Server
A Power BI report published to the report server can be configured to refresh. To do this configuration, open the report server web portal, and click on the more options of the Power BI report.
In the Manage tab of a report, you can configure things such as data source configuration, connection to the data source, and schedule refresh if required.
Schedule Refresh Requirement
If your report is sourced from a file, then you may have some requirements to schedule it for a refresh. You would need to source the file from a network path.
If you use a network shared path to access to the source file, then you can set up the connection to the file.
Make sure to click on Save after this step. Otherwise, you won’t be able to schedule the refresh process.
Then you can click on the Scheduled Refresh and create a scheduled refresh plan.
The scheduled refresh configuration of the report server has much more options compared to the Power BI Service; you can choose to schedule hourly, daily, weekly, monthly, or any custom period. You can choose the start and end date and many other configurations.
The important point for scheduled refresh plan to be successful is that SQL Server Agent service should be up and running.
In the Scheduled Refresh section, you will also be able to see a list of configurations and their status.
Pros and Cons of the Report Server
Power BI Report server comes with advantages and disadvantages.
No Gateway Needed
Yes, you read it right, with Power BI Report Server you do not need a gateway. Gateway is only for all connections from Power BI Service. Gateway is responsible for connecting the data set from Power BI service to the data source on-premises. With Power BI Report Server, everything is hosted on-premises. You do not need to install or not to configure the gateway.
All Types of Connections Are Supported
At the very early releases of Power BI Report Server, you could have only created a live connection to SQL Server Analysis Services from Power BI reports. Nowadays, you can use any types of connection. The example you have seen earlier in this post used Import Data and then scheduled the report to refresh. You can also use the DirectQuery connection or Live Connection to Power BI report server.
The screenshot below is an example of data source management for a Live connection Power BI report.
Power BI Report Server is a Fully On-Premises Solution
I am writing this post while I am flying on a 17 hours flight my first leg from New Zealand to the UK with no internet connection. All of the examples, screenshots you have seen is running without any internet connection. Power BI report server is a fully on-premises solution. You will not publish your reports to Power BI website, and you will not need any cloud-based technology for that to work.
*Power BI report server is an on-premises technology choice for companies who are not yet ready to move to cloud-based technologies.
Power BI Service Features are not Available
Power BI report server has many great features. However, it also has some drawbacks. One of the main drawbacks of the Power BI report server is isolation from the Power BI service. You won’t get great features of the Power BI website on the report server. In the website, we have features such as usage metrics of the report, Power BI apps, Q&A and quick insights, and many other features, which are not available at the moment in the report server.
Report Server team, however, are working hard to make these features available in the on-premises version sometime in the future.
Licensing of the Report Server
Power BI Report Server comes in only two types of licensing; Power BI Premium, or SQL Server Enterprise License with Software Assurance. I have explained about the licensing requirements in licensing post.
Power BI Report server is an on-premises reporting technology. With Power BI report server, you bring interactive Power BI reports into on-premises servers. This type of technology is based on SQL Server Reporting Services technology. You will need to set up Power BI report server alongside with a specific edition of Power BI Desktop.
There are some pros and cons for the Report server. With Power BI Report Server, you can host reports fully on-premises with no need for Power BI website. You will not need a gateway, and all types of connections (Scheduled Refresh, DirectQuery, and Live Connection) are supported. Power BI report server, however, doesn’t have all the features and functionalities available in the Power BI service.
Power BI report server needs a specific licensing which comes either from Power BI Premium or SQL Server Enterprise license with software assurance.