Skip to content

Category: SharePoint

Understanding Office 365 Groups

When Microsoft Teams was announced at the beginning of November 2016, I posted an article that attempted to explain the different social networking products available from Microsoft and the advantages/disadvantages of each. Since then, it has become apparent to me that there continues to be a lack of understanding about what Groups are, what they bring to the table, and where they fall short. This post is an attempt to help clear up some of that confusion.

To begin with, Groups isn’t a product in an of itself, it’s an infrastructure. Specifically, an Office 365 Group is a specific type of group in Azure Active directory. That’s it. They have a few properties, and they contain members, but outside of Office 365 administration or Azure Active Directory admin, there’s really nothing to look at. What is unique about them is that the Office 365 services are becoming increasingly tied to them, and creating one of these groups will provision multiple artifacts in multiple Office 365 services.

The value of Groups is really in the workloads and services that they tie together. This is where it starts to get a bit confusing. The way that I see it, there are currently 9 workloads (team sites, file storage, group inbox, enterprise social, group chat, notebook, plans, calendar, and report workspace) offered by 6 different services (SharePoint, Outlook, Yammer, Microsoft Teams, Planner, and Power BI). Arguably, Group inbox, enterprise social and group chat could all be considered conversation spaces, but my earlier article makes the case for considering them separately. Also, Skype for Business is notable by its absence, but I consider Teams to be the logical successor to Skype for Business.

A summary of the different workloads, and the services that offer them can be seen below.

This is a description of the various workloads offered by the component services, and it’s relatively straightforward. The only overlaps (if my earlier assertions about the different types of social are accepted) are group inbox services being offered by both Outlook and Yammer. Since Groups created and managed by Yammer will be kept distinct from other groups, this shouldn’t be the source of too much confusion.

The picture gets a little murkier however when we talk about the way that users will interact with groups, which is through a client application of one form or another. All the constituent services have at least one client application that interacts with Groups, and several of these overlaps significantly. A full understanding of Groups includes an understanding of all the available clients.

SharePoint

When an Office 365 Group is created, a Modern SharePoint Team site, which is a SharePoint site collection gets created along with it. This site collection is home not only to the Group’s team site, but to its OneDrive file storage, and to its Notebook (via OneNote). The SharePoint home page is focused on site collections, with Group site collections being called out specifically. From here one can search for Groups, pin favourite Groups, access recently used Groups, and access Groups deemed important by the tenant administrators.

SharePoint has two different clients that touch Groups – the standard web interface and SharePoint mobile. As noted above, SharePoint supplies 3 of the core Group workloads so the SharePoint interface is inherently well integrated. In addition to the native interface, the SharePoint web part framework lends itself to further integration, and indeed there are already two Modern web parts that have emerged that tough other Group workloads. The Yammer web part, which is available today in first release brings enterprise social into the SharePoint interface, and the upcoming Power BI allows the embedding of reports into SharePoint pages.

Groups can be created and managed directly in the SharePoint interface, and group conversations are accessed though a Group conversations link. Now, this launches the Outlook Web App to access the inbox, but when Yammer Group integration is rolled out, it will launch into Yammer for Yammer managed Groups.

The SharePoint mobile application is full fidelity, and modern web parts work with it. Thus, the SharePoint mobile app has all the same touchpoints that the browser interface does with one exception. It is currently not possible to add or manage Groups in the SharePoint mobile app.

There is currently no integration of Group chat (Teams), Plans, or the group calendar in either the browser or the SharePoint mobile client.


SharePoint UI integration with Groups

Outlook

The Outlook web application was originally the only place to go to create Office 365 Groups. Management of Groups is therefore its strong suit and is provided natively. The Group inbox and the Group calendar are also both provided by Outlook (Exchange) and the web client reflects that. The Outlook clients are currently the only tools that allow these two workloads to be accessed natively. In addition to these native workload, the browser client provides contextual like to open the Group Team site, OneNote, OneDrive, and the Group Planner. There is currently no integration between the Outlook browser client and enterprise social (Yammer), Group chat (Teams) or Power BI workspace.

The rich Outlook client included in Office 2016 has almost full feature parity with the browser client. The only difference is that is does not currently provide links for opening the Group Team site, or Planner.

The Outlook mobile app, available on iOS, Android and Windows Mobile is a bit of an anomaly. This client does not integrate at all with Groups. Instead, the Outlook team has published an app on these platforms called “Outlook Groups”. Given that they are known as Office 365 Groups, this name can be a bit confusing. The Outlook Groups app provides native access for the Group inbox, Calendar, and OneDrive files. It will launch the mobile OneNote app for access to Group notes, and it even allows for Group management. It is the only mobile app that allows for Groups management.

Outlook UI integration with Groups

Yammer

Yammer has historically been a completely separate application, and its user interface reflects that. To date, there is no integration with Groups, but this work has been done, and it will be available shortly. An early build of the Groups integrated Yammer interface was recently demonstrated at the Microsoft Ignite conference in Atlanta.

Groups integrated Yammer client

The integration points can be seen in the right column of the UI. Initially, Yammer will integrate OneNote and OneDrive for notes and file storage respectively, and accessing the links will open the respective web applications. The “classic” Yammer files and notes will be maintained for a period and can be accessed at the top of the UI. In addition to files and notes, both Planner and the SharePoint Team site will be available directly from the Yammer interface. There is not integration at all with the Group Inbox, Group chat, Calendar or the Power BI workspace.

IT will be possible to create manage Groups and add/remove members directly from the Yammer interface. Creation of a Yammer group will spawn an Office 365 group, and while all operations will be performed in Yammer, they will be kept in synvc with the Office 365 Group. It should be noted that Groups that are created in the manner will be flagged as “Yammer managed” as opposed to “Outlook managed” and will be invisible to the Outlook clients. All the other clients will be able to see them however.

The Groups integrated mobile client has not been shown publicly yet, so we can only speculate on what it may contain. I suspect it will mirror the browser client, but for now, the only thing that is certain is that it will support enterprise social.

Yammer UI integration with Groups

Microsoft Teams

Teams is the new kid on the block. It is currently available in preview form, so this analysis may be incomplete. Microsoft Teams was built by the Skype product team, and given its ability to do 1:1 conversations, as well as textual, audio and video conversations, it should logically be seen as the successor to Skype for Business. What it brings to the table for Groups is a persistent semi-threaded chat interface. Although it only provides one of the workloads to Groups, it’s UI encompasses most of them.

The Teams client is quite rich, and it provides “sub-team” management. Every team gets at least one channel (General) and additional channels can be added at will. These channels are the containers for the semi-threaded discussions, and each channel also gets its own folder in the Groups OneDrive, as well as a section in the groups OneNote. Creating a channel provisions these artifacts automatically. Any one of these channels can be extended through tabs. Tabs are a way of including content that may be relevant to the channel, and that content can be dynamic. For example, a Power BI report can be added to a new tab and it will always be up to date, or through a third party, a Yammer Group conversation can be embedded as well. Finally, connectors can be employed to automatically add relevant content to a channel’s conversation interface as it occurs – a Twitter feed is a good example of this.

Teams channel showing the associated artifacts in OneDrive and OneNote

The fact that there is already a third-party tool for embedding Yammer conversations speaks to the extensibility of the Teams client as well. Extensibility options are available for tabs and connectors.

The integration of Teams with Planner is notable as well. As I previously wrote about here, the Teams client allows for multiple planner plans to be created within not only a single Group, but a single channel. These plans are NOT available through the Planner client UI, although the resulting tasks are. I would look for this to change in the near future, but that’s the way it works today.

The Teams client (both browser and desktop – they are virtually indistinguishable) has access to the widest set of Group workloads of any client currently. This is partly due to the fact that it is brand new, and as such, is the only client that has access to the Group chats. It has native access to Group management, file storage, group chat, notebook, plans and Power BI reports. It has links to the Group’s Team site, and through third party integration, it can embed enterprise social content. The only workloads that it does not currently integrate with are the Group inbox, and the Group calendar.

The mobile client is unfortunately vastly different. The only workloads that the mobile client works with today are Group chat (as expected) and Group files (from OneDrive. Given the importance of mobile to the modern team story, I would expect this to change. However, if the SharePoint mobile client had access to the Group chat, it could provide a viable alternative.

Microsoft Teams UI integration with Groups

Planner

There is very little integration between Planner and Groups. The Planner UI obviously offers native access to Group plans, but as mentioned in the Teams section, not all the plans – only the one associated twith the root Group itself. Each Plan also offers a link to access all of the files stored in the Group’s OneDrive, and that’s where the integration ends. There is no integration with the rest of the Group workspace. The Palnner browser client is also the only client available. Inexplicably, there is no mobile client for Planner.

Planner UI Integration with Groups

Power BI

Power BI makes very good use of Groups – Groups provides the optimal sharing option for Power BI. Each Group is provided its own Power BI workspace, which is a container for data sources, reports, and dashboards. All members of the group get access to all the assets contained within.

The Power BI browser client is aimed primarily at the use of the Power BI service, but does provide some integration with the other Group workloads. Groups can be created and members added from the Power BI UI (although they are referred to as Group Workspaces). Native access is obviously provided to all the Power BI assets contained within, and links are also provided to the Outlook browser client for access to the Group inbox and the Group calendar. Finally, Group OneDrive files are natively available for the storage of data sources. There is no integration with the rest of the workloads currently.

The Power BI mobile client is all about Power BI – it doesn’t integrate with any of the other workloads, aside from being able to use the Group workspaces themselves.

Summary

To summarize, the modern Office 365 Group provides the membership and access services to 10 separate workloads which are provided across six different products/services. This “Group workspace” is accessed through any of 14 different clients that provide varying levels of access to the different workloads/and services. The choice of client will depend heavily on requirements and will likely lead to a combination of clients based on capability and preference. At the moment, the most integrated browser client is provided by Microsoft Teams, and the most integrated mobile client is SharePoint mobile. A final summary is below.

9 Comments

Completing the Microsoft Reporting Roadmap

In the recent announcement outlining the SharePoint integration strategy on the SQL Server Reporting Services Team’s blog, there was a statement that was almost hidden that I think deserves more attention. The statement was:

“….in time, we aim to support web-based viewing of Excel workbooks in Native mode…”

This may not sound like a big deal – after all, we’ve been able to serve up Excel workbooks in a browser since Excel Services was initially introduced with SharePoint 2007. However, as per Microsoft’s Reporting Roadmap from October 2015, Reporting Services is their on-premises solution for BI report delivery. If an Excel workbook is to be considered a report, the SSRS absolutely should be able to do it. The roadmap defined four types of reports:

  • Paginated
  • Interactive
  • Analytical
  • Mobile

I tend to see there being two types of reports, Structured and Analytical. In the list above, Structured corresponds with Paginated, and the other 3 types are different subtypes of Analytical. They four categories do, however line up well with the different reporting technologies available.

Report Type File Type
Paginated RDL (Classic SSRS)
Interactive PBIX (Power BI)
Analytical XLSX (Excel)
Mobile RSMOBILE (SSRS Mobile aka Datazen)

The roadmap was primarily concerned with the future of SSRS, but SSRS is Microsoft’s stated report delivery platform for on-premises reporting. The platform for cloud reporting is of course Power BI. There is a third platform for the delivery of “Analytical”, or Excel based reports, and that’s Excel Online. On premises, it’s called Office Online Server, but it is the same technology. The three platforms and their capabilities are shown below.

SSRS

Excel Online/OOS

Power BI

Paginated

Yes

N/A

No

Interactive

Preview

N/A

Yes

Analytical

Announced

Yes

Yes

Mobile

yes

N/A

Yes

The technical preview of Power BI reports in Reporting Services is available for testing, which covers Interactive reports in SSRS, and the above statement indicates that there is a solution to support Analytic reports in SSRS as well. The Power BI platform does this already, and it is done by leveraging the capabilities of Excel Online. Given the fact that Office Online server provides the same capabilities on premises, it makes sense that it would be used by SSRS when the Excel workbook support is added.

It should also be noted that the above comparison shows Mobile reports being supported by Power BI. To be clear, Power BI does not support RSMOBILE files, but regular Power BI reports are inherently mobile and available through the Power BI mobile client. which is also how RSMOBILE reports are delivered to end users.

The Reporting Services team is clearly very close to completing the vision laid out over a year ago, in the Reporting Roadmap for on-premises users. If the goal is to have parity between on-premises and cloud platforms, the only thing remaining (apart from possible support of the RSMOBILE format) is support for Paginated reports in Power BI. There have been no statements made regarding this capability, but its absence is certainly notable.

Leave a Comment

The Future of Report Integration with SharePoint

Yesterday, Microsoft made official what many, including myself had been suspecting ever since the release of SQL Server 2016 – that SQL Server Reporting Services Integrated mode would not exist in the future. With the announcement, we now know the timeline of when that will happen. SSRS Integrated Mode will not be included with the next version of SQL Server. Instead, SSRS Native mode will be more tightly integrated with SharePoint for those organizations that use both products. As someone that has always approached Business Intelligence from the SharePoint point of view, I see this is a good thing.

This change is another step in the process of de-cluttering and uncomplicating SharePoint. This process started arguably with the move away from fully trusted code running on SharePoint, to the newer app, add-in and now SPFx development models that run with SharePoint. When Excel Services was removed from SharePoint in SharePoint 2016, with its capabilities moved to Office Online Server this process became obvious. A decreased dependency on SharePoint allows for simpler, more streamlined architectures, better options for upgrade management, and better, more targeted performance management.

SSRS SharePoint Integrated mode has been with us in various forms since it was first introduced in SQL Server 2005 SP2. The original goal was to simplify the integration of the two products, and to take advantage of SharePoint’s storage and authorization capabilities. The integration has always worked well, but the very fact that these two products were delivered by two different product teams on different media, often on different release schedules has typically led to a great deal of confusion. The SharePoint prerequisite for Integrated Mode leads to far too many SQL servers having SharePoint installed on them.

Managing SSRS in SharePoint Integrated mode requires a combined skill set to some degree as well, with knowledge of both SSRS and SharePoint. SharePoint administrators tend to be intimidated by SSRS, and SharePoint simply mystifies SQL DBAs.

The fact that the two different modes did not always maintain feature parity is another problem. PowerView and several other features are only available through SharePoint integrated mode. This results in entire SharePoint farms being created for the sole purpose of providing reporting features. Since those performing these installations are typically not familiar with SharePoint best practices, these farms tend to be unreliable. The latest release of SSRS 2016 contains a massive number of new features, but many of them in Native mode only, leaving the SharePoint integrated folks with a decision whether to favour features or integration.

A strategy that reduces the dependency of one platform on the other is therefore to everyone’s advantage.

The two operating modes also represent two different code streams for Microsoft to maintain. Given the finite set of resources that is any development team, resources must be spent on maintaining both of these streams that could otherwise be applied to features. A single code stream is simply more efficient.

Preventing the wholesale move to Native mode are several SharePoint integration features that have been employed over the years that are only available in SharePoint Integrated mode.

There has been a SharePoint Report viewer web part for SSRS Native mode since SharePoint 2003. The trouble is that while it does work, it is deprecated, and hasn’t been updated since SQL Server 2008 R2. It also doesn’t allow for parameter binding, or interface control. For all intents and purposes, it is an iFrame embed of a report. The web part that is available through integrated mode allows for parameter interactivity, and significant control of the look and feel. It has been widely deployed. Integrated mode also allows for the reporting of SharePoint list data, and the ability to publish reports to a SharePoint library on a schedule. These features are well utilized in the market today.

Power View reports (RDLX) built on top of SSAS tabular models, or Excel Power Pivot models also require Integrated mode. Compounding this is the fact that Power View requires Silverlight, which does not work in either the Chrome nor the Microsoft Edge browsers.

These integration features will need to be added to Native mode before it will be possible to fully abandon Integrated mode. The good news is that the announcement commits to doing just that. Report embed, Report viewer web part, and SharePoint library destination capabilities will all be added. For Power View reports, a conversion tool will be provided to convert from RDLX into Power BI Desktop (PBIX). A technical preview is already availably that demonstrated PBIX rendering in SSRS.

This announcement signals the end of SSRS SharePoint Integrated mode, but it does not spell the end of SSRS SharePoint integration. The single mode architecture should be more approachable, simpler, and more efficient. It’s a win all the way around.

3 Comments

SharePoint and Power BI – Better Together


Ever since 2007, SharePoint has included Business Intelligence amongst its core workloads. There have been a variety of approaches to the workload over the years, but today those core workloads include Excel Services/Excel Online, PerformancePoint, SQL Server Reporting Services Integrated Mode, and Power Pivot for SharePoint.

Power Pivot for SharePoint and Excel Services go hand in hand and can really be considered as one of the main pillars, leaving us with three. If we quickly examine these three pillars in SharePoint 2016, it’s pretty easy to spot an emerging trend. Excel Services is gone from SharePoint 2016, its capabilities being added to Excel Online. Excel Online connects to, but does not run on SharePoint. PerformancePoint still exists in 2016, but it has received precisely 0 new features – it is identical to the version in SharePoint 2013, and remains a part of product for legacy reasons. For all intents and purposes, I consider PerformancePoint to be deprecated. SSRS Integrated mode has been greatly improved in 2016, but contains nowhere near the improvements that the Native Mode version of SSRS has in 2016.

At the same time, the past year has witnessed the spectacular rise of Power BI. Power BI is clearly the focus area for Business Intelligence within Microsoft for cloud based BI delivery. Last fall the SQL team announced that on-premises customers were not being ignored, and that SSRS was the platform for on premises BI delivery They also sketched out a roadmap that showed both platforms being able to deliver the same type of reports. In June 2016, the team delivered on a portion of this vision with SQL Server 2016 Reporting Service.

So where does this leave SharePoint in the Business Intelligence ecosystem?

In my opinion, it leaves it right where it should be – as an integrating platform, and NOT as a runtime platform as it has been in the past. SharePoint provides in context BI by connecting content to reports, and providing dashboards connected to multiple sources. In 2016, SharePoint connects to Excel Online to deliver Analytical reports. Excel runs with SharePoint now, not on it. SSRS Integrated mode still runs on SharePoint, but the investments in Native mode are a clear indication to me that this will be the direction here as well. Unfortunately, Sharepoint has been lacking tight integration with Power BI.

The recent Ignite 2016 conference was the first public appearance of the Power BI web part.

Figure 1: Insert web part dialog with Power BI web part

The Power BI web part works with Modern Sharepoint pages and is based on the new SharePoint Framework (SPFx), which means that it is completely client-side. Why does this matter to us? The fact that it is completely client side means that it will work both in SharePoint Online and on premises. Initially, it will only work with SharePoint Online, but that is because the SharePoint Framework is currently unavailable on premises.

To use the new web part, first create or edit a Modern SharePoint page. The Modern pages support the new Modern web parts. Click on a “+” icon to open the insert part control (Figure 1). Once inserted, add the report URL, and the page. The report page should immediately render within the context of the SharePoint page.

Figure 2: Power BI Report page rendered within a SharePoint page

Since the web part is rendering client side, the consuming user obviously needs to have access to the report. This means that the source report must have been shared with them through Power BI dashboard sharing, or the report is in a group within which the consuming user is a member. This latter case makes the most sense given that all Office 365 Groups will have a corresponding Modern Team site. Embedding the report within group pages should “just work”.

The devil is of course in the details, and all of these details are not yet available, but Given the number of questions that I have received over the past year about Sharepoint/Power BI integration, I expect that its existence will come as welcome news. Over time I would expect to see it picking up support for parameters and the ability to work with individual report items (this is speculation, but it makes sense). It’s also not much of a stretch to see how SSRS could make available a Modern web part that worked in the same fashion with on premises SSRSs. That web part could conceivably work both on premises an Online, bringing SSRS to SharePoint Online for the first time.

SharePoint is still very much a platform for integration and for Business Intelligence content delivery. SSRS and Power BI will be the de facto reporting engines for on-premises and the cloud respectively, and Sharepoint will be the dashboarding/integrating platform for both environments.

15 Comments

A Simplified Method of Working with SharePoint Data in Power BI

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.

Before After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.

11 Comments