Power BI Report Server - the On-Premises Alternative

Moving to the cloud is the future (in fact it is the present) but some organisational policies still prevent moving some of their data into the cloud. And so there is still a need for on-premises solutions. Power BI Report Server is the on-premises alternative to the Power BI Service which hosts reports and dashboards in the cloud. Power BI Report Server reports comes with the flexibility of being able to move to the cloud when the organisation is ready to do so. There are, however, a few differences between the two Power BI versions and hence the need for a separate installation.

Microsoft has made another version of Power BI Desktop available. It is called ‘Power BI Desktop optimized for Report Server’, and can be used to create Power BI reports that can be deployed to an on-premises Power BI Report Server.

With the first release of Power BI Report Server, the most significant of the differences between the 2 versions of Power BI was that the only data source that could be used with Power BI Report Server was a Live Connection to SQL Server Analysis Server (SSAS) tabular model or multidimensional cube. Although connections to other data sources were made available with the August 2017 (preview) release; with limitations to data refresh and no support for direct query, SSAS is still the most feasible data source for a production scenario.

Now, if you haven’t used SSAS as a source for a Power BI report already then the above difference brings a few more differences along with it.

When you use a ‘Live connection to SSAS’ as the data source to a Power BI Report you will notice that you lose the ‘Relationships’ and ‘Data’ views in Power BI Desktop. This is because the functionality available to users in these views is also available in SQL Server Data Tools (SSDT) used to author SSAS models.

Power BI Desktop views:

If you aren’t already using a SSAS model as a data source then the simplest way to move forward would be to create a SSAS Tabular model and pull all the datasets that you already use into this model.

The data modelling will then need to happen in the SSAS model. This includes creating relationships between datasets, adding calculated fields, etc. Basically, everything that would normally be done in the ‘Relationships’ and ‘Data’ views will have to be done in the SSAS model instead. This can be an advantage if the same data model is used as a source for various reporting tools; the data model will not have to be recreated for every reporting tool separately.

In addition to the above, the management of row-level security will also need to be done in the SSAS model instead of in Power BI desktop. Again, providing similar benefits as above.

Power BI, in this case, is essentially used as a data visualisation tool.

One more point worth mentioning would be that if the Report Server and the SSAS database exist on different servers, then you may have to configure Kerberos for constrained delegation. If not, then you might see error messages when trying to view the report after deploying it to the report server. More information on how to configure Kerberos authentication can be found here: https://powerbi.microsoft.com/en-us/documentation/reportserver-configure-kerberos-powerbi-reports/

In conclusion, though SSAS as a live connection is, currently, the only feasible data source for a production-ready Power BI Report Server; there are reusability benefits of using SSAS. Microsoft will be releasing Power BI Desktop optimised for Report Server on a rapid release cycle, and they plan to make it compatible with more data sources and overcome the current limitations with future releases.

So stay tuned.