SharePoint and Power BI – Better Together

Power BI Power BI Power BI
Ever since 2007, SharePoint has included Business Intelligence amongst its core workloads. There have been a variety of approaches to the workload over the years, but today those core workloads include Excel Services/Excel Online, PerformancePoint, SQL Server Reporting Services Integrated Mode, and Power Pivot for SharePoint.

Power Pivot for SharePoint and Excel Services go hand in hand and can really be considered as one of the main pillars, leaving us with three. If we quickly examine these three pillars in SharePoint 2016, it’s pretty easy to spot an emerging trend. Excel Services is gone from SharePoint 2016, its capabilities being added to Excel Online. Excel Online connects to, but does not run on SharePoint. PerformancePoint still exists in 2016, but it has received precisely 0 new features – it is identical to the version in SharePoint 2013, and remains a part of product for legacy reasons. For all intents and purposes, I consider PerformancePoint to be deprecated. SSRS Integrated mode has been greatly improved in 2016, but contains nowhere near the improvements that the Native Mode version of SSRS has in 2016.

At the same time, the past year has witnessed the spectacular rise of Power BI. Power BI is clearly the focus area for Business Intelligence within Microsoft for cloud based BI delivery. Last fall the SQL team announced that on-premises customers were not being ignored, and that SSRS was the platform for on premises BI delivery They also sketched out a roadmap that showed both platforms being able to deliver the same type of reports. In June 2016, the team delivered on a portion of this vision with SQL Server 2016 Reporting Service.

So where does this leave SharePoint in the Business Intelligence ecosystem?

In my opinion, it leaves it right where it should be – as an integrating platform, and NOT as a runtime platform as it has been in the past. SharePoint provides in context BI by connecting content to reports, and providing dashboards connected to multiple sources. In 2016, SharePoint connects to Excel Online to deliver Analytical reports. Excel runs with SharePoint now, not on it. SSRS Integrated mode still runs on SharePoint, but the investments in Native mode are a clear indication to me that this will be the direction here as well. Unfortunately, Sharepoint has been lacking tight integration with Power BI.

The recent Ignite 2016 conference was the first public appearance of the Power BI web part.

Power BI

Figure 1: Insert web part dialog with Power BI web part

The Power BI web part works with Modern Sharepoint pages and is based on the new SharePoint Framework (SPFx), which means that it is completely client-side. Why does this matter to us? The fact that it is completely client side means that it will work both in SharePoint Online and on premises. Initially, it will only work with SharePoint Online, but that is because the SharePoint Framework is currently unavailable on premises.

To use the new web part, first create or edit a Modern SharePoint page. The Modern pages support the new Modern web parts. Click on a “+” icon to open the insert part control (Figure 1). Once inserted, add the report URL, and the page. The report page should immediately render within the context of the SharePoint page.

Power BI

Figure 2: Power BI Report page rendered within a SharePoint page

Since the web part is rendering client side, the consuming user obviously needs to have access to the report. This means that the source report must have been shared with them through Power BI dashboard sharing, or the report is in a group within which the consuming user is a member. This latter case makes the most sense given that all Office 365 Groups will have a corresponding Modern Team site. Embedding the report within group pages should “just work”.

The devil is of course in the details, and all of these details are not yet available, but Given the number of questions that I have received over the past year about Sharepoint/Power BI integration, I expect that its existence will come as welcome news. Over time I would expect to see it picking up support for parameters and the ability to work with individual report items (this is speculation, but it makes sense). It’s also not much of a stretch to see how SSRS could make available a Modern web part that worked in the same fashion with on premises SSRSs. That web part could conceivably work both on premises an Online, bringing SSRS to SharePoint Online for the first time.

SharePoint is still very much a platform for integration and for Business Intelligence content delivery. SSRS and Power BI will be the de facto reporting engines for on-premises and the cloud respectively, and Sharepoint will be the dashboarding/integrating platform for both environments.

13 thoughts on “SharePoint and Power BI – Better Together

  1. John White Post author

    That hasn’t been announced yet, but given where it’s at, and the fact that modern Sharepoint pages are rolling out now, I would expect to see it by the end of the year.

  2. John White Post author

    @Koen – The biggest improvements have been to the rendering engine – it’s moved to HTML5. It’s had a serious facelift, which extends to Report Builder, there are new chart types, and PowerPoint is a rendering output. Another big one is that printing no longer requires an Active X control…..

  3. Anindya Sankar Chattopadhyay

    Got couple of questions:

    1. This web part only works with embedded power bi URL (from the Power BI tenant) and doesn’t host any direct PBIX file. Am I right?

    2. For on premise, how will the authentication work?

    Cheers!

  4. Jamie

    Great article! Is this available as sample yet for preview / development tenant users? Is something required to activate it? Saw there are samples in GitHub for PowerBI Embedded but assume this is different.

  5. jpw@unlimitedviz.com Post author

    Not yet – the shots and the corresponding demo are taken from a Microsoft demo tenant. It’s not yet ready for prime time!

  6. jpw@unlimitedviz.com Post author

    1. It doesn’t use Power BI embedded, but yes, it works only with the Power BI service (not a PBIX file)
    2. It’s a client side web part, so authentication is handled client side. If the user has access to the report, it’ll get rendered.

  7. jpw@unlimitedviz.com Post author

    Integrated has gotten the facelift, and the HTML 5 rendering engine. That’s pretty significant. And printing no longer requires ActiveX. That’s a start.

  8. Leslie

    Hi John – this is a great article, thank you.

    What are the key differences between leveraging this webpart vs. using the PowerBI REST APIs to embed the report into a SP page (https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-integrate-report/)?

    Do you recommend holding off developing the webparts using the Rest APIs since the release for the native PowerBI web part is imminent? It will seem like wasted effort unless we immediately need it.

  9. John White Post author

    Thanks Leslie!

    In fact, this webpart does leverage those APIs as far as I know. The key difference is that working with a web part is within the capabilities of a Power User, while working with the REST API requires development skills.

    The decision to hold off will be based on the use case. If you’re going to put out something that doesn’t change frequently, and you’re comfortable with REST , go right ahead. Also, if your SharePoint is on-premises, it’s going to be a while before this web part is available.

Leave a Reply

Your email address will not be published.