Power BI Report Server; Power BI in On-Premises World

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

https://powerbi.microsoft.com/en-us/report-server/

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.

Summary

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

21 thoughts on “Power BI Report Server; Power BI in On-Premises World

  • 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 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

  • 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

  • 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

Leave a Reply to Slap Cancel reply

Your email address will not be published. Required fields are marked *