SharePoint 2016 Team SItes and Groups – It All Comes Together

SharePoint is back. With a vengeance.

For the past few years, SharePoint has been relegated to a supporting role within Office 365. It even lost its identity a few years back, with the name “SharePoint” being replaced by the bland “Sites”. This has been exacerbated recently by the rise of Groups (referred to either as Outlook Groups or Office 365 Groups). If Groups are the way forward, what value can SharePoint provide?

A lot, as it turns out.

Office 365 Groups

I refer to them as Office 365 Groups, because they incorporate elements from multiple Office 365 products. However, they are also referred to as Outlook Groups, which is the name of the mobile app. The interesting thing is that if you scratch the surface of the Groups user interface in either OneDrive or OneNote, you can see pretty quickly that it’s really a SharePoint site, or more specifically a site collection. Just look at the URL.

clip_image001

In SharePoint, a Group is a specific type of site collection with a single document library for files (the OneDrive), and a library for other supporting files (including the Group OneNote). The difference is that a Group is what it is – it can’t be extended or modified to any significant degree. You can’t even access the All Content or site settings by adding “/_layouts/15/viewlsts.aspx” to the site URL (if you try, you get redirected to the main OneDrive view of the Group). SharePoint is really just there as the container. This can be frustrating, because as anyone that has worked with SharePoint knows, it can be much, much more.

The introduction of Groups initially caused confusion, particularly for users of SharePoint team sites, or Yammer. Was OneDrive replacing SharePoint (which is kind of silly… OneDrive IS SharePoint)? Did the new Exchange based conversations mean that Yammer was dead? Those questions have been hanging out there unanswered for quite some time.

Once I understood them, I came to really like Office 365 Groups. They bring together multiple tools into a single coherent location with a clear security boundary, and they are relatively simple to manage. One of the criticisms of Groups has been that there is no single central UI. Groups are manifested in Exchange, SharePoint, OneDrive, OneNote and Power BI, but there’s really no central starting point for a group. It’s like a city of suburbs in search of a downtown.

There have been more than a few detractors of Groups as well. Most of them relate to their immaturity. The Outlook conversations provide excellent email integration (obviously) but were not as full featured as Yammer in other ways. There have been several others, but the biggest complaint seems to me to be the fact that a SharePoint team site provides much more functionality than a simple OneDrive library. These factors have been a significant blocker for the adoption of Office 365 Groups.

That all changed with the Future of SharePoint event on May 4, 2016.

The New Team Sites

Team sites have been the traditional place for groups of people to work in the world of SharePoint. These sites would be decorated with web parts, both in and out of the box in order to augment their capabilities, and to provide a window into other team based content structures such as calendars, custom lists, reports, etc. Team sites have always seemed like the logical starting place for group data, and now they are.

Beginning in mid 2016, whenever a new group is created, a new team site will be created as well. Conversely, a new team site will create an Office 365 Group, with all of its components (OneDrive, OneNote, Mail address, Planner, Power BI Workspace). To be totally clear, this new style of team site is a SharePoint site collection, and not a subsite (or web), which means that its security details are bound to that of the group.

Yammer users may wonder what this means for the previously announced integration with both Groups and Azure Active Directory. Nothing was announced at the event, so this is pure speculation on my part, but I would have to assume that if there is to be a 1:1 correspondence between Office 365 Groups, and Yammer groups, that Yammer will be a part of this as well. Given SharePoint’s strengths, I can only assume that this will be the place that all non conversational Yammer content is stored (files, calendars, etc.

The new team site will intrinsically integrate many of the things that formerly needed to be added on later, and the new Office 365 connectors mean that many other content sources can be added with a minimum of effort.

External Sharing

While both SharePoint and Yammer have had external sharing for several years now, and Yammer now has external groups (with a lowercase g…), Office 365 groups have been restricted to members of the tenant’s Azure Active Directory. Therefore, if we now have a 1:1 correspondence between Groups and team sites, and we are also able to use Yammer as the conversations provider, Office 365 groups need to accommodate external users.

The good news is that soon, they will. Thanks to Wictor Wilen’s sharp eye, we can see in the Office 365 admin center that as of this writing, the infrastructure to support external access to groups has already rolled out. Coincidentally (or not), Yammer support of external groups also rolled out in the same timeframe.

image

The new SharePoint team sites, and their integration with Groups will give Office 365 that entry point that so many have been missing. It is exceedingly easy, and fast to get up and running with a usable site that is automatically integrated across the platform.  When you create a Group, you not only have the AAD group, but a team site, a calendar, a distribution list, a conversation platform, a Planner Plan and a Power BI workspace. At the same time, it brings SharePoint back out of the shadows, and back in to the limelight.

SharePoint is back at the center of Office 365, and it’s better than ever.

Advertisements

SSRS 2016 – Integrated or Native Mode – Which one should you use?

The answer to the SQL Server Reporting Services Integrated vs. Native mode question used to be very simple. Once upon a time, if you had a SharePoint environment, you would want to deploy SharePoint Integrated mode, and if you didn’t, you would pick Native. Integrated mode would leverage your pre-existing security model in SharePoint, it would allow reports to look like documents in SharePoint making them more user friendly, and you would be able to use the advanced features of the SSRS web parts in SharePoint. Non-SharePoint users were able to do what they needed around security and report storage with Native mode. Everyone was happy.

SQL Server 2012 changed that a little bit. Power View reports were first introduced in SQL Server 2012 as a part of SSRS. These reports leveraged the tabular (PowerPivot) data models available in SSAS 2012 and provided some very user friendly tools for self service analytical reporting. However, one of the catches introduced was that Power View reports were only available in SharePoint Integrated mode. Suddenly, the choice of mode became feature based. This suited those with SharePoint environments just fine, but those without SharePoint would now need to stand up a SharePoint farm just to gain access to Power View. This is a daunting prospect, especially for those unfamiliar with SharePoint. This requirement, coupled with the minimal investment into new features for core SSRS in SQL Server 2012 had the effect of making the Native mode users feel abandoned. After all, we know what typically happens when Microsoft stops investing in a product. The balance was heavily tilted in the favour of Integrated mode.

The new normal

This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.

Feature Integrated Mode Native Mode
HTML 5 based rendering engine X X
New chart types X X
PDF based printing (no ActiveX) X X
PowerPoint rendering and export X X
New UI for Report Builder X X
Customizable parameters pane X
New web portal X
Mobile reports X
KPIs X
Pint to Power BI X
Render Power BI reports* X

* Coming soon

You can see above that the balance has shifted very heavily in favour of Native mode. The folks using Native mode are very happy about this move – they are no longer having SharePoint forced on them in order to access new features. However, now it’s the SharePoint folks turn to feel abandoned, but they really don’t need to. SSRS Integrated mode is still getting a significant enhancement in 2016, it’s just not as significant as the improvement to Native Mode. Integrated mode is also still required for rendering Power View reports. Last fall’s Reporting Roadmap reconfirmed Microsoft’s commitment to SharePoint as a platform -“We will continue to support embedding of BI content into SharePoint”. SharePoint has a bright future as an report destination. The only question is how that will be brought about.

It may well be that the features had to go into Native mode first in order to meet the shipping schedules, and that they’ll be brought along eventually. I suspect however that this is not the case. I think that this is either the last, or penultimate version of SSRS to contain Integrated mode. If the same level of embedding into SharePoint could be provided by Native mode, and the user experience improved (as it has been in the new report portal) then there is very little real need for Integrated mode at all.

Building shared service applications in SharePoint is a non-trivial task, and those resources could likely be better spent on features for SSRS. A new embedding model could support both SharePoint on-premises (as it currently does) and SharePoint Online (as it currently doesn’t). The same mechanism could be used to embed Power BI reports. We’ve already seen glimpses of this hybrid interoperability in the SSRS and Excel pin visual to Power BI capability. I suspect that over time we’ll see SSRS Native mode and its reporting portal also assume the role currently played by PerformancePoint Services as well. For all of these reasons, I think that SSRS Native mode is the only future for SSRS.

But that’s the future. What about the present?

When I first learned of these developments, I suspected that I would be recommending Native mode for anyone moving forward. However, as I discuss in an earlier article, the SSRS web parts for Native mode are deprecated, and missing key pieces of functionality, parameters being first among them. They are really little more than iframes, and they certainly can’t replace the Integrated mode web parts. If you’re going to use reporting in SharePoint in any meaningful way, or you are looking to upgrade an existing SharePoint farm with SSRS integration to 2016, you’re going to need Integrated mode. That means no mobile reports, report manager, or Power BI integration.

So why choose?

There is nothing stopping you (apart from possibly licensing) from running both modes. Using Integrated mode, you can take advantage of the new rendering engine, etc, and a separate Native mode server can be used for Report Manager, mobile reports, and Power BI integration. Over time, more reports can be brought over to Native mode and the embedding story improves. Once they are all brought over in “the future”, the Integrated mode service can be simply removed. This provides for a smooth, gradual migration. In fact, you can set up an SSRS 2016 Native mode server along side an existing SharePoint 2013 farm with SSRS 2014 or earlier Integrated mode to get started. Your SharePoint reports won’t have any of the new features, but your Native mode certainly will.

We are clearly in a transitional stage when it comes to on-premises reporting technologies from Microsoft. There are significant, bold steps forward, but there is also a legacy of technology to support. The current lineup of technologies allows for both approaches for organizations to embrace at their own pace.

Where we are with the BI Workload in SharePoint 2016 – BI Focal #4

This past week, on March 24 2016, My co-host Jason Himmelstein and I had the privilege of hosting Kay Unkroth and Gregory Appel from Microsoft on our monthly BI Focal web show. Kay and Gregory are both Senior Product Managers at Microsoft. Kay is responsible for both the PowerPivot for SharePoint and SQL Server Reporting Services service applications, and Gregory is responsible for Excel Online in Office Online Server (or as we’ve come to affectionately call it, Excel Online On-prem). We titled the show SELECT From On-Prem WHERE version=’2016′. The recent release of SharePoint 2016 RTM, and the imminent release of Office Online Server and SQL Server 2016 make this topic particularly relevant.

What transpired was a frank, hour long (almost) discussion on the current state of the On-premises BI story from Microsoft. We discussed a number of burning topics such as

  • The implications for the deprecation of Excel Services
  • The new focus on SSRS as a BI platform
  • How SharePoint fits in to the new Microsoft BI vision
  • The status of PerformancePoint and Visio Services
  • Where Power BI fits into the on-prem picture
  • The upgrade story for 2016 BI, and version dependencies

I had a great time participating in this discussion, which was jam packed with great information. The recording of it is now available from IT Unity, or below.

As always, Jason and I would like to thank IT Unity for all of the logistical information for the show, and our guests who graciously donated their time.

What you need for Business Intelligence in SharePoint 2016

Over the past few weeks, I’ve put together a number of posts that outline the intricacies of setting up SharePoint 2016 with its BI workloads, in particular Excel, PowerPivot, and SQL Server Reporting Services. With the full release today of SharePoint 2016, I wanted to summarize these posts, and to provide some context.

The major change to the BI world is of course the fact that Excel Services is no longer included, its capabilities having been replaced by Office Online Server (OOS). The posts below discuss the implications of this change, as well as how to configure all of the BI features in the new platform.

Article Description
Rethinking Business Intelligence in SharePoint and SQL Server 2016 My take on the changes to on-premises BI in the Microsoft world, and what the implications are for the present and future
Adding Excel Services Capabilities to a SharePoint 2016 Farm How to Set up Office Online Server to support the services previously available in Excel Services
Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016 How to set up SharePoint 2016 and Office Online Server to support Excel workbooks with embedded PowerPivot data models
Using PowerPivot for SharePoint with SharePoint 2016 How to configure the PowerPivot for SharePoint 2016 service application
Configuring SSRS 2016 Integrated Mode with SharePoint 2016 How to configure SQL Server Reporting Services 2016 Integrated mode in SharePoint 2016
Integrating SharePoint 2016 with SSRS Native Mode How to configure SQL Server Reporting Services 2016 Native mode and integrate it with SharePoint 2016

Just a quick glance at the articles above will show a deep dependency on SQL Server 2016. For example, in prior versions of SharePoint, multiple versions of SSRS were supported on SharePoint. This is no longer the case with SharePoint 2016. To be clear, I am talking about the BI components (SSRS, PowerPivot for SharePoint) and not the core database server for SharePoint. SharePoint 2016 requires SQL Server 2016 versions of both PowerPivot for SharePoint and SSRS. This means that if you’re invested in Business Intelligence in SharePoint 2013, you’re going to need to wait for SQL Server 2016 before you upgrade in a production environment.

SQL Server 2016 is currently at the Release Candidate (RC0) stage, and its release won’t be that far off. You can get started today on your test migrations, knowing that the full release will likely be available by the time your testing is complete. The articles above were all written while using the CTP 3.3 version of SQL Server 2016.

Looking through the articles you’ll find a number of configurations, and requirements that line up with specific scenarios. Below is a quick guide to outline what is required to support what feature in the SharePoint 2016 BI space.

Feature Requirements
Excel workbooks connected to SSAS Data Sources Kerberos Constrained Delegation (KCD) between OOS and SSAS data source

OR

EffectiveUserName enabled on OOS Server(s)

OOS Server account(s) added to Admin list on SSAS server(s)

Connected Excel workbooks to Windows Authenticated SQL Server Data Sources KCD between OOS and SQL Server

Claims to Windows Token Service running on OOS Server with Network Service enabled

Connected Excel workbooks using stored credentials (Excel Services Authentication Options) Secure Store Service (SSS) credential created

OOS machine account added to SSS Members list

“AllowHttpSecureStoreConnections = true” set on OOS server if HTTP is used

PowerPivot enabled Excel workbooks SSAS PowerPivot Mode server available

SSAS PP Mode server added to BI server list on OOS Server via New-OfficeWebAppsExcelBIServer cmdlet

OOS Server account added to Administrators list of SSAS PowerPivot Mode Server

Automatic Refresh of PP enabled workbooks PowerPivot for SharePoint

Silverlight (client side)

PowerPivot Gallery PowerPivot for SharePoint

Silverlight (client side)

Excel files as a data source PowerPivot for SharePoint

PP4SP must have admin access on SSAS PP mode Server

KCD between OOS and SharePoint application

Claims to Windows Token Service running on OOS Server with Network Service enabled

External ODC file support
PowerPivot Management Dashboard
S2S Trust Configured between OOS and SharePoint
Power View reports SSRS Integrated mode

Silverlight (client side)

Power View in Excel
Power View with Excel as a data source
SSRS Services account must be added to the Admin group on the BI server

Silverlight (client side)

I’ll update this post if anything significant changes between now and the release of SQL Server 2016, but this should help those interested get up to speed today on Business Intelligence in SharePoint 2016.

Integrating SharePoint 2016 with SSRS Native Mode

Why on earth would I want to write an article on this topic? Surely, if I am using SharePoint and SQL Server Reporting Services, I should be running in in SharePoint Integrated mode, right? That’s certainly the message that I have been delivering for quite some time. However, the game has changed significantly with SSRS 2016. Last October, Microsoft outlined their reporting roadmap, and that roadmap included a significant investment in SSRS. The roadmap was very clear as to Microsoft’s intentions.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services is the solution, not SharePoint with Reporting Services, or PerformancePoint with Reporting Services, just Reporting Services. For several years now, the path to any new features in SSRS led through SharePoint Integrated mode. Features like Power View reports were only made available in Integrated mode as an example. While that was great for those invested in SharePoint, it presented an adoption issue for those that were not. This issue has prompted Microsoft to remove the SharePoint dependency, while still providing solid integration. In short, the goal for SSRS is to run with SharePoint, not on it.

In my opinion, this is all to the good. By making SharePoint integration pluggable, the product team can focus on one codebase instead of two, and spent more energy on features. This does however have some negative impact on administrators that will need to again manage two security models, but in the ideal world, it should be transparent to end users.

The immediate impact of this refocusing is that Native mode now receives new feature priority. If we look at the current state of SSRS 2016 (RC0 at the time of this writing), only a few of the major new features will be available in SharePoint integrated mode.

SSRS New Features

Native SharePoint Integrated
HTML 5 Based Rendering Engine

Customizable Parameters Pane

New UI for Report Builder

New Web Portal

Mobile Reports

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

KPIs

Pin to Power BI Dashboard

Render Power BI Desktop files

HTML 5 Based Rendering Engine

New UI for Report Builder

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

This new disparity is likely to leave some in the SharePoint world feeling left behind. The reality is that although this may seem like the case in the short term, Microsoft stated that “We will continue to support embedding of BI content into SharePoint”. For the record, that statement is open ended enough to include both SSRS and Power BI. The improvements to integrated mode in SharePoint 2016 are a testament to this support. It would have been just as easy to leave Integration mode in its previous state (like PerformancePoint). My view is that ultimately Native mode reports will work with SharePoint in much the same manner that current Integrated mode ones do. In fact, it’s possible to do some of this today – to embed Native mode SSRS Reports into SharePoint. That’s what the remainder of this article describes.

The ability to embed Native mode SSRS reports has actually been available since SharePoint 2003. It fell by the wayside after Integrated mode was introduced in SQL Server 2008 R2, but it has continued to be there. What is needed is a Native mode SSRS Server, and the Native mode SharePoint web parts.

Installing and Configuring SSRS Native Mode

Native mode SSRS is installed from the SQL Server media. It should be installed on a NON SharePoint server. Run the SQL Server installer, and eventually you will be taken to the feature selection screen.

Native mode SSRS installs as a SQL Server instance, and it is the only option necessary to install. The SharePoint add-in is only used for Integrated mode.

Once installed, it is necessary to run the Reporting Services configuration tool. The first step in configuration is to set up the web service URL.

Once the desired options are set, click Apply and the SSRS web service will be set up. Next, click on the Database node to configure the SSRS database. If the SQL Server database is installed on the same machine, you can use it, but you can use any SQL Server at your disposal. The only restriction is that the database engine must be at least the same edition level as SSRS (ie Standard vs Enterprise).

To create a new SSRS database, click the “Change Database” button and provide the database parameters.

Two databases will actually be created, one of them a Temp database. I recommend using the word “Native” or some other identifier in the name, particularly when both Native and SharePoint Integrated mode servers may be used. Complete the database creation process, and move to the Report Manager URL node.

Click Apply to create the SSRS Report Manager. The initial URL will always be based on the machine name, but once complete, you can click on the Advanced tab to add additional URLs. This is how you can add a Fully Qualified Domain Name (FQDN) to your SSRS server, which is strongly recommended if you will be integrating SSRS with Power BI. Power BI users will need to connect directly to the SSRS server to view SSRS reports, and this requires an FQDN.

There are other steps to be performed at this point, including Power BI integration and exporting the Encryption keys, but this is all that is necessary for basic configuration. You should now be able to navigate to your Report Manager URL and create reports. The next step is therefore to integrate them with Sharepoint.

Integrating with SharePoint

Native mode ships with a pair of web parts that allow SSRS web parts to be embedded into a SharePoint page. The web parts are embedded in an installable .cab file that can be found in the folder “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Reporting Services\SharePoint” where C: is the installation drive, and “130” is the major installation version for SQL Server (130, or 13.0 corresponds to SQL Server 2016). The name of the file is RSWebParts.cab. Copy this file to a SharePoint server in the farm, and from there, it can be installed from either PowerShell, or the gool ol’ STSADM command. With PowerShell, the command is:

Install-SPWebPartPack -LiteralPath “D:\Software\RSWebParts.cab” -GlobalInstall

Where “D:\Software” is the folder that the file was copied to. The corresponding STSADM command is:

STSADM.EXE -o addwppack -filename “D:\Software\RSWebParts.cab” -globalinstall

Unfortunately, I and many others have found that the version of the .CAB file distributed with SQL Server 2012 and above are incompatible with SharePoint 2013 and 2016 – the web parts fail to deploy. The good news (and the bad) is that the web parts are unchanged from SQL Server 2008 R2, and that version of the .CAB file will work with modern SharePoint. Of course, not everyone has a SQL Server 2008 R2 server lying around, so if you happen to need the file, I include it here:

RSWebParts.cab from SQL Server 2008 R2

Using the Web Parts

Once deployed, the two web parts, Report Viewer and Report Browser will appear under the miscellaneous section when a web part is inserted into a page. Report browser allows the browsing of reports on a server, and Report Viewer renders them. By connecting the two, it is possible to provide a highly interactive navigation of the report server right in a SharePoint server. However, editing the Report Viewer web part reveals that it is lacking some very fundamental capabilities.

Native Mode Web Part

Integrated Mode Web Part

The Native mode web part is missing all of the view control features that are available to the Integrated mode part, which means that when it comes to Native mode reports, you get what you get. However, more concerning is the fact that it is also missing parameters. There is no way to configure parameters for, or pass parameter values to Native mode reports embedded on a page.

Add to this limitation the fact that these web parts are approximately 10 years old – they were designed for SharePoint 2007. They are able to render the new chart types, but not through the new HTML renderer. These limitations make it very difficult to recommend their use, except in a few very specific scenarios.

Recommendations

So what is a Report driven SharePoint administrator to do? All of the cool new features are showing up in Native mode, but except in certain circumstances, there no really good way to embed those reports in Sharepoint pages. It seems a difficult question, but the reality is that these choices are not necessarily mutually exclusive. SSRS Integrated mode is getting many of the modernization improvements and continues to be a totally viable platform moving forward. If you want or need to take advantage of the new SSRS features like mobile reports, parameter pane customization, or Power BI integration, you can stand up a separate SSRS Native mode server, and even integrate it with SharePoint using the older web parts.

Taking this dual approach means that you’ll be well positioned to gradually move assets from Integrated mode to Native mode as the embedding story and capabilities improve.