Power BI is not only a cloud-based reporting technology. Due to the demand for some businesses to have their data and reporting solutions on-premises, Power BI also has the option to be deployed fully on-premises. Power BI on-premises hosting is called Power BI Report Server. This post concerns using Power BI in a fully on-premises solution with Power BI Report Server.
This post will teach you everything you need about the on-premises world of Power BI. You will learn how to install Power BI Report Server, learn all requirements and configurations for the Power BI Report Server to work correctly, and see all the 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 you, 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 that can host Power BI reports. For running Power BI Report Server, you don’t need an SQL Server installation disk; the Report Server comes with its setup files. You can download set-up files (explained in the very next section). The Power BI report server can host Power BI Reports and Reporting Services (SSRS) Reports, also called Paginated Reports.
With the 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 the 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 the 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 the 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 the Power BI report server comes later in this post).
Earlier in this post, I mentioned that you don’t need to install SQL Server 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, don’t worry; this setup will install the database engine.
The remaining installation steps will be easy to go through to complete the set-up.
After completing the setup, you can open the configuration section by clicking on “Configure Report Server”. The instruction asks you to restart and then go to the report server. Both options are fine. If you decide to restart your server, then after a 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 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
You can create databases for the report server in the Change Database wizard. 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 the SQL Server database, you can connect to it with a single dot (single dot means the local database server). If not, you should enter the database server and the username and password required. You can also test the connection afterwards 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 the 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 successful set-up of database creation.
Click on Finish. The database setup is done now; the next step is setting up URLs.
Web URL Set Up
The Report Server needs web portals, and you need to set up the URLs for that, 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; 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 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 running without any issues or errors.
On the report server’s page, you won’t see anything except the version of the Report server and its 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 the 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 the Power BI report server’s admin view. There is no content on the server yet; later in this post, we’ll add content.
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 you download Report Server from.
After a successful installation, you can open the Power BI Desktop Report Server. The Power BI Desktop Report Server is 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 how 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 a normal Power BI Desktop in the Power BI Desktop Report Server.
Power BI Desktop Report Server is slightly behind the Power BI Desktop. Some features are added in the Power BI Desktop, which takes a few months before it is implemented in the Power BI Desktop Report Server (Power BI Desktop updates every month, but the Power BI Desktop Report Server updates every four months). One of the features you will lose in this edition is the Power BI Desktop preview features. Because the Report Server cannot run the preview features, these items are unavailable here.
You can run the normal Power BI Desktop and the Power BI Desktop Report Server simultaneously 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 the Power BI Desktop Report server edition. First, you need to set up the URL to your report server. Go to the File menu and select Power BI Report Server from Open.
In this window, you can connect to a report server. Enter the web portal URL from the step of configuring the report server here.
Like the option above, you can go to Save As, 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 the 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 more options for the Power BI report.
In the Manage tab of a report, you can configure the data source configuration, connection to the data source, and schedule a refresh if required.
Schedule Refresh Requirement
If your report is sourced from a file, 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 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 than the Power BI Service; you can 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 see a list of configurations and their status.
Pros and Cons of the Report Server
As you saw, the Report Server environment has similar configurations (Data source settings, scheduled refresh, security, sharing) to the Power BI Service. Power BI Report server comes with advantages and disadvantages.
No Gateway Needed
You read it right; you do not need a gateway with Power BI Report Server. Gateway is only for all connections from Power BI Service. Gateway is responsible for connecting the data set from the 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 connection (except the Composite model). The example you saw earlier in this post used Import Data and scheduled the report to refresh. You can also use the DirectQuery connection or Live Connection to the 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 wrote the first draft of this article while flying on a 17 hours flight, my first leg from New Zealand to the UK, with no internet connection. All of the examples and screenshots you have seen are running without any internet connection. Power BI report server is a fully on-premises solution. You will not publish your reports to the 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 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 its isolation from the Power BI Service. You won’t get great features of the Power BI website on the Report Server. On the website, we have features such as usage metrics of the report, Power BI apps, Q&A and quick insights, and many other features that are not available in the Report Server.
Here is a list of some of the main features that are not included in the Power BI Report Server;
- Analyze in Excel
- Composite Model
- Template App
- Shared Dataset
- Cross Report drill through
- Preview features
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 the licensing requirements in licensing post.
Power BI Report Server is an on-premises reporting technology. With the 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 a Power BI Report Server alongside 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 without needing a Power BI website. You will not need a gateway, and all types of connections (Scheduled Refresh, DirectQuery, and Live Connection) are supported (Except the composite model). However, the Power BI Report Server doesn’t have all the features and functionalities available in the Power BI Service.
The Power BI report server needs specific licensing from Power BI Premium or SQL Server Enterprise license with software assurance.