Power BI and SQL Server Reporting Services 2016 in an entirely On-Premises Environment
Jan. 17, 2017 - Another Update! The January release is a huge step toward realizing Power BI in an entirely On-Premises environment. In this technical preview, we see a few big improvements:
- Publishing Power BI reports directly to the SSRS Portal - With this glorious improvement, we will be able to easily share Power BI reports on-premises. In this scenario, the report developer will create the report in Power BI, save the .pbix file locally, and then upload it to the SSRS portal. At this point, the end user can click on the Power BI report link in the SSRS portal and see it rendered directly in the portal in their browser. No more downloading/opening in Power BI on the client side. This is the step my customers have been waiting for!
- Standalone installer for SSRS (apart from the full SQL Server setup experience) - This is a great improvement and makes setup/configuration much simpler.
- SQL Server Analysis Server (SSAS) models (tabular and multi-dimensional) are still the only data source available to use in the version of Power BI in the tech preview.
- With this technical preview, you can download a configured VM in Azure or you can download the components and run it on your own on-premises VM or server.
Dec. 16, 2016 - Update: The SSRS product team released an update on Power BI reports in SQL Server Reporting Server. The article is here and outlines the following:
- We’re targeting January 2017 to release our next Technical Preview, which you’ll be able to download and install on your own VM or server.
- We plan to release a production-ready version in the next SQL Server release wave. (As previously clarified, we won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.)
- We’re targeting availability in mid-2017 for the production-ready version.
Original article (published April 2016)
In my current role as a SQL Server and BI Premier Field Engineer, I support customers that are unable to move to Azure-based Power BI in the near term, so all of my current BI deliverables have a strict requirement to be wholly on-prem. With the release of SQL Server 2016 RTM, I spent some time exploring SSRS and Power BI I features that I can leverage in my on-premises customer environments within RTM and additionally with the features set to be released post-RTM based on public comments from the SSRS and Power BI product teams. Here is what I discovered.
Power BI Desktop support in SSRS 2016
The big change I noticed is that SQL Server Reporting Services (SSRS) 2016 now recognizes the .pbix file extension created from Power BI Desktop. With this addition, we can now upload .pbix files into SSRS 2016 to leverage the security model that we’re familiar with in SSRS, controlling permissions to view, update, and delete the reports. This also allows customers who are already using SSRS to begin to integrate Power BI reports into the same reporting structure.
Let’s take a look at the workflow:
1. Upload an existing .pbix report to SSRS 2016 using the Upload button – After uploading a .pbix file into SSRS, notice the Power BI Desktop icon associated with the file and the new category titled "Power BI Desktop Reports" to help organize different file types.
2. Click on the ellipses (...) to show the operations supported for the .pbix files, including "Add to Favorites". After adding to favorites, you'll see the star icon below in the screenshot below in step #3.
3. Click on the file to open it on your local instance of Power BI Desktop.
4. Note that clicking on the file in Step #3 opens it from a location on the local machine. As a result, by making changes and saving the file locally, the changes are not reflected on the SSRS server by default. In order to get your changes uploaded, you need to manually replace the file back on the SSRS instance. To do so, click on the ellipses, then "Manage". On the next screen (shown below), click on the "Replace" button, supply an updated name, and click "Apply" to upload the updated .pbix report.
5. And finally, let’s look at the security model available for.pbix files. To view security details, click on the ellipses, then "Manage", and then on the "Security" tab to show the existing permissions on the file as well as options to add/edit security permissions as seen below.
Power BI Desktop support in future SSRS (Post SSRS 2016 RTM)
See the article linked at the top of this blog entry from the SQL Server product team with future plans announced.
This is where the on-prem story gets great. The top two requested features in Power BI User Voice are on-premises hosting and PowerPoint integration. The Power BI product group has publicly stated that their top priority after RTM is to implement the ability to publish Power BI Desktop reports to on-premises SSRS, allowing the report to be viewed in a browser. Once this functionality is released, we will be able to proceed with a full on-premises workflow that will allow for easier and broader consumption of Power BI reports, ideally where they are integrated into SSRS and able to be opened in a web browser to eliminate the need to the report viewer to run Power BI Desktop on their machine. I am eagerly awaiting this functionality and much more as the impact, usability, and value of Power BI and SSRS at that point will be amazing for on-premises customer environments!
Sam Lester (MSFT)
(Disclaimer – This is not an official roadmap of Power BI and SSRS features, but instead an exploration of available features that can be used in on-premises environments with Power BI Desktop and SQL Server 2016 along with my interpretation post-RTM based on the product group’s official statements of future plans. The official roadmap is located at: https://blogs.technet.microsoft.com/dataplatforminsider/2015/10/29/microsoft-business-intelligence-our-reporting-roadmap/.)