Power BI Report Server vs SQL Server Reporting Ser...

nicolewells · ‎09-19-2017

Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.

Last year Microsoft released a Power BI on SQL Server Reporting Services preview for some then “future” version of SQL Server. This version basically allowed you to deploy your Power BI reports to SQL Server Reporting Services to share them with your organization. That version was based on the Power BI embedded which was extremely limited.

In answer to this problem Microsoft has now created Power BI Report Server.

Introducing Power BI Report Server

Power BI report server is a new component which comes as included in the licensing of SQL Server Enterprise Edition with SA or as a free extension of Power BI premium. It is not a replacement for SQL Server Reporting services as originally speculated.

Interestingly, if you do not have an on-premise SQL Server license but you purchased Power BI Premium and you want to run Power BI Report Service on-premise, you will have the right to run a server with the equivalent number of cores in your premium subscription for it.

Power BI Report Server Installation and Configuration

Installation of the Power BI Report Server is fairly straight forward. Just download and install following the wizard steps.

The only thing to note is that it does not install SQL Server, so you need to install one or have access to one before commencing your configuration. As you can see in the image below it says “Install Database Engine”, which is actually just a reminder that you need one.

Once the Power BI Report Server is installed, you need to configure it. When you open Report Server Configuration Manager, you will get a distinct sense of Déjà vu, since it looks exactly like Reporting Services Configuration Manager except for one additional option at the bottom: Power BI Service (cloud), which allows you to specify a Power BI account to connect to the Power BI Service in the cloud.

Figure 2: Configuration

Power BI Reports

The advantage of being able to use Power BI reports is that you can create interactive reports. If you are familiar with Power BI, you will know that as a developer you can create datasets and allow your business users to create their own reports to change the visualizations and layouts to suit them without having to request that from development, saving everyone a ton of time.

Authoring Reports

Creating Power BI reports is where the catch comes in. Even if you are running everything on-premise and you are not using any Power BI in the cloud. You will still require a Power BI Pro license for each person who wants to author and publish reports.

To be able to publish reports to Report Server you need a special version of Power BI Desktop, currently known as Power BI Desktop (Report Server) which comes as part of the Power BI Report Server download, the version of which needs to be kept in sync with the version on Power BI Report server. As explained in the section up upgrades later on.

Mobility

Power BI Report Server will allow you to view your SQL Server Reporting Services Reports as well as your Power BI reports through the Power BI app on any device, through the Power BI Report Server Web Portal.

Workflow

The typical work flow would be:

Figure 3:Power BI Report Server Work Flow

Important things to note here is the data is not imported into the Power BI Service as with typical Power BI reports. Instead the Report Server connects to Analysis Services (in this case on-premise) live, to ensure that the data stays safe within the boundary of the customer’s firewall.

Data Sources

When Power BI Report Server is released initially it will only be able to connect to SQL Server Analysis Services (SSAS). Both multi-dimensional and tabular models are supported.

If you try to deploy a report which is connected to Excel for example you will encounter the below error:

Figure 4: Unsupported data sources

Publishing to SQL Server Reporting Services (SSRS)

Even though deploying a report to Power BI Report Server is referred to as publishing, you won’t find Report Server under the Publish Menu.

You have to Save As… to save the report to the Report Server.

Figure 5: Deploying a Power BI report

Migrating from SQL Server Reporting Services (SSRS)

You can migrate existing reports from your SQL Server Reporting Services to Power BI Report Server with minimal effort for native mode at least:

Backup existing SQL Server Reporting Services Database
Install the Power BI Reporting Server
Restore Reporting Server Databases to Power BI report server instance
Connect to Reporting Server Databases using the Report Server Configuration Manager.

If you are using SharePoint mode there is a script you can use which can be downloaded here.

System Requirements

To be able to install Power BI Report Server, you need at least:

Windows Server 2012
SQL Server 2008 or later
SQL Analysis Services 2012 or later

In terms of licensing, as long as you have an active SQL Server license with Software Assurance, you are goo to go. Alternatively, you need Power BI Premium.

Updates

Unlike SQL Server which only gets updated with Service Packs and new versions. Power BI Report Server will get updated 3 times a year and have additional security patched in between if that should be required. This is known as the Modern Life Cycle Policy.

Since Power BI Report Server goes hand in hand with Power BI desktop, you need to ensure that the Power BI desktop version which will be used by all users will be upgraded at the same time as the Power BI Report Server, to ensure that the versions remain in sync.

Figure 6: Upgrade must occur simultaneously

Anonymous · ‎03-13-2019

So if we have software assurance, does this require the licensing requirements needed to allow users across the business to develop reports and host them on the server.

Does it also cover the licensing to allow us to make reports available to our clients via our internet, embedding into websites and mobile apps?

alxdean · ‎08-06-2019

@Anonymous , that would be a solid asumption. You would need to configure the PBI Server to use custom authentication as windows authentication would require the users to be on the domain and as such they would need a Windows Server CAL.

Alternatively you can look at PowerBI Embedded which is much cheaper than PowerBI Premium for the scenarios you listed, if you don't mind using the online service instead of on-prem. But if you already have Enterprise with SA, then using the on-premise server will work out most cost effective.

kskarthick · ‎07-05-2018

Hi nicolewells,

This is very good to understand the architecture.

I need one clarification, i have SQL Server Enterprise license with SSRS.

Again i need to purchase again Power BI premium License to publish Power BI reports.

alxdean · ‎08-06-2019