Tag: PBIRS

Business Intelligence in SharePoint 2019

Published August 2, 2018

The recent availability of the SharePoint 2019 public preview, and the supporting information that accompanies it has clarified the status of Business Intelligence features in SharePoint 2019. This release, with one exception, is the culmination of the process of decoupling BI from SharePoint which began in SharePoint 2016 through the removal of Excel Services. This decoupling strategy was initially articulated in the fall of 2015 with the document Microsoft Business Intelligence – our reporting roadmap which stated that SQL Server Reporting Services was to be the cornerstone of their on-premises BI investment (and not SharePoint).

The embedded BI features now run with SharePoint as opposed to on SharePoint. These changes do however require some planning and some effort on behalf of those that have already invested in the current platform and wish to move forward on-premises. With this in mind, and the fact that concise information around these changes is a bit difficult to find, I wanted to put this reference together. This post does not get into migration strategies, only the changes themselves.

The source for much of the below comes from discussions with the relevant product teams, and official information is found (today) in two primary places. The document What’s deprecated or removed from SharePoint Server 2019 Public Preview
which was published concurrently with the SharePoint 2019 public preview, and Christopher Finlan‘s presentation at the Microsoft Business Application Summit 2019 entitled Self-service BI and enterprise reporting on-premises with Power BI Report Server.

A summary of the changes to BI features, and a brief discussion of each is below.

Feature	Status
SQL Server Reporting Services Integrated Mode	Removed
Power View	Removed
BISM file connections	Removed
PerformancePoint	Deprecated
PerformancePoint – Decomposition Trees	Removed
Power Pivot for SharePoint	Removed
Scheduled workbook data refresh	Removed
Workbook as a data source	Removed
PowerPivot management dashboard	Removed
PowerPivot Gallery	Removed

SQL Server Reporting Services Integrated Mode

SSRS Integrated mode was deprecated in November 2016, as was not a part of SQL Server 2017. However, organizations could continue to use SSRS versions from 2016 and prior in SharePoint 2016. This is not supported in SharePoint 2019, which means that integrated mode isn’t an option at all with SharePoint 2019. The good news is that the recent Report Viewer web part fully replicates the capabilities of the SSRS Integrated mode web part.

Power View

Power View was a feature of SSRS Integrated mode and is available in Excel. When Excel Services was removed in 2016, Power View in Excel required SSRS Integrated mode to work. Both supporting platforms are now gone, and thus Power View is not supported in SharePoint 2019.

BISM file connections

The BISM file connection type was used by Excel and SSRS to connect Power View reports to SQL Server Analysis Services data sources. This connection type has been removed along with Power View.

PerformancePoint Services

PerformancePoint is a combination of capabilities that includes dashboarding, scorecards, and analytic reports. Very few new features have been added to PerformancePoint in the last few versions, and this one even loses a few. Many of of these features are also available in Power BI and Power BI report server, and Microsoft has taken the decision to deprecate this product. This gives customers with a PerformancePoint investment time to migrate their assets but is a clear indication that it will also be removed in a subsequent release.

PerformancePoint – Decomposition Trees

The Decomposition Tree feature in PerformancePoint came originally from ProClarity – one of the three products that made up the original PerformancePoint product. These visuals are based on Silverlight, and have been removed from the product accordingly.

PowerPivot for SharePoint

PowerPivot for SharePoint is not supported in SharePoint 2019. PP4SP was originally a combination of two technologies – a specialized version of SQL Server Analysis Services, and a SharePoint service application. In the 2016 version, these two parts were split into two – the SSAS component became a part of the SQL Server installation media as SSQL – PowerPivot mode, and the service application, which continued the name PowerPivot for SharePoint. To be clear, it is the second of the two that has been removed. SSAS PowerPivot mode continues to be an important component and is used by Office Online Server for working with Excel files that have embedded models.

Scheduled workbook data refresh

This feature allowed for the automatic refresh of the data stored within Excel workbooks in SharePoint. It requires a PowerPivot data model to work, but the refresh operation would refresh all connected data in the workbook on a scheduled basis. This was a component of PowerPivot for SharePoint. It has recently been announced that this capability will soon be available in Power BI Report Server.

Workbook as a data source

With PowerPivot for SharePoint deployed, it is possible to use the data model in a published Excel workbook as the data source for another workbook. This feature will no longer be available, and there are no plans at present to reintroduce it.

PowerPivot Management Dashboard

Originally a part of SharePoint Central Administration, the management dashboard provided status updates on all PowerPivot for SharePoint operations. Being a part of PowerPivot for SharePoint, this has been removed accordingly.

PowerPivot Gallery

The PowerPivot Gallery is a modified SharePoint Document library form that displays worksheet thumbnails contained in published Excel workbooks. This component is Silverlight based, and part of PowerPivot for SharePoint. It has been removed accordingly.

Power View, Decomposition trees, and the PowerPivot gallery were the last remaining features that carried a Silverlight dependency. SharePoint 2019 no longer has any Silverlight dependencies.

These changes are significant for anyone with an existing Business Intelligence investment that plans to move to SharePoint 2019. I intent to write more about migration strategies and will be addressing these topics at various conferences in the future.

5 Comments

The Road Ahead for SQL Server Reporting Services and Power BI Report Server

Published March 20, 2018

Power BI has garnered a lot of attention in the past few years as the “place to be” for Microsoft business intelligence. Power BI is of course Microsoft’s cloud platform for dashboarding and reporting. On premises, Microsoft’s reporting platform has traditionally been SQL Server Reporting Services (SSRS) and is now joined by Power BI Reporting Server (PBIRS). While there are differences between the two in the way that they are licensed and distributed, the main technical difference is that PBIRS includes SSRS, plus the ability to render both Power BI reports (pbix) and Excel based reports through Excel Online.

PBIRS is therefore able to render all four report types as defined by Microsoft:

Paginated	RDL (classic SSRS style reports)
Interactive	PBIX (Power BI Desktop)
Mobile	RDLX or PBIX (Datazen and Power BI Desktop)
Analytical	XLSX (Excel)

Given that PBIRS is the on-premises reporting platform, and Power BI is the cloud platform, they should theoretically be able to perform the same task. However, this isn’t the case. Currently, there is no way to render paginated (or as I like to refer to them, operational) reports in the cloud. PBIRS can render all report types, but today, the Power BI Service cannot.

Jason Himmelstein and I recently hosted Riccardo Muti, Microsoft Group Program Manager for Power BI Report Server on our BI Focal podcast (you can hear the whole show here) and we asked him a few questions about the future of paginated reports.

While it has been hinted at before, Riccardo explicitly stated that the ability to render paginated reports in the cloud was not only on the roadmap, but it was actively being worked on. This will complete the picture for BI in both the cloud and on premises. This is important because I the ideal world, the choice of on-prem, cloud, or hybrid should be related to the data in question, not the available features. No more standing up an Azure virtual machine to be able to get printable, paginated structured reports.

Riccardo sees this service rolling out in three distinct phases. Initially for the preview, RDL reports will be able to connect to cloud data sources like Azure SQL Database, Azure Data Warehouse, Azure Analysis Services, and Power BI data models. The next stage will leverage the On Premises Data Gateway in order to connect to on-premises data like SQL Server, Oracle, Teradata and more. The next phase will begin to leverage Power Query to connect to the world of data sources that Power Query Offers.

With paginated reports adopting Power Query, and Excel having moved to Power Query as a default, it is hard to argue with making an investment in learning Power Query. It all seems to be coming together.

One of the top requested features for SSRS on-premises for year has been the ability to authenticate to it with claims-based authentication. This will be the native authentication mechanism for paginated reports in the service. When this is combined with the fact that these reports will ultimately leverage Power Query, and the vast number of data connection options that it brings, it’s not hard to wonder when these features will be coming down to the on-premises product. When asked about this, Riccardo confirmed that this is certainly the vision for the product.

The future looks bright for both cloud based, and on-premises reporting.

3 Comments

SharePoint and the New SSRS/PBIRS Native Mode Report Viewer Web Part

Published September 22, 2017

When SQL Server 2016 was released, I wrote a post that covered how to integrate SharePoint 2016 with SQL Server Reporting Services (SSRS) in native mode. I wrote that post because although SSRS was still available in SharePoint Integrated Mode, many of the new features were only in Native mode. It also wasn’t difficult to surmise that Integrated mode had a limited lifespan at that point, a fact that was confirmed in November 2016.

As my integration article pointed out, one of the glaring problems integrating Native mode with SharePoint was that the Native Mode web part had significantly less capability than the one that was available with Integrated mode, not to mention the fact that it was deprecated. Parameter support was first and foremost among those missing features. Organizations that had incorporated SSRS reports into their SharePoint based dashboards couldn’t just move to Native Mode, and those dashboards relied heavily on the ability to pass parameter values to the SSRS reports through the web part. SSRS did make it easier to embed reports into pages without a web part through the rs:Embed URL directive, but there was no real way to make that dynamic.

The introduction of Power BI Report Server (PBIRS), and its ability to render Power BI reports also left the SharePoint dashboard folks without a solution. Although PBIRS is a superset of SSRS, it is only available in Native Mode, in fact, there’s no longer any need to mention modes. SharePoint dashboards that used SSRS report components were stuck at the 2016 version.

Until now.

Today, Microsoft unveiled the new, fully capable SSRS/PBIRS Report Viewer web part for SharePoint.

Integrated Mode	Native Mode

“Meet the new boss, same as the old boss” – Pete Townshend

The web part allows paginated reports from either SSRS in Native mode, or Power BI Report Server to be embedded into a SharePoint page. Essentially, it brings all the capabilities of the integrated mode web part to the native mode web part. Organizations that have bet on SSRS running in Integrated mode can now seamlessly move their reports to SSRS Native mode, or to Power BI Report Server. Let’s have a quick look at some of the details.

Parameter and view support

All the options that we’ve come to expect from the integrated mode web part are here in the new web part. Toolbar options can be controlled at a fine level and web parts can be connected to pass in parameter values. In fact, as seen above, the only difference between the two modes now is the report location option.

C2WTS and Kerberos required… unless

With Integrated mode, Kerberos constrained delegation (KCD) was only required if you needed the user’s identity passed back to the data source. This was because Integrated mode and SharePoint itself shared the same access mechanism. Native mode and PBIRS have their own membership providers, and this new (server side) web part needs to connect to the report server itself to authenticate the user. This is an additional “hop” and therefore requires Kerberos constrained delegation. This only gets the user as far as the report server itself. To get the user’s identity further downstream, KCD will also need to be set up between the report server and data source(s).

SharePoint 2016 in most cases uses claims authentication, and SSRS/PBIRS does not. This also means that the Claims to Windows Token Service (C2WTS) must also be running in your SharePoint environment. This allows the user’s NTLM identity to be extracted and sent via KCD to the SSRS/PBIRS server.

There is one case where KCD is not required, and that is when SSRS is running on a machine in the SharePoint farm along with C2WTS. In a small environment, this may be suitable, but if scaling is a factor, you may need to crack open your Kerberos books.

Paginated reports only

Even though SSRS supports the new RSMobile report format, and PBIRS also now supports both Interactive (Power BI) and analytical (Excel) reports, this web part is only built to render paginated reports. Up until 2016, paginated was the only report type supported by SSRS, so this will not be a problem for those looking to move forward. However, for those people that are looking to embed these other formats in SharePoint on premises, there are other options.

Both Power BI and mobile reports can be embedded into a SharePoint page using the script editor web part alongside the rs:Embed=true URL directive. Parameters can be passed to mobile reports using this technique, and text filters can be passed to interactive reports as well. Excel reports can be embedded with the Excel Services web part, the way that they always have which itself allows for passing slicer values. However, for this to work, the Excel files need to be stored in SharePoint, not PBIRS.

On-premises only

The new web part deploys to the SharePoint farm as a WSP solution file, which means that this is an on-premises solution only. Given that SSRS/PBIRS are also on-premises products, this shouldn’t pose much of a problem. It IS possible to ebbed on prem reports into SharePoint Online pages with the script editor web part for classic pages, and the embed web part for modern pages. A complete summary of report/SharePoint edition embed options can be seen below.

Report type	SharePoint Online modern page	SharePoint Online classic page	SharePoint on-premises
PBIX on Power BI Service	Power BI WP	Anonymous*	Anonymous*
PBIX on PBIRS	Modern embed content WP	Script editor WP	Script editor WP
RDL on PBIRS or SSRS Native	Modern embed content WP	Script editor WP	Report Viewer WP ^NEW!
RDL on SSRS Integrated mode	Modern embed content WP	Script editor WP**	SSRS WP
XLSX (stored in SharePoint)	N/A	Excel Services WP	Excel Services WP
RSMobile on SSRS/PBIRS (Native)	Modern embed content WP	Script editor WP**	Script editor WP

* NOT recommended

** Requires adding sever to HTML Field Security options in site collection settings

In summary, the new native mode web part will be very welcome for those organizations that have been feeling stranded by the deprecation of SharePoint Integrated mode. The very fact that it is now available also underscores that SharePoint still plays an important role in the Microsoft Business Intelligence ecosystem. It may no longer be a prerequisite, but it still adds value, and those are both very good things.

10 Comments

SQL Server Reporting Services vs Power BI Report Server – What’s the Difference?

Published September 18, 2017

Power BI Report Server (PBIRS) was first introduced in May 2017. Based on SQL Server Reporting Services (SSRS), it brings the ability to work with Power BI reports completely on premises in addition to all the other capabilities of SSRS. Given this, it would be reasonable to conclude that PBIRS was the next version of, or a replacement for SSRS, but that is not the case. I have heard people state that SSRS is “going away”, but this is simply not the case. SSRS is still a core part of the Microsoft BI stack. So, what are the differences between the two platforms? The differences boil down to features, licensing, and update cadence.

Features

Early builds of SSRS 2017 (V.Next at the time) contained the ability to render Power BI (Interactive) reports in addition to the “classic” RDL (Paginated) reports that SSRS is well known for and the recently added RSMOBILE (Mobile) report types. However, when PBIRS was introduced, SSRS lost that capability, and from a feature standpoint, it really was the only difference between the two. The recent introduction of the Excel report type (Analytical) to PBIRS has further differentiated the two products.

From a features standpoint, the differences between the two products are straightforward. PBIRS is a superset of SSRS. It contains everything that SSRS has, and it ads the ability to render both Interactive (PBIX) and Analytical (XLSX) reports.

Licensing

Licensing is where things get a little more involved. SSRS was always included on the SQL Server installation media, but with SQL Server 2017, this is no longer the case, it’s a separate download (the RC version of SSRS 2017 is currently available for download here). However, the license for SSRS is still tied to your version of SQL Server. Therefore, if you have a license for Standard mode SQL Server, you will be able to use the Standard mode features of SSRS, Enterprise unlocks the Enterprise features, etc. As of the 2017 version, there is also no longer an Integrated mode of SSRS, it’s Native Mode only.

Power BI Report Server is licensed in one of two ways. Purchasing Power BI Premium capacity gives you a license to run the same number of cores as you have in the capacity. This ONLY applies to Premium P SKUs, not any others such as EM. The other way that it can be licensed is by purchasing SQL Server Enterprise Edition + Software Assurance.

Release cadence

Just as with licensing, the timing of releases of SSRS is also tied to that of SQL Server. Whenever a new version of SQL Server is released, a new version of SSRS will be as well. This is not the case for PBIRS. Since PBIRS is considered a standalone product this makes sense, and the constant pace of change in the Power BI service itself necessitates a more frequent update cadence.

As an example, PBIRS first came into General Availability (GA) in June 2017, and as of this writing (Sept 2017) is already in preview for its next release, whereas SSRS 2017 hasn’t yet gone to GA.

How to choose

The choice between which platform to use will likely be straightforward and likely driven by requirements. If your organization only uses paginated reports on premises, you may find that SSRS is a more cost-effective option. If, on the other hand you have the need to render interactive or analytical reports on premises, or you already have SQL Server Enterprise Edition with Software Assurance, then PBIRS will likely be your best choice. There are no circumstances that I can think of where both products will be advisable, if you have PBIRS, you have everything that SSRS offers and more.

15 Comments

Power BI Report Server Completes the Vision for On-Premises Reporting

Published August 26, 2017

Microsoft today made available the August 2017 preview of Power BI ReportServer 2017. This preview includes the long awaited support of embedded data models, as well as the ability to render Excel reports natively. This is a major step forward, because with this release, Microsoft has completed its vision for its on-premises reporting platform that it first articulated in October of 2015.

Excel content being rendered in Power BI Reporting Server

The big news at the time was that the platform was stated to be SQL Server Reporting Services (SSRS). Not SharePoint, not PerformancePoint, but SSRS. SSRS was a mature product that quite competently provided a platform for operational reports. What it needed was some modernization and the addition of some analytical and self-service reporting capabilities. Several of these capabilities were subsequently included with the release of SSRS 2016.

Gone would be the days of configuring complex SharePoint farms just to be able to work with analytical reports (ie Power View, Excel). New Features were being added to SSRS to make it a complete platform for both analytical and operational reports.

The Vision

The roadmap articulated four different report types, 3 of them analytical (by my definition) and one of them operational. These three types line up with reporting tools in the Microsoft BI stack:

Name	Type	Primary authoring tool	ext
Paginated	Operational	SSRS Report Builder SQL Server Data Tools	.RDL
Interactive	Analytical	Power BI Desktop	.PBIX
Mobile	Analytical	Mobile Report Designer	.RDLX
Analytical	Analytical	Excel	.XLSX

Therefore, reading between the lines, in order to be a complete reporting platform, SSRS needed to be able to render all of these report types. Paginated reports were of course always native to SSRS, and the roadmap announced that Mobile reports would be included in SSRS 2016 through the integration of Datazen. The roadmap further committed to SSRS being able to render Power BI files in the future.

SSRS 2016

SSRS shipped with some significant modernization improvements, including a much awaited HTML5 rendering engine, and it included Mobile Reports. Mobile reports are delivered through the Power BI mobile application, and SSRS visuals can be pinned to Power BI dashboards.

Significant plumbing was done to move the platform forward in 2016, but it still only rendered 2 of the 4 report types.

In November 2016, it was further announced that the 2016 version of SSRS running in SharePoint Integrated mode would be the last. Moving forward, Reporting Services will only run in Native Mode. In the same announcement. In the same announcement, as I noted in another post, for the first time, the SSRS team committed to providing Excel report rendering capability as well.

Power BI Reporting Server

We first saw the on premises rendering of Power BI reports in the first community preview of SSRS V.Next in the fall of 2016. Those previews required that the reports be directly connected to SSAS tabular models, but they were ground-breaking just the same. A user could be totally disconnected from the web, and still render Power BI reports.

In May of 2017, Power BI Report Server (PBIRS) was announced. A less confusing name could have potentially been SSRS Premium, because that is in essence what it is. PBIRS is everything that SSRS is, plus the ability to render Power BI reports. SSRS will continue forward as a product without Power BI rendering capabilities. It is just a licensing distinction.

The release today of the August 2017 preview of PBIRS allows for embedded data models, and therefore a much wider breadth of capabilities. These models cannot be automatically refreshed yet, but they will upon release. This is, after all, just a preview. If you need automatic refresh of these data models in the meantime, there is an excellent third party solution to do this: PowerPivot Pro’s Power Update.

The inclusion of Excel report rendering capabilities means that PBIRS is a complete report rendering platform, more complete even that the Power BI cloud service.

Moving Forward

Now that the basic on-premises capability has been provided, SSRS/PBIRS needs to pay attention to paying back the debt that it incurred when SharePoint Integrated mode was deprecated. Chief among these features is the SSRS web part. The lack of a decent web part is a blocker for many organizations to move forward with this strategy. Some migration tools to move from Integrated to Native mode (like this one that migrates in the other direction) would be highly useful as well.

Now with on-premises covering all the bases, it’s easy to spot a glaring hole in the cloud Power BI offering. While it supports all three types of analytical reports, there is currently no way to render operational reports in the cloud. Until this capability is provided, it appears that on-premises will have the most complete solution.

2 Comments