Tag Archives: Power View

Business Intelligence in SharePoint 2019

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.

Power BI Desktop Excel Connections

Using Excel Files with Power BI Desktop and SharePoint

As discussed in a previous post, Working with Excel Files in the Power BI Service, Excel and Power BI have a rich, complex relationship. Power BI Desktop is the primary design tool for Power BI, and it has many feature overlaps with Excel as an analytic tool. Excel can be used both as an analytic tool and a data source, and the structure of the Excel file will dictate the way that Power BI Desktop can be used with it. If Excel is being used as an analytic tool (i.e. connected to data), the appropriate items in the file can be imported into Power BI Desktop. If it is being used as a data source (data in worksheets), Power BI Desktop will connect to it, and use its data to build a model. This post attempts to articulate the nuances of both scenarios.

Importing from Excel

Power BI Desktop has an unfortunate name in my opinion. It is a design tool and is not meant to replicate the capabilities of the Power BI service on the desktop, as the name might suggest. A better name for the product would I believe be Power BI Designer. Its purpose is to connect to and transform data (Power Query), build data models for Analysis (Power Pivot) and build reports (report designer).

Used as an analysis tool, Excel has all these capabilities as well. In fact, the first two (Power Query and Power Pivot) are identical to what is already in Power BI Desktop. Excel also has Power View for analytic reporting. Power View is very similar to the type of reporting in Power BI Desktop, but uses a different technology and has been deprecated for some time. As a result, Excel charts and pivot tables are the primary means of visualizing data in Excel.

So why would you need to use Power BI Desktop if you are using Excel? As explained in my previous post, the Power BI service can fully interact with Excel as an analysis tool, and allows you to interact with Excel right from the Power BI Service. If Excel is meeting all your analytics needs, then there may be no need to introduce Power BI Desktop at all. However, if you wish to take advantage of Power BI’s analytic reporting capabilities, and you have existing Excel assets, you may wish to convert them to the native Power BI format.  Whatever the reason, moving from Excel to Power BI is relatively straightforward with Power BI Desktop.

From the File menu in Power BI Desktop, select Import, and then Excel Workbook Contents.

Importing Excel Files contents

You are then prompted to select an Excel file. Once selected, you are then presented with a warning dialog.

Excel files contents warning dialog

The dialog does a very good job of explaining what will happen, specifically the fact that data from workbooks will not be brought into this new file. Any Power Pivot data models or Power Queries will be brought in. If the workbook contains legacy Power View sheets, they will be converted to native Power BI visuals. In addition, any legacy (non-Power Query) data connections used by the source file’s Power Pivot data model will be converted to Power Query and imported.

Imported Power View sheet

Legacy Power View Sheet converted to Power BI visuals

A complete list of workbook content and what is/isn’t converted is below:

Excel Content Import to PBI Desktop Support
Data in sheets Not imported
Data model (Power Pivot) Imported
Data connections Converted to Power Query and imported
Power Queries Imported
Power View Sheets Converted to PBI visuals and imported
Pivot charts/tables Not imported
Excel charts Not imported
Macros Not imported

Once imported, the new Power BI file (PBIX) lives on its own and contains no connection or any other type of relationship to the original source Excel file. If the source Excel file is changed, there is no way to update the PBIX file. Any imported data connections are between the PBIX file and the original data source. The new PBIX file can be published to the Power BI service like any other.

Connecting to Excel

Connecting to Excel as a data source is a very different thing than importing from it. In this scenario, the data in the worksheets and only the data in the worksheets is brought into the data model. The is very different behaviour than that of connecting to Excel files to the Power BI Service, where both the model and the worksheet data is brought in.

Using the Excel Connector

The easiest, and most obvious way to connect to Excel worksheet data is by using the Excel connector. From the ribbon in Power BI Desktop, select Get Data. The Excel connector is right at the top of the list.

Connecting to Excel files on the file system

Selecting it allows you select your source file, and then the workbooks within it, and then build out the data model.

This approach works well but carries with it an important limitation. The new queries are  connected to the file using a local file system. This means that to be refreshed, an on-premises data gateway is required. In order to eliminate the gateway requirement, you can connect to the file in SharePoint using the SharePoint folder connector.

Using the SharePoint Folder Connector

The SharePoint Folder connects to all the files stored in libraries of a SharePoint site. It allows you to report on file metadata, but it also allows you to drill into file contents.

From Power BI Desktop select Get Data but instead of selecting Excel, Search for SharePoint and select SharePoint folder.

Using the SharePoint folder connector

Once selected, enter the URL of the SharePoint site (NOT the URL of a library or folder) in the dialog box.

Next, you will be presented with a preview of all the files in your site. Unless you are only interested in file metadata, click on the Edit button to bring up the Power Query editor.

The initial view will contain all the files in the site, but we are interested in the content of just one of those files. Every file in this view will contain the hyperlinked value “Binary” in the Content column. Clicking that link for the file that you want to connect to will drill down into the contents.

Site contents using the SharePoint folder connector

From this level, you can build your Power Query, data model, and report as needed just as if you had used the Excel connector. The difference is that now when you publish your report to Power BI, it will know the file is stored in SharePoint and will connect directly to it. It will not require a gateway for refresh purposes. Once credentials are registered, the report will refresh itself directly from the workbook stored in SharePoint.

XLS vs XLSX

A note of caution. The above SharePoint folder approach only works for XLSX files. The Power BI Desktop and the Power BI service both support both Excel file formats (XLS and XLSX). However, refresh does not. If the source file format is XLS, and a refresh is attempted, you will receive the classic “microsoft.ace.oledb.12.0 provider” error in the Power BI service.

Excel files refresh error with XLS file types

The older Excel file format (XLS) requires an Access driver to refresh, which is not a part of the Power BI service. The newer XLSX file does not require this driver. As a result, if the source file is XLS, refreshing it requires going through an On-Premises Data gateway, and that gateway machine must also have the ACE components installed.

To recap, you can bring Excel assets into Power BI Desktop by using the import function, and you can load data from Excel files through Power Query. The two operations have very different results, and the can be combined if a source workbook contains both analyses and data.

Configuring SSRS 2016 Integrated Mode with SharePoint 2016

SQL Server Reporting Services (SSRS) has experienced some very significant improvements in the 2016 version. As has been the case Since SQL Server 2005 SP1, it runs in either Native, or SharePoint Integrated mode. Integrated mode (the subject of this article) requires SharePoint 2016, and it is required for SharePoint to be able to render Power View reports in a browser.  This article walks through the setup and configuration of SSRS 2016 Integrated mode in a SharePoint 2016 farm.

The process for setting up SSRS in Integrated mode is little changed with 2016. The process consists of installing the bits on the SharePoint server(s), creating and configuring the service applications, deploying the solution, and configuring document libraries to contain report elements.

Installing SSRS 2016 on Sharepoint Servers

When running in integrated mode, SSRS MUST be installed on a server that is part of the SharePoint farm. This only makes sense because it is deployed as a SharePoint Service Application. Unfortunately, the fact that is distributed as part of the SQL Server media causes confusion for some.

As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not support any other role than the Custom role. If your server is not running the Custom role, installation will succeed, but SSRS will be shut down by the roles engine during the next maintenance window. In order to check which role your server is using, and to possibly change it, you can use either PowerShell or Central Admin. With Central Admin, the setting is found in “System Settings”, under the “Servers” category as “Convert server role in this farm”.

Selecting this option opens the role configuration dialog, which is quite simple.

If the role is already set to Custom, you are good to go. Otherwise, it can be changed with the “New Role” drop down dialog.

Once the correct role is in place, SSRS can be installed. The first step is to mount the SQL Server media on a SharePoint server, and run the standard SQL Server installer. SSRS Integrated mode is part of the Shared Features collection (ie no SQL instances are installed), and it consists of two parts.

The first option, “Reporting Services – SharePoint” is the actual SSRS Service application. This should be installed on any SharePoint servers allocated to doing the heavy lifting of rendering reports – the “app” servers. The second option “Reporting Services Add-in …” is used to connect a SharePoint server to an instance of the SSRS Service application. This should be installed on any SharePoint front-end servers at a minimum, but I recommend installing it on all of them as a convenience.

After a few “Next”s and “OK”s, the SSRS bits should be installed on a server. The next step is to Create and configure the Shared Service Application itself.

Creating the SSRS Shared Service Application

Once the bits are installed, an SSRS Service application is created in the same manner as any other service application. From the Service Applications interface in Central Administration, select “New” from the ribbon, and then select “SQL Server Reporting Services Service Application”.

You will then be presented with a configuration dialog where you will need to specify a name for the service and a few other configuration parameters.

I typically use the same application pool as most of the other SharePoint services, and I always change the name of the database. The default database name contains a GUID, and nobody likes GUIDs in their database names. The SSRS will actually create 3 databases, one with the name specified, and two others that use this name as a base. Also, if you’ll be using other Reporting Services databases on the same SQL Server – for Native mode as an example, it’s a good idea to name it so that it’s easily distinguishable. In this example “Integrated” is added to the end.

Scrolling down, you’ll see options for activating the SSRS features in all of the farm’s site collections. The features can be activated from the site collections as well; this is simply a convenience.

Once saved, additional SSRS configuration items can be configured, and should be. At the very least, the subscription options should be configured, and the encryption key should be backed up, but these operations are not essential for basic setup, so they will not be done here. The next operation will be to enable a document library for SSRS reports.

Creating a Reporting Library

Enabling a document library in SharePoint for SSRS reports is unchanged from the past several versions. The first step is to add a new document library by going into “All Content” for a site, and selecting “Add an App”. You may be tempted to select “Reports” or “Report Document Library” at this point – don’t. The “Reports” library template that ships with SharePoint 2016 and prior contains content types for creating Excel documents in prior versions, web pages – that’s it. It has nothing to do with SSRS reports.

Select a Simple document library, give it a name (something like SSRS Reports, or SSRS library), and let it be created. Then, go into the library settings, click Advanced settings, and enable the use of content types. Next, add the SSRS content types to the library by clicking “Add from existing site content types”, selecting the “SQL Server Reporting Services Content Types” category, and then selecting “Data Connections” and “SSRS Report”. Unless you have a specific need, do not add the “Report Builder Model” content type. Models are a deprecated artifact and exist only for backward compatibility.

Once added, click OK, and you will be returned to library settings. At this point I like to remove the “Documents” content type from the list to restrict it to reports, but that will depend on your requirements. At this point you should be able to create a new report or data source by selecting new in the library’s ribbon and choosing the appropriate item. This library can now be used to store reports.

The final step is to enable and confirm support for Power View.

Power View Support

Power View support in SharePoint 2016 is provided through SSRS Integrated mode (and ONLY through SSRS Integrated mode). It is manifested in 3 different areas:

  1. Creating and viewing a standalone Power View report from a data connection
  2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery
  3. Using a browser to view a Power View report contained in an Excel workbook

1. Creating and viewing a standalone Power View report from a data connection

Standalone Power View reports utilize BISM (BI Semantic Model) connections. BISM connections can be added to a SharePoint library by adding the “BI Semantic Model Connection” content type to the library – this would normally be done for a connections library. A BISM connection can also be created through an SSRS data source by selecting “Microsoft BI Semantic Model for Power View” as its data source type.

Creating a Power View report from either connection type follows the same process. In the library, click the ellipsis for the connection, and then the second ellipsis. From there, select “Create Power View Report”

Provided that Silverlight is available on the client, Power View should launch, and you should be able to build a report on the underlying data.

2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery

Creating a Power View report is significantly simpler. Once SSRS is installed, it adds a small Power View icon to every workbook that is in a Power Pivot gallery.

Simply click on the icon, Power View will launch, and you can build a report on the data model that is contained in the workbook. There is however one additional step necessary for this to work. Because the data model is actually stored in the SSAS PowerPivot mode server(s), and it is SSRS (remember, Power View is part of SSRS) that is working with the model (not OOS), the service account for SSRS needs to be added to the Administrators list on the SSAS PP mode server(s). In our case, the service account is NAUTILUS\spServices.

3. Using a browser to view a Power View report contained in an Excel workbook

Power View reports that have been embedded in an Excel workbook require no additional configuration, they should “just work” once SSRS is configured. However, as with the PowerPivot gallery, SSRS needs access to the data models, and therefor its service account needs to be in the administrators list (see above).

Wrapping Up

Once installed and configured, you will have access to the new HTML5 based rendering engine and new visuals available to SSRS 2016. You will also be ale to work with your existing Power View investments. However, you will not be able to use the new mobile reports, Reporting Dashboards, Parameters customization, and Power BI integration. For that, you’ll need a Native mode SSRS instance, and yes, it can be connected to SharePoint. That will be the topic of an upcoming article.

Rethinking Business Intelligence in SharePoint and SQL Server 2016

SharePoint 2016 and SQL Server 2016 will both be released in 2016, adding to the changing Business Intelligence landscape already being disrupted by Power BI. Many of them will be incremental, but some are significant architectural changes that require a rethink of how we will approach on premises and cloud based Business Intelligence.

All of the bits to deploy the SharePoint based BI components are now available. With the December 8, 2015 publication of the white paper “Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016” white paper, it’s possible to kick the tires and to come to a few conclusions. I will be posting a number of “how to” posts in the coming weeks, but I felt that it was important to set the context for them first. The Business Intelligence ground has shifted significantly, and this greatly affects the way that we think of, design and use Business Intelligence tools with SharePoint. For the record, there is quite a bit of opinion in here, and I want to make it crystal clear that the opinion is mine, and not stated by Microsoft.

Excel and Excel Services

In August 2015, Microsoft announced that Excel Services would not be a part of SharePoint in 2016, which came as a big shock to the community. Excel has always been one of the main pillars of BI in SharePoint, the other two being SQL Server Reporting Services (SSRS) in SharePoint integrated mode, which we’ll get to below, and PerformancePoint. As I’ve argued before, PerformancePoint, while still included in SharePoint 2016, has been dormant for several versions, and likely doesn’t have much of a future. It’s been exposed to the elements, and has gotten quite rusty. I wouldn’t put much weight on that particular pillar. In this context, Microsoft’s decision to remove Excel Services, (the only BI component delivered by the Office team) seems like a big deal.

In reality, it’s not such a big deal in itself. Microsoft has, for the most part, shifted the functionality of Excel Services to Office Online Server (OOS). I explained in another post that despite its name, OOS is NOT a cloud service, but in reality is the new name for the Office Web Apps server – the server that allows for browser-based editing and viewing of Excel documents. The difference between Excel Services and Office Web Apps Server has always been confusing to users and a configuration headache for administrators using both. I believe that Microsoft’s consolidation of Excel Services and OOS makes a great deal of sense.

BI professionals need to understand the change to options and components with this new model, and they need to understand that the change Microsoft has made does not represent a net loss in functionality. In fact, I think you will see a net gain because users can make changes to workbooks with data connections and embedded data models directly from a browser.

PowerPivot for SharePoint

Microsoft will continue to deliver PowerPivot for SharePoint as a SharePoint Service application, and a special instance of Analysis Service. However, Microsoft has significantly changed the installation process. Previously to install PowerPivot for SharePoint you had a separate installation mode for SQL Server, which would install either SSAS in SharePoint mode alone or SSAS in SharePoint mode along with the PowerPivot for SharePoint bits if you wanted to install it on a SharePoint server. With SQL Server 2016, it’s simply one of the modes for the SSAS install (called PowerPivot mode), and the PP4SP bits are installed separately (always). You’ll also need to perform separate configuration steps to connect the OOS server to the PowerPivot mode instance.

Installing SSAS in PowerPivot mode in SQL Server 2016 CTP 3.1

Installing PowerPivot for SharePoint SQL Server 2014

Microsoft did not make very many changes to PowerPivot for SharePoint 2016. Users will note one big improvement—they can refresh Power Query-based connections. While this improvement is not currently in the preview s, Microsoft has promised this feature for the final release. Until now, PowerPivot for SharePoint could update workbooks with new data, but only if those workbooks contained standard Excel-based or PowerPivot-based connections. If you had used Power Query to import data, you were out of luck. This contrasts sharply with Microsoft’s cloud based Power BI service , which can only use Power Query to import and refresh many data sources. It was impossible to answer the question “which tool should I use for data import” without being aware of the destination platform. Now, it is simple. Use Power Query, and your workbooks will work on all platforms.

When I mention that there aren’t many changes, this includes the PowerPivot Gallery. The Gallery is a specialized SharePoint document library template that allows you to see thumbnails of your Excel and Power View reports, and gives easy access to refresh options and self-service reporting options. As with prior versions of the Gallery, it is delivered as a Silverlight application on a SharePoint view page. As I will discuss later, the Silverlight dependency could be construed as a problem, but it is not necessary to use the Gallery in order to interact with Power Pivot workbooks. You can switch to a more standard library view and still have access to workbook refresh options.

The fact that Power Query refreshes PowerPivot-based and Excel-based connections may be reason alone to update existing PowerPivot for SharePoint installations to the 2016 versions. If you navigate to the Feature pack page for SQL Server 2016 CTP 3.1, you’ll see add-ins for both SharePoint 2016 and 2013, so this will be possible, for SharePoint 2013 at least (and no, you will not need your database server to be SQL 2016 as well). Correspondingly, the SSRS Integrated mode from SQL Server 2016 will work on all SharePoint from 2013 and up (but will need the new 2016 add-in).

Power View

Power View first debuted with SSRS in SQL Server 2012. Microsoft developed Power View to become the future self-service BI reporting tool that SSRS itself never really was. Initially, Power View has very specific requirements to use it, so specific that very few people did. You needed to be running SSRS in SharePoint integrated mode (it wasn’t available in native mode), and it needed to connect to a SQL Server 2012 SSAS tabular mode instance. Once that was set up, you would create a BISM connection file and then use it to launch Power View from a SharePoint Library. When Excel 2013 debuted, it contained a version of Power View that could work with embedded data models, which greatly increased its adoption. In all cases, Power View interaction required Silverlight in order to access it using a browser.

The Silverlight dependency was a clear problem, as it prevented mobile users from working with it, and Silverlight’s “retirement” meant that realistically, no new features would be added. Microsoft addressed this problem fairly quickly in Office 365 with the addition of an HTML5-based rendering engine that would be invoked if the browser machine did not have Silverlight. These HTML 5 enhancements never made it into the on-premises version of SharePoint. While Microsoft initially put a lot of energy into the HTML5 rendering engine, but Microsoft appeared to stop adding new features at about the same time that it achieved feature parity with Silverlight.

This stoppage, combined with recent moves, indicate to me that Power View has no future. I can think of three major developments that lead me to this conclusion, and you can find these developments in Power BI, Excel 2016, and the Office Online Server.

The original Power BI Service, originally introduced in 2012 and retired on Dec 31, 2015, was based on Office 365 and Excel, and leveraged Power View for self-service reporting. Microsoft introduced a new version of the Power BI Service in July 2015; it is a standalone service that connects to Excel among many other sources. The visuals in the new Power BI service are similar to, but not the same as Power View. Realistically, they are the logical evolution of what Power View started, and are based on the D3 JavaScript engine. I think Microsoft could have made it less confusing had they just called these visuals Power View V2, but it is what it is. The open architecture allows Power BI to quickly implement new visualizations, whether they come from Microsoft or from the community. You can see the many new visuals in the Power BI Visuals gallery, most of which have been submitted by the community. When you import models and Power View from a workbook into Power BI, any Power View reports are converted to the new version, and it’s a one-way street. In this environment, Power View is clearly considered a legacy technology.

Microsoft first included Power View in Excel as a ribbon item in 2013. During the test phase of Excel 2016, it disappeared from the ribbon. Power View still exists in the product, and you can add it back to the ribbon as I describe in this article. However, I wonder why Microsoft removed it. In my opinion, the reason is simple. Microsoft wants people to stop using embedded Power View in Excel, and to start using Power BI for self-service reporting. This just makes sense from where I’m standing. Excel has a rich set of native visuals that can connect to embedded data models, and most of the Power View visuals aren’t as mature as these are. Having Power View in Excel never made much sense, except possibly from a usability standpoint. If an analyst wants to live within Excel, they can use Excel Visuals and expose them as an Excel report in Power BI. For self-service reporting, we can connect to an Excel file and use Power BI visuals. There’s simply no longer any need for Power View as an Excel embedded tool.

Finally, let’s look at the new Office Online Server 2016. When OOS renders an Excel workbook that has an embedded Power View report, it will use Silverlight to do so. That’s right… OOS 2016 will require Silverlight for Power View rendering. We know that Microsoft has already updated the Power View rendering engine for HTML5 with some of the visuals for Office 365, so we know that it was possible to do so. The only reason that I can think of that Microsoft didn’t do this entirely through Power View is to discourage people from using it, or the effort was too great for a technology that was being replaced. In addition, now in order to user Power View with OOS, you’ll need to use Kerberos constrained delegation. Microsoft did not require this in the past because Excel Services was running on the same server as SharePoint itself and could pass the User Principal Name through to the backing SSAS server that used EffectiveUserName. Now Kerberos is required for this.

So why doesn’t Microsoft just state that Power View is at end of life? Microsoft didn’t say that, but that was the message I heard at the October 2015 PASS summit. I think that the reason is that so far, Power BI is a cloud service only. In order to replace Power View fully with Power BI, customers need to embrace cloud services to some level, and there are organizations that are still not ready to do this. Very soon, after Microsoft releases SQL Server 2016, SSRS will support direct rendering of PBIX reports. (The file format for Power BI Designer and the new visuals.) At that point, the new visuals, and the new self-service reporting tools will be available on premises, but for now Power View is the only tool that can function in an on-premises only environment. It doesn’t really have a future, but it’s still necessary. It’s certainly not the only technology to exist in this state – both InfoPath and PerformancePoint serve similar roles. Microsoft still supports them and PerformancePoint, while dormant, could come back any time should Microsoft choose to do so.

SQL Server Reporting Services

Microsoft has included SSRS as a core part of the Business Intelligence workload in SharePoint since SharePoint 2003. SQL Server 2005 SP1 introduced SSRS in SharePoint integrated mode, which allowed administrators to replace the web server and storage functions of the SSRS server with that of SharePoint’s, making it easier to administer. With SQL Server 2012, you had the option to deploy SSRS as a SharePoint service application, further simplifying administration and scaling. During this period, the native mode SSRS server was always still available for those that didn’t use SharePoint, but over time, it lagged behind its sibling from a features standpoint. Many people wondered aloud if native mode SSRS had a future at all, and if SharePoint would become a required component. They needn’t have worried.

At the same time, in the past few years we’ve seen a marked shift in the way that Microsoft has positioned SharePoint, from being at the center of everything to being more a set of services. The first hint of this was the new app (now add-in) model for SharePoint, and more recently with the wholesale shifting of services, of which the Excel Services change is a prime example. This shift, combined with a renewal of emphasis on SSRS for structured reporting is cause for re-evaluation.

At the PASS summit, Microsoft rolled out its reporting roadmap. It’s comprehensive, well thought out, and exciting. I’ve pointed out before that it doesn’t include the name “PerformancePoint”, but you know what else doesn’t feature prominently? SharePoint. Microsoft committed to SharePoint integration but they offered few details.

With SQL Server 2016, customers will still deploy SSRS through both native and SharePoint integrated modes. However, for the first time, the feature set will be significantly greater in native mode at least on initial release. With the roadmap, Microsoft defined four report types:

  1. Paginated Reports – I call these operational, or structured reports. These are “classic” SSRS reports.
  2. Interactive Reports – These reports are built with Power BI Desktop, and will run in SSRS and Power BI Web. I call these “Analytical reports” and this role would previously been performed by Power View.
  3. Mobile Reports – These reports are aimed at mobile devices, and are what was previously known as Datazen.
  4. Analytical Reports and Charts – Excel workbooks.

SSRS 2016 will be the delivery mechanism for 3 of these 4 report types, but only in native mode initially. Integrated mode will support these report types one way or another down the road, but we just don’t know when. Microsoft is investing in quite a few new areas in SSRS, and it’s worthwhile to break down exactly which of the new features will be available in the two different Reporting Services modes at release. For a definition of these features, please refer to Microsoft’s roadmap announcement.

Feature Native Mode SharePoint Integrated Mode
Paginated Reports

X

X

Interactive Reports

X*

Mobile Reports

X

New Reporting Portal

X

New visuals

X

X

HTML5 rendering

X

X

Pin and link SSRS visuals to Power BI dashboards

X

*Shortly after initial release

It’s pretty clear that the tables have turned. Power View reports are now the only thing that is uniquely offered in SharePoint integrated mode. If you have SharePoint and you decide to use native mode SSRS, no functionality is lost – you can still use the SSRS web part in SharePoint for report rendering and dashboards. Reports will be stored in the SSRS server, and you’ll need to set up security separately. On the plus side, you can leave these tasks to a Reporting admin, who will not need to know how the SharePoint security model works. The biggest issue that I can see is that while integrated mode allows you to work with a single authentication provider, the SSRS native mode server requires its own, making a direct connection with it necessary, at design time at least.

These downsides aside, this shift to a focus on native mode fits with what is happening with Excel on the Office side. The two become peers that work together, as opposed to being dependent on one another. The increased functionality makes native mode compelling, even if you are running a SharePoint farm. If you are creating a new BI environment and you want to take advantage of the new SSRS features, and/or you are looking to the future for your BI investments, my recommendation is now to provision a native mode SSRS server whether or not you have SharePoint in most cases. If you already have an investment in integrated mode Reporting Services, don’t panic. Patience will be a virtue here. You will gain all of the new visuals and appearances immediately, and the other pieces will come in over time. Microsoft has not yet clearly stated the roadmap for integrated mode beyond the release of 2016.

Summary

So, to summarize, all of this represents a shift away from SharePoint as a dependency and to it as an interface option. Instead of these tools working “on” SharePoint, moving forward they will work “with” it. Overall:

  1. Power View can now be considered a legacy product.

    Power View was the future of the past. The future is now Power BI. If it helps, think of the visual elements in Power BI as being Power View V2, which in effect they are. If you’re thinking about using Power View to build a report, please consider Power BI Desktop. If it’s not possible, the good news is that if you use Excel to build it, you will be able to easily import it into Power BI Desktop – it has a migration path forward, and on premises PBIX support is also on the way.

  2. SQL Server Reporting Services should be deployed in Native mode

    Whether or not you have a SharePoint farm, native mode SSRS is the way to go for a new deployment, even if you’re not yet ready for SQL Server 2016 SSRS. The reason for this is that migrating reports between the two modes is not simple.

  3. Excel Reporting is alive and well

    Excel is still well supported and Microsoft is investing in it. It is the tool for analysts and model builders and is easily portable to Power BI and to SSAS. The removal of Excel services in SharePoint is simply and architectural shift, not a functional one, and Excel reports are very well supported in Power BI.

  4. Power BI and Power BI Desktop are the preferred tools for self-service reporting

    Self-service reporting is clearly the domain of Power BI. The legacy options are still available for current on-premises customers, but if you want to future-proof your investments, look to Power BI.

Where Did Power View Go in Excel 2016?

If you’ve been using the Excel 2016 Preview or just Excel 2016 (depending on when you read this), you may have noticed that there is no longer an option to insert a Power View report into o workbook. The reason is that it has been removed from the default ribbon in Excel 2016. It used to be on the Insert tab in the Reports Section, right beside Power Map.

Power View in Excel 2013

However, opening the Insert tab in Excel 2016 reveals it to be missing.

Power View Missing in Excel 2016

Did Microsoft remove Power View from Excel? What’s going on? Power View is still very much a part of Excel; the only change is that now it is no longer a default ribbon option. The good news is that it’s simple enough to add it back in. To do so, we need to edit the ribbon. Click on File-Options, and then select Advanced Options. The ribbon editor will appear. We can add Power View to any tab that we would like, or even create a new one, but here we’re just going to add it back to the Insert menu. To do so, expand the Insert menu. Each command must be added to a group, so we need to click the “New Group” button. Next, because I don’t think anyone will want their group named “New Group”, we want to rename it. In this case, we’ll rename it to “Reports”, the way that it used to be.

Adding a new group to the Insert tab

Next, we need to add Power View into the group. The easiest way to do this is to select “Commands Not in the Ribbon” from the “Choose commands from” dropdown. It’s a long list of items to choose from, and you’ll be tempted to look under “P” for Power View. You will be disappointed. The correct command is actually to be found in the “I”s, and it is “Insert a Power View Report”. Select that option, and click the “Add” button.

Once this is complete, Power View should once again appear in the Insert tab, in the Reports section.

I have no idea why Power View has been removed from the ribbon by default. It may just be temporary given that we’re not yet at release, but it could signal some other change. In any event, if you work with both Power View and Excel 2016, you can continue to do so.