Power BI & SSRS 2016 in an On-Premises Environment


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 (17)

  1. Rob Connock says:

    Can you now run a power bi dashboard from ssrs (all on prem)?

  2. 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

  3. Yuhu Che says:

    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.

    1. SOUMEN DATTA says:

      Can we avail NLP features through SSRS 2016 uploading a Power BI Desktop .pbix file or even deploying the final SSRS report into SharePoint?

  4. Mark says:

    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?

    1. Kenneth Wilson says:

      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

  5. Iulian says:

    All this is useless without two key features we don't know when or if they will be implemented: Web rendering of the PBIX and scheduled refresh. Also, very important is the ability to create PBIX report parts and dashboards within the desktop editor similar to Tableau Desktop/Tableau Server. It doesn't seem to be such a dificult to achieve design and it would definitely kill off any competition.

  6. Great to know Power BI components could be used as part of SSRS ,on prem, in future. Do you have any indicative dates on when this will be available (if it is part of SP1)

  7. Andreas Bergman says:

    Is it possible to have Row level security for individual users in this scenario? That would require a Power BI Pro license i presume?

    1. Aman says:

      Do we know when is the web rendering feature of Power BI components in SSRS be available?

  8. Andrew says:

    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

  9. Javier says:

    Hi guys, any news about the possibility of opened in a web pbix in RS without the Power Bi Desktop?

  10. 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/

  11. Benny MATHEW says:

    Today we tested power bi in ssrs 2016. Itis working fine with Ssas tabular model.

  12. Andykm.Wong says:

    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 ?

  13. andykm.wong says:

    Hi Administrator,

    With release in mid Jan 2017 to allow Power BI reports publish to SQL Server SSRS, we have some technical questions below :

    https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/17/power-bi-reports-in-sql-server-reporting-services-january-2017-technical-preview-now-available/

    1. Which SSRS version would allow features for this release i.e. allow .pbix files store and administer in SSRS ?

    2. With this release there is a requirement for installation below, does it means SSRS installation also need to install SQL Server Analysis services ? What is the use of this component ? Understand Database engine is for report and data storage.

    Your own VM or server (not one in production use)
    Windows Server 2012 or later (or Windows 8 or later)
    .NET Framework 4.5.2 or later
    SQL Server Database Engine (2008 or later), to store the report server database
    SQL Server Analysis Services (2012 SP1 CU4 or later), to store your data models
    3. SSRS Enterprise version has a mobile report (.rsmobile) that could be support in mobile device, then what is the architecture for this feature ? More specifically, assume SSRS is hosted in company on-premises network, then how to authenticate through mobile to company network ?

    https://msdn.microsoft.com/en-us/library/mt742500.aspx?f=255&MSPPError=-2147217396

    4. Do user need to install any mobile apps or just use mobile browser in order to view .rsmobile report ?

    Thanks

    Andy Wong

    1. 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/

Skip to main content