Share via

Power BI and SSRS 2016 in an On-Premises Environment

  • Article

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.

Power BI On-Prem

 

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.

Power BI On-Prem

 

3. Click on the file to open it on your local instance of Power BI Desktop.

Power BI On Premises

 

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.

Power BI SSRS

 

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 SSRS On-Prem

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!

Thanks,
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/.)

Comments

  • Anonymous
    June 14, 2016
    Can you now run a power bi dashboard from ssrs (all on prem)?
  • Anonymous
    June 14, 2016
    Hi Rob, you can host the .pbix file (dashboard/report) in SSRS as per the screenshots above. When the user clicks on the file, they will be required to open it in Power BI Desktop on their local machine as opposed to it being viewed in the browser. In this regard, you can use Poewr BI completely on-prem with no cloud features required. Also, the product team has stated that this "view in browser" functionality is the future plan, but they haven't released a date for this plan. Thanks,Sam
  • Anonymous
    June 27, 2016
    SSRS should be the unique on-premises hosting solution for all MS reports including SSRS reports, Power BI Desktop reports and PPS dashboards (move PPS from SharePoint to SSRS). Since SSRS can be integrated into SharePoint and so all MS reports can have three hosting options: 1) Native SSRS without SharePoint; 2) SSRS integrated into SharePoint; 3) Power BI Service in Azure Cloud. These three options can cover all kinds of businesses.
    • Anonymous
      July 20, 2016
      Can we avail NLP features through SSRS 2016 uploading a Power BI Desktop .pbix file or even deploying the final SSRS report into SharePoint?
  • Anonymous
    August 13, 2016
    So, for a pc I will need Power BI Desktop installed in order to view a pbix view in a ssrs report. But what about the mobile Power BI? will I be able to see the same on a phone?
    • Anonymous
      September 22, 2016
      yes, the local hosting is what everybody wants, but does not seem to actually have been implemented. Still trying to sell cloud services I assume. It only uploads the file, no actual hosting happens. The user must have PowerBI on the desktop. The reporting services is just acting as a file server then and nothing else. Kind of defeats the purpose of a report server. We are trying to avoid having to install powerbi on everybody's desktop and let the developers do their job with PowerBI first
  • Anonymous
    September 27, 2016
    The comment has been removed
  • Anonymous
    October 13, 2016
    The comment has been removed
  • Anonymous
    November 29, 2016
    Is it possible to have Row level security for individual users in this scenario? That would require a Power BI Pro license i presume?
    • Anonymous
      December 02, 2016
      The comment has been removed
  • Anonymous
    December 05, 2016
    Hi Sam,just adding my voice to the request for a timeframe on the web rendering of PBIX reports... Any news?Thanks in anticipation...Andrew
  • Anonymous
    December 07, 2016
    Hi guys, any news about the possibility of opened in a web pbix in RS without the Power Bi Desktop?
  • Anonymous
    December 16, 2016
    For the latest update from the product team regarding Power BI On-Prem and the technical preview, check out the blog entry here:https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/
  • Anonymous
    February 01, 2017
    Today we tested power bi in ssrs 2016. Itis working fine with Ssas tabular model.
  • Anonymous
    February 24, 2017
    Few Questions for Power BI Desktop with SSRS 2016 for On Premises Hosting : 1. Does this release applicable to all version of SSRS 2016 ? 2. Does this release only need to purchase the license for SSRS 2016 but not need for Power BI Desktop ? 3. Purpose of this release for Power BI Desktop is user self served the reporting but administration & hosting keep in SSRS 2016 ?
  • Anonymous
    March 06, 2017
    The comment has been removed
    • Anonymous
      March 11, 2017
      Great questions, my suggestion would be to post them on the blog for the tech preview so the product team can provide accurate answers:https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/17/power-bi-reports-in-sql-server-reporting-services-january-2017-technical-preview-now-available/