Tag: Business Intelligence

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.

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.

Last November, I was interviewed by Sr. Technical Evangelist John Weston on the MVP program, Office 365, Cloud Computing, Business Intelligence, and how these things all tie together. The entire interview was conducted online using Lync online, available in Office365. It’s now online, and can be seen below.

While the blog has been quieter than usual for the past couple of months, owing to a hectic schedule, I do have a number of speaking engagements coming up that I wanted to promote. All of them are related to SharePoint Business Intelligence in one way or another, and all are in the Toronto/South Western Ontario area. If you’d like to come out and talk SharePoint, Business Intelligence, or just indulge in a SharePint or two, I would love to see you there.

Hamilton SharePoint User Group

Thursday, Feb 16 2012

SQL Server Reporting Services with SharePointSQL Server Reporting Services provides a rich reporting environment, and integration with SharePoint makes it seem seamless to end users. How does Reporting Services impact the SharePoint environment? This presentation will walk through the basic features of Reporting Services, and architectural considerations when installing in a SharePoint farm. In addition, some of the differences included in the upcoming SQL Server 2012 version of Reporting Services will be discussed.

Toronto SharePoint User Group

Wednesday, April 18, 2012

What’s new in SQL Server 2012 for SharePointSQL Server 2012 brings a wealth of new features to the core database used by SharePoint. However, it also brings a number of exciting new Business Intelligence features right to your SharePoint users.

This session will walk through a number of the new features that have a direct impact on SharePoint administrators, designers and end users, with in depth demonstrations of how to configure and use them. These features include significant architectural changes to Reporting services, the new BISM or tabular engine for Analysis Services and PowerPivot, and the new end user focused reporting tool, PowerView.

Reporting Services and SharePoint have been working together since SharePoint 2003. SQL Server 2005 SP1 brought the ability to use Reporting Services in Integrated Mode through a SharePoint Add in, and with SQL Server 2012, Reporting Services is a fully fledged SharePoint Service Application, and some features, the new PowerView in particular, are only available through SharePoint Integrated Mode.

Reporting Services bring a wealth of benefits to your SharePoint farm, but can also have a significant impact on it. This session will discuss the do’s and don’ts for a successful Reporting Services implementation. It will cover architectural considerations through to Report design, for both the Reporting Services Add In (SQL Server 2005-2008) and the new Reporting Services Service Application (SQL Server 2012).

Yesterday, as part of the ongoing Panellist Spotlight series for SharePoint Shoptalk, I presented a session on how to move your data from SharePoint, and in to a data warehouse, and then consume the warehoused data directly within SharePoint. These presentations are recorded, and posted online, and if you’re interested, you can view the entire presentation below:

In it, I demonstrate how SQL Server Integration Services (SSIS) can be used to extract the data from SharePoint, and to store it in a SQL Server data warehouse. Then I walk through the creation of an external content type, and an external list using SharePoint Designer and SharePoint Business Connectivity Services (BCS). Finally, I create a report using SQL Server Reporting Services (SSRS) in SharePoint Integrated mode.

I’m happy to announce that a new UnlimitedViz blog went live today, The Data Queen. This blog is authored by Martina White who has over 10 years experience working with Business Intelligence products, originally Cognos, but for the past 6 years has been working exclusively with the Microsoft BI stack of products.

Martina is a cube designer extraordinaire and can form data into all kinds of informative shapes, hence the nickname. We’ve convinced her to start putting her techniques out in public, and this blog is the result. She’s already posted a couple of interesting articles, and there will be many more to come.

Have a look when you get a chance – it’s at http://dataqueen.unlimitedviz.com.