Reporting in SQL Server Power BI Report Server
Reporting in SQL Server - Power BI Report Server
Figure 1: Power BI Report Server Installation 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
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.
Figure 4: Unsupported data sources
Figure 5: Deploying a Power BI report
Figure 6: Upgrade must occur simultaneously
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg Latest posts by Minette Steynberg (see all) The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017 Introduction to Azure SQL Data Warehouse - August 29, 2017
SQLShack
SQL Server training EspañolReporting in SQL Server – Power BI Report Server
June 13, 2017 by Minette Steynberg 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.Figure 1: Power BI Report Server Installation 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
Conclusion
If you are looking for more interactivity than what your typical SSRS paginated reports can provide, and you are not yet ready to go to the cloud; then Power BI Report Server is probably the right solution for you. The preview release is now available for download on the Power BI website.References
Power BI Report Server Preview Reporting Services Web Portal Modern Life Cycle Policy Author Recent Posts Minette SteynbergMinette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg Latest posts by Minette Steynberg (see all) The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017 Introduction to Azure SQL Data Warehouse - August 29, 2017