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.
Video
What is Power BI Report Server
Power BI reports can be hosted in two environments; cloud-based on on-premises. The cloud-based Power BI hosting is called Power BI Service. The on-premises option is called 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
- Dashboard
- Workspace
- Apps
- Template App
- Shared Dataset
- Q&A
- Cross Report drill through
- Preview features
- Metrics
- Dataflow
- Datamart
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.
Summary
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.
Thanks a lot for this post, it was very useful for me.
I am using a SSAS-T model. I connect Power BI Desktop successfully with the SSAS server, but when I publish the report on the server, (using the manage live connection example), I obtain this error:
Data source AnalysisServices is not supported.
I don’t understand the error….. ¿Would you know what it is?
Hi Cris
I imagine you are using Live Connection? what is the version of SSAS you are using? The live connection is only supported on 2012 SP4 CU1 upwards. and what edition? BI? Enterprise? Standard? there are some limitations on that too.
Cheers
Reza
Hi Reza,
This is really a great post.
I have never used Power bi report server before, so need some clarity on the below point.
Currently I have developed a Master Dataset and published it to power bi services (my personal PRO Account). Then created different reports (separate PBIX files connected to above Master Dataset) and published to powerbi Services.
Now the client asked me to deploy the above reports & Master Dataset to his premium account.
Is it possible to publish the master dataset and and then connect different reports (PBIX files) to the master dataset through Power BI datasets connection.
Thanks
Hi.
You said client asked to publish under his Premium account, I think you mean his premium capacity. Premium capacity is still in the Power BI website. Using premium doesn’t mean it is report server. Report server is just one of the functionalities of the Premium (which may not be used for some people at all). If your client still wants the model in the cloud, then you can continue the way that you did it already, but under the premium capacity settings.
Cheers
Reza
Hi Reza,
We are planing to get a Power BI report server through SQL Sever Enterprise Edition with SA. My question is can users (another deveoper having pro lincese) connect to a dataset which is published on to the Power BI report server? As in similar to the live connection available on Power BI service?
Regards,
Maddy
Hi Maddy?
Do you mean similar to Analyze in Excel feature in the website? if your data source connection is a live query connection, then users can directly use Excel to connect to underlying Azure AS.
Cheers
Reza
Is there a way in your Power BI Report Server db to find data sources that are in the .pbix files that are stored on the On-Prem Report Portal?
In SSRS you can find them and I am wondering if there is a way to see what is in your .pbix files.
If so any shared queries will help. If not how do you manage the published .pbix files that you are allowing the business to publish? I am trying to establish a way to find all the data aources to prevent or police the data sources from publishers pointing their data sources to Production data sources from test and from Production to Test.
Hi.
It is possible, this information is part of the *.pbix file.
it is a good idea for our Power BI Helper application I think, we will add it to the documentation hopefully for the next version
Cheers
Reza
Hi, is it also possible two install two instances (TEST/PROD) of the PBIRS on 1 server?
Hi Peter.
I haven’t checked it, however, the original Reporting Services, was allowing multiple installations on the same server, with just changing the database and web config. Power BI report server might be the same, however, PBIRS has an SSAS builtin in that, I have to check if it is supporting multiple installations on the same server
Cheers
Reza
Dear Reza
Thanks a lot for your tutorial which is really one of the best ones.
Regards,
Alireza
Hi Reza,
I hope you are well.
Regarding the Power BI Report Server, I know it can automatically refresh the data in the back end (the data set is refreshed).
However, once the report is opened, can it be automatically refreshed (in the front end)?
The idea is to have a report that receive refreshes every 5 minutes and so the updated data is displayed in the screen without any human iteration.
Is it possible?
Thanks,
Rafael
Hi Rafael
There are some browser add-ons for Chrome and other browsers that you can install and get your page refreshed on the schedules you want. The report itself doesn’t refresh automatically.
Cheers
Reza
Hi Reza, we use PBIRS, it works perfect. To launch pbi refresh we set task and had to configure login/pwd for each datasources. Fine.
… but, 😉 with my login/pwd, i have to manually change all passwords monthly. :o/
So i asked my admin to create a generic user. And there he told me : “we do use generic users with our own job scheduler”. And here come my question : i read that it was possible to run powershell command with the power service. Is it possible to run command as well with the onprem ?
Like i’m in a job scheduler (not the rs one), and i would set a job to be executed each morning :
job : Refresh path/on/the/server/mypowerbi.pbix
Hope this is clear…
Thanks
Hi Stephane
Not sure If I understand your question correctly. when you have the PBIX file in PBIRS, then you can schedule it to refresh whatever you want.
If the password for your user is changing, you have to consider creating a service account user (which you called it here generic user), and use that in your data source configuration, and then everything else would be fine. You won’t need PowerShell or anything like that.
Cheers
Reza
hi
I have a problem in showing web portal of PBIRS . First of all i config the SSRS configurations.It works right.Then i install and config PBIRS configurations,but when i open web portal , it shows the SSRS web Portal!
PBIRS and SSRS are having very similar portal views. If you want to install these two separately, then you need to set them on different servers or different ports on the same server. However, you can keep both SSRS reports and PBI reports under one PBIRS if you want.
Cheers
Reza
Hi Reza, seems PBIRS doesn’t support embed in a public website like Power BI service?
Do you mean publicly for ANYONE to see? or embedding it in a webpage? if the latter, then read this article.
Cheers
Reza
Hi Reza, i mean embed in public web just for viewing purpose.
have you checked this?
Cheers
Reza
Hi Reza,
Great article! I was wondering about the security concerns with power bi report server. Can you please shed some light on data and report security? Especially if I set a url filter to filter the report for different users, will that pose a security threat?
Hi Richa
What do you mean by a security threat?
The report server is installed on-premises. no one from outside of your organization would be able to access to it. In fact, only people would be able to access to it, whom are authorized and the reports are shared with.
Cheers
Reza
We are finding that our Power BI reports run much slower when published to Power BI Report Server than when published to the Power BI Service. In Power BI Desktop the reports render in 2-3 seconds. When accessed from the Power BI Service it takes about 4 seconds and when accessed from our Power BI Report Server it takes more than 8 seconds. These numbers are for Chrome. Edge takes another 75% longer.
Our Report Server has plenty of CPU’s, RAM and Flash Storage. The data is stored in the PBIX file itself, so querying SQL server is not the issue. And the performance problems happen when only one user is hitting the PBiRS server at a time, so it’s not due to too many simultaneous user connections.
Has any one else experienced these Power BI performance differences between PBI Service and PBI Server and Chrome vs Edge?
Any benchmarks for displaying Power BI Report Server reports as well as performance diagnostics and tuning guidance would be greatly appreciated.
Hi Michael
What else is installed on the server that Report Server is running on it? any other services/users/applications that use CPU/Memory can affect the performance of the report server.
Cheers
Reza
I need a topology to run PBIRS in our company. I mean the capacity of the servers and etc
can you help me on this
Here is a recommended spec
Cheers
Reza
Hi Reza,
We have done the Dash boarding on Powerbi Desktop(*.PBIX). We are planning to take from cloud to on premise(SQL Enterprise with SA). Will my exisiting(*.PBIX) can be used in PowerBi Desktop optimized or I need to redesign the entire dashboard.
Also we will be embedding it into portal, what resource will it require.
Pankaj
You need to build your PBIX files using the specific version of Power BI Desktop which is accompanied by the report server, we call it Power BI Desktop Report Server.
However, most of the time, you can open the PBIX file (that you created using normal Power BI Desktop) in the Power BI Desktop report server, and save it, and then publish it to the report server.
Some of the features of Power BI Desktop are not, however, available in the report server.
Cheers
Reza
Hi,
Great article, i could create server using Enterprise license without any issue.
Question: How do you publish or share report to MVC or Core application?
Hi Raj
Do you mean embedding Power BI reports from the report server? have you checked this article?
Cheers
Reza
Is there a way to change the UNC path to a file after it is uploaded or saved to the PBIRS. The data sources are always pre-populated and dont allow for editing, In my case I have dev, test and prod servers and the file structures as well as domain accounts are different. I know this can be done by opening the desktop version and changing them and doing a refresh but this is bot possible because of change control and who has access to what. I tried changing the path in my desktop with apply changes later, but since they were never applied, when I uploaded / saved to a different server the old path was still there.
Thanks
you can use Power Query parameters for that. here I explained a method.
Cheers
Reza
Hi Reza,
Kia Ora, we are currently exploring Power BI Report Server and we installed it to our Dev Env, and uploaded a pbix that connects live to our SQL Server AS 2019 Developer successfully.
However when I view the report in PBIRS web for the first time, I got an error saying “We couldn’t connect to the Analysis Services server. Make sure you’ve entered the connection string correctly”. I have to change the credentials manually to “Using the following credentials” to make it work.
Thanks
Kia Ora Gil
There are lots of details around creating a live connection from Power BI. Here I explained a bit of that (through the Power BI service and gateway, however).
these are places that I would go and check for this kind of issue:
– can this user create a connection to the SSAS from SSMS or SSDT?
– How did you set up the dataset in the Power BI report server
– what is the permission of the account running the report server service
– how is the row-level security implemented in SSAS
Cheers
Reza
Hi Reza
I have installed power bi report server on a sql development server I have on my local machine. I can deploy reports and view reports with no problems. I would now like to create some mobile reports to view reports using iPhone. I see from the app that you can connect to report server but having difficulty connecting. Can you please help me out on how to connect from iPhone to report server on my local network. I am doing all of this at home on my desktop. Any help will be appreciated.
Joe
Hi Joe
you can follow the instruction here.
Cheers
Reza
Hi Reza,
Thanks for the detailed post.
Currently, we have a client who has SQL Server Enterprise License and installed the PBIRS based on it. However, we have a question, for non-production system, could we use developer edition of PBIRS and connecting with the SQL Server database with Enterprise Edition?
Best Regards,
David
Hi David
If you want to use the Developer edition for your development environment I guess it is fine. I am not sure how terms and conditions of Microsoft is for using it in the production environment though. Better to check that with your Microsoft sales rep.
Cheers
Reza