Power BI Analyze in Excel – The beginning of a beautiful thing?

One of the announcements made at the Microsoft Data Summit in this past March 2016 was the availability of Analyze in Excel. This feature allows an Excel workbook to connect to a data model that is stored in the Power BI service, and to use it to analyze the data contained within. With this approach Excel is not importing data, or at least it is not importing any more data than the query results. It is exactly like connecting data to SQL Server Analysis Services data sources, something that Excel users have been doing for years. Well, to be completely accurate, it’s not LIKE connecting to SSAS, it IS connecting to SSAS. The only difference is that in this case SSAS is in the cloud. This feature significantly enhances the utility of the Power BI Service, and is important for several reasons that may not be all that obvious. I’d like to walk through a few of them, but let’s start with the obvious.

Excel is a very powerful analytical tool

As nice, and as attractive as Power BI visuals are, Excel still rules the roost when it comes to doing advanced analytics. Excel has been doing this for years and is very mature. It supports features such as pivot tables, pivot charts, and drill through to data, where Power BI reports still do not. The lack of these features can be a blocker for Power BI on its own, but if the data models in Power BI can be analyzed with Excel, suddenly a move to Power BI is not an either/or decision – you can have it both ways. You can deploy models and reports into Power BI and take advantage of all the goodness there, but you can also connect with Excel when the deep analysis is needed. With Analyze in Excel, you can have it both ways.

A wider audience for your data models

Very often, the person that builds the data model is the same person that does the analysis. This is the nature of self-service analytics. In the past when the only multidimensional analysis tools were OLAP cubes and connected Excel workbooks, cube design was a specialized skill. The cubes were published and users would use them as is. The advent of the data model (Power Pivot) and self service analytics lowered the skills bar so that analysts could acquire data, model it and analyze it, making the entire process much quicker and responsive. However, this still puts the model out of reach for those with no data modeling skills or interest.

Analyze in Excel provides the best of both worlds. Analysts can build models and reports in Power BI, and users that need more in depth analysis can connect to them with Excel without having to reinvent the wheel. This in effect provides the same capability that PowerPivot for SharePoint does on premises. One model can now reach a much wider audience of users. This has many of the benefits of an on-premises SSAS deployments without all of the organizational overhead of getting them up and running.

Uses the Analysis Services OLEDB Driver

The connection that is made from Excel to the Power BI services uses the latest version of the classic Analysis Services OLEDB driver. This is the driver that has always been used to allow Excel (and other tools) to communicate with Analysis Services, and this new version has been updated in order to work with the cloud based SSAS service. In fact, in order to use the feature, you must first download and install the updated driver. Therefore, in theory, any tool that uses this driver should be able to communicate with Power BI models as if Power BI was one great big SQL Server Analysis Services server (because it is).

It really is Analysis Services in the cloud

The Power BI service itself is backed by tabular mode SSAS. Until now, it was necessary to go through the service to access it. Analyze in Excel is the first instance that I know of that a client application communicating directly with that SSAS instance. While this connection is really using the Power BI API, it does beg the question – can a fully Platform as a Service version of Analysis Services be very far away?

Claims based authentication and Power BI API

None of the products in the SQL Server suite currently supports claims authentication. This is true even for the yet unreleased SQL Server 2016. Even SQL Azure, a cloud based version of SQL Server, requires SQL authentication only (although Azure Active Directory authenticated databases are currently in preview). However, looking at the connection string contained in the ODC file used by the Analyze in Excel feature reveals some interesting things. Here’s one connection string:

<odc:ConnectionString>Provider=MSOLAP.7;Integrated Security=ClaimsToken;Identity Provider=AAD;Data Source=https://analysis.windows.net/powerbi/api;;Initial Catalog=xxxxxxx; ……..

The value MSOLAP.7 for the provider indicates that this is the next version of the SSAS OLEDB Driver. No surprises there, but this does hint at future compatibility (see SharePoint below). The value for Integrated Security, and Identity Provider (ClaimsToken and AAD) indicate that it is leveraging Azure Active Directory Claims authentication. We therefore have a version of SSAS that can use Claims based authentication. This isn’t available to on-premises installations, but given that the capability has been built, I imagine that it is not all that far away.

Finally, the Data source indicates that the Power BI API is being used to marshal all communication with the back end API service. I think that it is reasonably to conclude that any API for a PaaS based version of SSAS would be based on, or strongly resemble the Power BI API. They may even be one and the same.

Excel Online in SharePoint

As anyone that has set up PowerPivot for SharePoint can tell you, SharePoint supports the configuration of new OLEDB drivers. This support carries forward into the Office Online Server in the world of SharePoint 2016. Given that both SharePoint and OOS utilize claims based authentication, it should theoretically possible to create a workbook that uses the Analyze in Excel feature, store it in SharePoint, and have it work for multiple users from within a browser. I imagine that more plumbing is needed at this point, but it would be an interesting way of integrating Power BI in the cloud with SharePoint both on premises and Online.

Reusing the Excel Files, and Limitations

In the same vein as discussed with SharePoint, Power BI itself allows Excel files to be interacted with in the service in exactly the same manner that Excel Online does. Theoretically, one should be able to use Analyze in Excel to build a workbook, then connect it to Power BI and have it work for interaction. While it is possible to connect it, all interactions fail at the moment. It appears that the Power BI service (or the backing Office Online service) does not yet support the new OLEDB driver.

Another current limitation of this feature is that data sources using Direct Query (this includes SSAS sources) or sources created by the Power BI API cannot be used with Analyze in Excel. At least not yet.

Analyze in Excel is another useful tool in the Power BI arsenal, but as outlined above, I think that it’s a harbinger of even greater things to come.

Connecting Excel to Data with SharePoint 2016 and Office Online Server 2016

If you’re in the SharePoint community, and you’ve been living under a rock since August 2015, you may not have heard that Excel Services is no longer present in SharePoint 2016. This is not a as big a deal as it initially sounds, because all (OK.. most) of the features available to Excel Services have been moved over to Office Online server, streamlining the architecture. It does however lead to many changes, and this is certainly true for external data access and authentication. This article hopes to answer the question of how these changes impact data access and the Excel Services Authentication Settings.

My previous article “Adding Excel Services Capabilities to a SharePoint 2016 Farm” dealt with getting the farm enabled with Office Online Server and Excel Services capabilities. This article drills down into using Excel workbooks connected to back end data sources. It does not cover PowerPivot enabled workbooks, or workbooks with an embedded data model – that is the topic of the next article in this series.

The Excel Services authentication settings are use when an Excel workbook is opened in a browser, and the workbook connects directly to data on another server. Note that it does NOT pertain to PowerPivot enabled workbooks (at least that portion of those workbooks that uses the data model) as they utilize a different mechanism entirely.

The settings are accessed from the data tab in Excel. First, click on “Connections” to view the available connections in the workbook. Next select the connection and click “Properties”. Select the “Definition” tab, and at the bottom of the dialog box, click on “Authentication Settings”. It’s a well hidden option.

image_thumb3

Before continuing, it should be mentioned that these settings affect Windows credentials only. If the data source uses SQL Authentication, or something proprietary, the credentials will be stored in the Secure Store Service, or embedded in the connection string, and they will be used as proxy accounts – fine grained permissions on the destination server will not be possible.

The issue with Windows credentials is the standard “double hop” problem encountered in Windows authentication. This problem existed in prior versions of Excel Services, with SharePoint being the middle tier server. Office Online Server (OOS) must now reside on a separate server, and this add an extra “hop”, exacerbating the problem. In a nutshell, the problem is how to allow the server (in this case, OOS) to provide an identity to the source server. These settings allow for a number of solutions to this problem.

The two major data sources that support Windows authentication are SQL Server, and SQL Server Analysis Services (SSAS), and they are unique enough to be considered separately. Any other data source that supports Windows authentication can be treated in the same manner as SQL Server.

1. Use the authenticated user’s account

This is the option that you’ll choose if you want the identity of the person opening the workbook to be passed back to the source server when the data is refreshed, or in the case off SSAS, interacted with. It’s also the default option. If you never knew about the Excel Services Option Authentication Options button, this would be the one that you’d use.

Before you go rushing to declare that this is the best option, keep in mind that it means that the user permissions will need to be maintained on the data source for all users accessing it. Sometimes proxy credentials are a better choice. In order to get either option working however, additional configuration will be required.

SQL Server Analysis Services Data Sources

If your data source is SSAS you have two options. You can set up Kerberos Constrained Delegation (KCD) between the OOS Server and the SSAS Server, or you can use EffectiveUserName. Given that Kerberos will be discussed in the SQL Server section, and that my recommendation is to use EffectiveUserName whenever possible, we will cover EffectiveUserName here.

To begin with, EffectiveUserName is turned off by default in OOS. In Excel Services this could be turned on through the user interface in the Excel Services Service Application, but with OOS, this is done through PowerShell. As with the other OOS parameters, it’s a single line on the OOS Server:

Set-OfficeWebAppsFarm -ExcelUseEffectiveUserName:$true

With EffectiveUserName,  a connection is made between the OOS Server and the SSAS server using the machine account of the OOS Server itself. The OOS server has the identity, but not the password or token of the requesting user, and that is provided to the SSAS Server to use when executing queries. This impersonation process requires a high level of trust, and the machine account of the OOS server must be an administrator in the destination SSAS instance.

Adding this account requires the use of SQL Server Management Studio 2016 (SSMS), in prior versions it was not possible to add machine accounts to SSAS. To add it, connect SSMS to the destination SSAS instance, then right click on the server node and select “Properties”. Once the dialog options, select the “Security” node, and click the “Add” button.

image_thumb18

Next (and order is important here), select the “Locations” button, and choose “Entire Directory” (or a container where your server accounts live). Click “Ok” and and then click the “Object Types” button. Computer accounts are excluded by default, and this is where you select them.

image_thumb16

Once selected, you can enter the machine account in the form of DOMAIN\MachineName. After clicking OK, it should appear in the administrators list with a “$” appended to it. In our case, the machine name is NAUTILUS2016OOS and the domain is NAUTILUS. THis needs to be done for each OOS server in your farm.

image_thumb17

At this point, any user interacting with the workbook in a browser will interact with the SSAS server using their own identity, which of course means that the SSAS server needs to know who they are. The end users will need to be granted access to the data sources in SSAS, in the same way they would if they were connecting to it in an Excel client.

SQL Server Data Sources

SQL Server has an equivalent feature to EffectiveUserName, the SetUser command. This feature has been supported for years in SQL Server Reporting Services, but unfortunately was never supported by Excel Services. Unfortunately, it’s still not supported by OOS. SharePoint and OOS both support claims based identities, but SQL server does not and this leaves us with only Kerberos Constrained Delegation to provide the user’s identity to the source system.

In order for this to work, Kerberos must be configured for the OOS Server(s) to trust the account running SQL Server on the destination server to delegate credentials. However, KCD delegates Windows credentials, and as mentioned above, both SharePoint 2016 and OOS use claims based authentication. It is necessary to first retrieve the user’s Windows token in order to delegate it, and this needs to happen on the OOS server, as it is now the final “hop” before connecting to the source system.

In the past, SharePoint provided this capability through the Claims to Windows Token Service (C2WTS). The was perfect for Excel Services, as it ran on top of SharePoint and could leverage this service. With OOS that is no longer an option because it runs on a separate machine, but C2WTS can be made available here as a part of the Windows Identity Foundation (WIF).

WIF is not a prerequisite for OOS, so it must be added first. In order to do so,  launch the Add Roles and Features wizard on each OOS server, proceed to the features section, and select “Windows Identity Foundation”. Allow the wizard to complete.

image_thumb20

(Note – if you will be supporting ADFS, you will also need to install the Microsoft Identity Extensions.)

Next, the C2WTS needs to be configured to allow access for the Network Service account. To do so, edit the file “C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config” and remove the comments in the <allowedCallers> section for Network Service. When complete, it should appear as below:

<allowedCallers>
<clear/>
<add value=”NT AUTHORITY\Network Service” />
<!– <add value=”NT AUTHORITY\Local Service” /> –>
<!– <add value=”NT AUTHORITY\System” /> –>
<!– <add value=”NT AUTHORITY\Authenticated Users” /> –>
</allowedCallers>

Once edited, run the following Powershell to start the service and set it to automatic start:

Set-Service -Name C2WTS -startuptype “automatic”
Start-Service -Name C2WTS

At this point, we are ready to start delegating credentials.

The following Kerberos settings need to be performed on a directory server, or a server that can run the SetSPN command and the Active Directory Users And Computers tool.

The first step is to add the SQL Server service and its associated account as a Service Principal Name (SPN). We need to do this for both the machine name of the server and its Fully Qualified Domain Name(s). This can be done from either PowerShell or a command prompt.

SetSPN –S MSSQLSvc/NAUTILUS2016SQL.nautilus.local :1433 NAUTILUS\SQLService
SetSPN –S MSSQLSvc/NAUTILUS2016SQL :1433 NAUTILUS\SQLService

In this example, NAUTILUS2016SQL is the name of the SQL server, nautilus.local is its domain, and NAUTILUS\SQLService is the account that SQL service is running as.

Once the SPNs have been added,  open the Active Directory Users and Computers tool. Navigate to the Computers node, select the OOS Server(s), right click and select properties. Click the delegation tab, Select “Trust this computer for delegation to specified servers only” (this is the constrained part…), and “Use any authentication protocol”. Next, click the “Add” button, and then “Users and computers”. Find the SQL service account (in our case, NAUTILUS\SQLService), and save it. You should see its corresponding service appear in the dialog box.

image_thumb1

Click OK, save everything and that’s it. KCD should be set up to allow delegation from OOS to SQL server, and any direct connected Excel workbooks should work.

This process will need to be repeated for any data source that supports Windows Authentication.

2. Use a Stored Account

A stored account is a proxy account, which is to say that all users using the workbook in a browser will connect to the data using the same credential – the one that is stored. This option uses the Secure Store Service in SharePoint, which is therefore a requirement. Usage is relatively straightforward. To set up a new Stored Account, navigate to the Manage Service Applications section in Central Admin on the SharePoint farm, and select the Secure Store Service.

Once in the service, Select the “New” button in the ribbon to create a new “Target Application” (Stored Account). Enter an ID, Display Name, and contact email (it’s required for some reason). Then, ensure that the Target Application Type is “Group”.

image_thumb[1]

Once complete, click the “Next” button.

The next screen prompts for the type of credentials, and the default is for Windows credentials, which is what we’re using. Click the “Next” button.

The administrators are those users that can modify the target application (stored account) and set the credentials for it. Add administrators as appropriate. The members are those users that can use the stored account, so this should be set to anyone that can access the workbook.

Now, in addition to any regular users, there is one or more accountsthat MUST be in the members list –  the computer accounts for all of the OOS Servers. This makes sense as the Office Online service itself runs as the Network Account, and it needs to consume the credentials from the SSS – it needs permissions to do so. The account should be entered in the form DOMAIN\MachineName$ – so in our example it’s NAUTILUS\Nautilus2016OOS$.

image

Finally, click OK to save it. The last step is to set the credentials. Select the target application, and click on the “Set Credentials” button in the ribbon. Enter the account that will be used as the proxy here. This will be the stored account that gets used to access the source data, so it obviously should have access to that data.

image_thumb[5]

If your network is http based, there is one further configuration task to perform on the OOS server. By default, OOS requires https to access the secure store service. The option for this is in a PowerShell parameter, and you can change it with the following PowerShell script:

Set-OfficeWebAppsFarm -AllowHttpSecureStoreConnections:$true

Now that this is complete, go to the Excel Services Authentication dialog in an Excel workbook, select “Use a Stored Account” and enter the name of the Secure Store Service Target application that was created (in the example above, this is SSSExcelTest). Save the workbook to SharePoint, open it in a browser and refresh data. All should work.

This procedure is the same for both Analysis Services and for SQL Server. This approach does not require Kerberos, EffectiveUserName, the C2WTS, or S2S trust for it to work. It is likely the easiest option to get up and running quickly.

As with any proxy account, these accounts should only have enough access to  the source data to serve the worksheet(s) they are used for. These should always be least privilege accounts.

3. None

This option was always poorly named. None does not mean no credentials, or anonymous, it means default, or the Unattended Access Account. Both Excel Services and PerformancePoint had the concept of an Unattended Access Account. This account was really just a default entry in the Secure Store Service. It’s useful when the entire organization has access to a certain body of data. Giving the Unattended Access Account access to that data means that nobody needs to configure anything – it should “just work”.

One of the casualties of the move from Excel Services to OOS was the Unattended Access Account. There is no configuration area or PowerShell where it can be set. It would seem that the “None” option cannot be used at all. However, it can, in a very limited way.

Selecting this option will cause the OOS machine account(s) to be used when attempting to access the source data. Therefore, if we grant access in the source data to the machine account, we are treating it like an Unattended Access Account. If this is acceptable from a security standpoint, then it can be used in this manner.

We have already seen how to do this for SSAS, but there is no option in SSMS to add a machine account as a security user in SQL Server, even with SSMS 2016. It is however possible. The way to do this is to use SSMS to open a new query, and run the following T-SQL command:

create login [NAUTILUS\NAUTILUS2016OOS$] from windows

where NAUTILUS\NAUTILUS2016OOS$ is the machine account. The square brackets are required. This will add the machine account as a user in the security section in SQL Server Management Studio.  Once that is done, the account can be added to individual databases in the same manner as any SQL account by navigating to the database Security\Users folder and adding a new user.  From here, select “SQL User with login” as the User type, click the ellipsis beside Login name, enter the machine account and select “Check Names”. Once added click “OK”, and “OK”, and then that account can be used just like any other account.

SSAS cannot use the “None” parameter in this way due to the way that the EffectiveUserName option works. If EffectiveUserName is enabled in OOS, it will simply use the credentials of the logged in user and be indistinguishable from the “Use the authenticated user’s account” option. If it is not enabled, it will try to connect to SSAS as anonymous and fail. The alternative to failing would be to open up access to all data on the server – failure is the best option here.  The bottom line is that the “None” should never be used with SSAS.

Summary

Below is a Summary of the various authentication options that can be used with Excel and OOS, and the requirements to make them work.

Use authenticated user’s account with EffectiveUserName (SSAS Only)

  • ExcelUseEffectiveUserName = true on OOS Server
  • OOS Computer account added to SSAS Server Administrator list

Use authenticated user’s account with Kerberos (SSAS or SQL Server)

  • CTWTS running on OOS Server
  • C2WTS configured to allow Network Service on OOS
  • KCD delegation from OOS Server to Source Server/account

Use a stored account (SSAS or SQL Server)

  • SSS Set up on SharePoint Farm
  • SSS Target account(s) created
  • OOS Computer account added to Members for Target account(s)
  • Target account configured for Least Privilege access to data

None

  • OOS Server account configured with Least privilege access to data (SQL Server Only)
  • ExcelUseEffectiveUserName = true on OOS Server (SSAS)

Acknowledgements

I wanted to acknowledge the help and guidance I received while putting this together and testing. Both Kay Unkroth and Jason Haak at Microsoft were quick to answer some of the thornier configuration questions, and fellow MVP Trevor Seward was instrumental in getting me over the Kerberos hump. Thanks guys.

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.

New Clarity on SharePoint and Office Release Cadence

For the past few years, Microsoft has been talking about cloud computing, as it continues to transition itself into a devices and services company. As part of this transition, there has been a strong push from within to increase its release cadence, shortening the time between major releases of its products.  In fact, we’ve seen the result of this in Office 365, with now features coming to the product every 3-4 months or so. As a side note, the place to keep track of the changes is the Office 365 technology blog.

What has been less clear is how this impacts the traditional on-premises versions of the products. Being a part of the SharePoint community, I’m obviously concerned with staying on top of developments with it, and from some of the messaging from Microsoft, it appeared that we were looking at minor releases on a quarterly basis, with major releases coming annually.

While the idea of this is very interesting from a technical standpoint, it causes some very real concern for IT departments that don’t want to be retooling every year. The good new is, these concerns are unfounded. This morning, at a session for the Office group MVPs, Julia White, a General Manager in the Office division cleared this up for us, and succinctly articulated the release strategy moving forward. (She specifically stated that this information was not NDA and could be freely disseminated).

While the cloud versions of Office 365 will continue to receive updates every few months or so, the on premises versions of the components will continue to be updated every 2-3 years, with patches being released in the form of incremental updates every two months. Essentially, it’s business as usual for on-premises deployments.

In my opinion, this is a solid approach. Those wanting access to the latest and greatest can turn to Office 365, or use it in a hybrid scenario, and IT departments will be able to keep pace as well. This “cloud first” approach benefits everyone. New features don’t need to wait for a major release to see the light of day, early adopters can get access to the latest and greatest in a speedier fashion, and when major releases are rolled out, they will have benefitted from the fact that the constituent features have been proven in the field.

Running a Business in the Cloud – 3 Years In

A couple of years ago, I wrote a post called “Why I love Office 365”. It centered on the reasons that a SharePoint infrastructure consultant might want to outsource their SharePoint operations. After running our business in the cloud with it and a few other services for a few years now, I thought that I would revisit the topic and share a few observations and impressions.

In 2010 When UnlimitedViz became UnlimitedViz again (it’s a long story), we made a conscious decision to not invest in traditional “brick and mortar” infrastructure. Everyone working with us would work from their homes, hotel rooms, or wherever they happen to be. If fact, as I write this, I am sitting on a beach just outside of Playa Del Carmen in Mexico (it’s tough, I know). In order to do that, we would either need to set up our own IT infrastructure at a hosting center, or to rely on cloud services. We opted for the latter. At the time, it really was a leap of faith.

In the early days, we used BPOS – the original cloud offering from Microsoft that provided SharePoint, Exchange and Live Meeting. At the time, the SharePoint offering was extremely limited, so we primarily used Exchange for email, and that was about it. Because we’re a SharePoint shop, we also relied on local virtual machines for development and demonstration farms. We used to carry around a huge machine that was configured with several VMs to recreate a real world multi server infrastructure. To complete the picture, we chose Freshbooks for our time and expense entry service – it has served us well.

Eventually, we moved to Cloudshare for our demonstration environment to support our infrastructure, workflow and BI demonstrations. No more lugging around big hardware. The big concern there is what happens if there is no connectivity, or there is a problem with the platform? These continue to be concerns, although connection availability has become even better over time. Since hardware failure is also a risk, I consider that and the platform risk to be a wash.

When BPOS became Office 365, it became our core cloud platform. From our vantage point, Exchange continued to work well, we gained Lync, which we now use every day for instant messaging and conferencing, but the big bonus was that SharePoint Online became a viable option for us. The more recent update to the SharePoint 2013 feature set brought even more value.

In that spirit, I thought that I would list a few of the Office 365 features that we use on a regular basis, and how we use them.

Exchange Online – This is the service that we’ve used the longest. Secure, reliable email service with server side spam filtering that you can control. It never seems to go down. Email is probably the most sensitive feature to outages, and in the three years that we’ve been using it, we’ve noticed outages twice – only once during business hours, and in both cases, it was remedied quickly. It’s a track record that I don’t think that many on premises installations could match.

Lync Online – As mentioned above – we are a small, but highly distributed organization. Lync allows everyone to stay in touch, and to understand what each other is up to. Lync to Lync communication is great, and we are constantly using it to ask for assistance, or to demonstrate concepts quickly. Our cloud only implementation prevents us from taking advantage of call to landline capability, and video and voice quality isn’t quite as good as I’ve found with Skype (which I use instead of a landline). Hopefully these things will be addressed with future Skype integration.

We’ve federated Lync with Microsoft, which means that we can communicate just as easily with Microsoft employees, as well as with any other organizations that have federated their Lync environment. Of course, the Skype and MSN users are federated too, so Lync can be used to communicate with them as well.

Document Management – All of our corporate documentation goes into SharePoint. However, even though we always preach good document management practices, when it came to our own documents we weren’t always as disciplined as we should have been, and too many documents were sent via email, or stored locally. There are good reasons for this, as it wasn’t always simple to get the documents uploaded  into SharePoint. SkyDrive Pro has removed these barriers, making the process as simple as working with the file system. In fact, when your library is synchronized, you are working with the file system, and any third party tools can work with it seamlessly, whether on or offline.

Team One Notes – We have a wide variety of customers, with a wide variety of information to keep track of. this information isn’t well structured, so it’s a perfect problem for OneNote to solve. We have a single library with a single Notebook for each customer that is available to all of our consultants. OneNote replicates, so it isn’t necessary to be online, and it’s a great place to store things like VPN client installations, credential information etc. OneNote is a fantastic place to put down quick thoughts, meeting notes etc. so that they don’t get lost. It allows for one consultant to pick up from where the other left off just by opening the OneNote. It really is a handy tool and something my team uses every day.

InfoPath Forms – Although we use Office 365 for our SharePoint needs, not all of our customers do. The bulk of them are still on premises, and we help them with their infrastructure. When working with a SharePoint farm, there is a lot of information to keep track of – server names, IP addresses, service accounts, running services, etc. To keep all of this organized, we use InfoPath forms. This provides a consistent set of information about our customers’ SharePoint environments that can be used by anyone within the company. In fact, it’s  been used more that once to provide information back to our customers when it was lost.

Demonstration Environment – I mentioned before that we use a mock company infrastructure in Cloudshare for our demonstration environment. As more and more features are enabled in Office 365, we can increasingly move to it for our demonstrations. This is certainly true of information architecture demos, but with the recent release of Nintex Forms for Office 365, our workflow demos are moving there too. At the moment, there are many Business Intelligence features in SharePoint that we can’t use in Office 365, but with the pending release of Power BI, even that gap is narrowing.

Customer Collaboration – Office 365 allows external users to be invited to a SharePoint site at no extra cost. All that they need is a Microsoft (Live) or Organizational (Office 365) account. Whenever we need to collaborate with a customer on a project, we can spin up a team site, or an entire site collection, invite all of the team members, whether internal or external and get going. All of the content is in SharePoint and is therefore searchable (if desired) throughout the Office 365 tenant. We don’t always use this capability, but it comes in very handy when it’s needed.

There are many more features that we use quite regularly, but these are a few of them. Quite frankly, Office 365 allows us to run our business with our lean, highly disconnected model. Right out of the box it provides the bulk of our business requirements. In Canada, the “all-in” version of Office 365 (Enterprise E4) which includes Office Professional Plus costs $25.50 per user per month. My cell phone bill regularly comes in at over $200 per month, and when I compare the value offered by the two, it isn’t even close. In my opinion, Office 365 is far and away the best value of any of our infrastructure investments.

So yes – 3 years in, I still love Office 365.