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.

How to Enable Unlimited Storage in OneDrive for Business

Last December, it was announced that OneDrive for Business users would indeed be receiving unlimited storage if they had a qualifying subscription. (Details on which subscriptions qualify for unlimited storage can be found in the original announcement here).

Furthermore, I understood from the announcement and the coverage around it that users would initially be enabled with 5 TB, and that if you needed more, you would have to call support and ask for it to be enabled. Presumably this was to discourage users from seeing the infinity symbol for available space, and immediately uploading the contents of their DVD library.

I had been watching my storage stats and checking every month to see if the 5 TB was yet enabled for my account to no avail. I was stuck at 1 TB. My wife also uses our tenant and is an active photographer with quite a few RAW files that she stores in OneDrive for Business. As an aside, she’s very good – you can check out her work at http://www.oliveraphoto.com. Last week, her storage exceeded 1 TB, and OneDrive for Business started complaining. It was time to do some digging.

As it turns out, my understanding wasn’t exactly correct. You are entitled to unlimited storage, but you will only be given the 5TB cap when you ask. You can ask anytime however. In order to get more than 5 TB, you ask for that too, but you can only ask when your storage is in the warning zone – close to 5 TB.

You might think that being Canadian, I’m fine with just asking politely, but patience is not my strong suit. The good news is that you can use the SharePoint Online PowerShell module to connect to your Office 365 tenant, and change the limit yourself. It’s not particularly easy though, so I’ll walk through the required steps, or at least the steps that I required.

1. Install the SharePoint Online PowerShell management shell

The SPO management shell is a PowerShell extension that allows you to connect to SharePoint Online and use PowerShell to perform administrative functions. It’s not installed by default, but it can be downloaded and installed from the Microsoft Download Center here. The odd thing is that it prompts you to choose from 2 different files, 2 for 64 bit systems, and 2 for 32 bit systems.

image

I’m not sure what the differences are aside from the bit level, but I grabbed the most recent 64 bit version and installed it.

Once installed, you must run the shell as an administrator, otherwise, it will fail to find the extension files. I also had all sorts of trouble running it on Windows 10 machines. After trying on 2 different ones, I gave up and installed it on a Windows 8.1 virtual machine, where it ran correctly.

2. Connect to your tenant with admin credentials.

From within the management shell, the first thing that you need to do is to connect to your tenant. You do so by running the Connect-SPOService cmdlet. The syntax is:

Connect-SPOService -Url https://youradmintenant.sharepoint.com -credential adminuseremailaddress

Neither one of the parameters is as simple as it may seem.

The –Url parameter is the administrative url of your Office 365 SharePoint tenant. Normally, it’s the standard SharePoint url with “-admin” appended on to the end of the first identifier. If you normally access SharePoint online with the url https://coolcompany.sharepoint.com, your admin url is https://coolcompany-admin.sharepoint.com.

The –credential parameter is also not quite what it seems. You need admin access to your tenant to run these command, and chances are If you are reading this, then you are. If not, you have to at least provide the credentials of an account that does have admin access. The credential is in the form of an email address, and you will be prompted  for a password when the command is run. This is where I ran into another difficulty.

If you have admin credentials to your tenant, it’s that much more important that your account is secure. One of the best things that you can do in that regard is to use multi-factor authentication. I do this, and have done for some time. Unfortunately, SharePoint Online doesn’t support multi-factor authentication.

Normally this isn’t a big problem, you can just register and supply an application password. Skype for Business still requires this as an example. Unfortunately PowerShell does not allow application passwords. There is no way around this problem.

I fortunately had access to an administrator account that does not use MFA, and I was able to provide that to connect successfully. If you do not, you’ll need to create one in your Office 365 tenant to do this.

3. Set the storage quota

The final step is to run the PowerShell command that actually sets your quota. The syntax of this command is:

Set-SPOSite -Identity https://yourmysiteurl -StorageQuota 5242880

The –Identity parameter is the URL of your MySite, which is where OneDrive is stored. the format usually incorporates your company’s normal SharePoint URL, adds a –my and your email, slightly altered. Therefore if your company name is “CoolCompany” and your email address is “joe@coolcompany.com”, then your MySite url is  https://coolcompany-my.sharepoint.com/personal/joe_coolcompany_com .

Finally, the –StorageQuota parameter needs to be 5242880 which corresponds with 5TB. I assume that you double it for 10 TB, but I haven’t been able to test that, as I haven’t uploaded enough to qualify for the next tier. You can only request storage increases in 5 TB chunks.

Once the quota has been successfully set, you should be able to see your new cap in the OneDrive for Business web UI. Just hover over the OneDrive for Business icon in your tray, right click and select manage storage.

image

The storage Metrics page will open and your storage allocation can be found in the upper right.

image

It’s not easy, but it’s worth it if you have a qualifying account.

SQL Server 2016–Which Edition Do You Need for Business Intelligence?

For the past several releases, SQL Server has come in 6 possible editions. Developer, Express, Web, Standard, Business Intelligence, and Enterprise. Developer, Express and Web are for specific workloads, which leaves Standard, BI, and Enterprise. The choice of which edition to use would seem to be obvious – the one named Business Intelligence. However, Enterprise contained all of the features that the BI edition did, and in many cases, wound up being a better choice from a licensing perspective. Standard mode also provided many BI capabilities, but not all.

The biggest difference (but not the only one) from a BI standpoint between Standard, and either BI or Enterprise edition was the support of the Tabular Mode in SQL Server Analysis Services. For those unaware, Tabular Mode is the engine behind PowerPivot, and increasingly importantly, Power BI. From a price standpoint the difference between Standard and either BI or Enterprise is quite significant. This has put the Tabular model out of reach for some small and medium sized businesses which is unfortunate, given that tabular is at the center of Microsoft’s future BI efforts.

SQL Server 2016 removes the BI Edition as an option, leaving us with a choice between only Standard and Enterprise. The biggest news in my opinion from a licensing perspective with 2016 is that Tabular Mode will now be supported in Standard Edition. This puts the tabular model within the reach of all organizations, and closes the licensing gap in the BI stack. This is fantastic news.

There are of course limitations with Standard mode. Tabular in Standard Mode is restricted to 16 GB of RAM, which may seem like a lot, but keep in mind that tabular is an in-memory technology. It’s possible to bump into this limit fairly quickly, but it’s a limit that serves the small/medium business space rather well.

PowerPivot for SharePoint also remains an Enterprise only feature. However, given the capabilities available in Power BI, and the upcoming rendering capabilities of SSRS, this may be less important than it previously was.

Given that it’s relatively simple to move from Standard to Enterprise (from a technology perspective), this approach allows organizations to get up and running, and then scale up if necessary. It removes that up front Enterprise cost barrier. It’s much easier to get budget for and Enterprise license when its value has already been proven.

Another difference between Standard and Enterprise in SSAS is that Standard mode does not support partitioning, perspectives or DirectQuery. DirectQuery allows for real-time analytical reports, which removes the cached data storage from the picture. All queries go directly back to the source. An explanation of partitions and perspectives is beyond the scope of this post, but if you don’t know what they are, the chances are that you don’t need them.

From an SSRS standpoint, the traditional differences between Standard and Enterprise are still in place. These include data alerting, data driven subscriptions, PowerView support  and scale out capability. All of the new features of SSRS 2016 are available in both Standard and Enterprise modes with one very notable exception. The new Mobile Reports are only available with Enterprise.

Mobile reports are the result of last year’s acquisition of Datazen, which has been fully integrated into SSRS. It allows on-premises SSRS servers to provide rich mobile reports on a variety of platforms. If your organization is using Power BI already, then you likely have a mobile solution, but if not, Mobile reports may fill that gap.

A complete summary of the differences between all of the different SQL Server editions can be found here. A quick PDF chart of what’s new in SQL Server can be found here.

In summary, both Standard and Enterprise editions of SQL Server 2016 are now suitable for use in business Intelligence solutions. The decision to move to Enterprise can now be based on scale and enterprise requirements, not on basic functionality. This, in my opinion, is all to the good. 

The State of Analytics in SharePoint and Office 365

After adoption of SharePoint or Office 365, one of the first things an organization will look for is some understanding as to how the product is being adopted, and what its impact on resource allocation is. Historically, options for reporting on SharePoint have been limited at best.

The Web Analytics Service application was introduced with SharePoint 2010, and relied on a series of connected Excel workbooks and a fairly Byzantine series of staging and reporting databases. It worked so well that it was removed from the product in SharePoint 2013. The Usage logs contain a rich set of information, and they are rolled up into the Usage database, but accessing the data or persisting it beyond a short time period required a fair bit of work.

There were also third party analytical solutions, but most of these came with a hefty price tag, and they focused on page views, embedding code on a page. This approach works well enough for web pages, but it doesn’t capture everything, for example document access though the .NET API. They’re therefore not always well suited to collaborative environments.

SharePoint in Office 365 was initially devoid of analytics, but some basic reports have been creeping in in recent months. With the new administration portal going live, these reports moved from the relative obscurity of the compliance center to the brand new report center, and were augmented by some additional reports.

With the release of SharePoint 2016, and the announcements made at the  Future of SharePoint Event on May 4 2016, we can see the additional areas where analytics are being introduced into the core product. At this point, it’s a good idea to step back and have a look at the Analytics landscape as it pertains to SharePoint and Office 365.

At the moment, the analytics offerings can be grouped into 4 major categories; tenant scoped, site scoped, document scoped, and Delve Analytics. Let’s have a look at each one in turn.

Tenant scoped

The tenant scoped reports are the aforementioned reports that are now available in the new Office 365 Reporting Center.

New usage reports for SharePoint OneDrive Yammer and Skype 1

There are a number of interesting reports in here that focus primarily on the tenant as a whole. How much OneDrive space users are using, Yammer message counts, Skype meetings, emails sent and received, etc. In addition, these reports can be interacted with to show four different time periods, 7, 30, 90, and 180 days. Year over year analysis is not available.

These reports will primarily interest administrators, and it therefore makes sense that they are only available in the administration center, where administrative permissions are required to access them.

Site Scoped

Site scoped analytics contain data that is of concern to site administrators. These users are more concerned with content usage than resource allocation. These analytics features were initially announced at the Future of SharePoint event on May 4 2016, and as of this writing, have not yet rolled out.

The initial rollout will focus on content consumption, visits to the site and document views

image

SNAGHTML1eec8862

SharePoint home page with activity - 100 percent

This is welcome data to beleaguered site administrators, and it will help to identify important content, and content that maybe could be pruned. While it will be initially rolling out to SharePoint Online, the good new is that on premises users will also be able to get this through the new Analytics service application.

In a similar model to the new hybrid search, the new Analytics service application called SharePoint Insights connects to Office 365 and delivers your on premises  usage data to the service – essentially everything that is kept in the logging database. From there, the service can act on it to do interesting thing. One of those interesting things will be to deliver content based activity reports like the ones seen above.

There are a few things to take note of about site scoped analytics. They are scoped to the site, not the site collection. They do not roll up into a master report, so each site must be visited in turn (they live in the “site contents” section) to see the results. As far as I’m aware, the data is only persisted for a short time (I have only seen 7 days), so time based analysis is not possible.

Document scoped

Document scoped analytics have been in the service for some time now, and the new document library exposes them. I call them analytics, but they really are just the activity stream for a document or a folder. The do offer insight, so we’ll stick with the term.

From a “new style document library, you select the information icon on the right to open up the information pane. Part of that information is the activity stream of the document. In the example below I have selected a folder.

image

It’s a welcome addition, and it is what it is. There’s currently no way to aggregate the data or to pivot on it focused on anything either than the document/folder

Delve Analytics

Delve Analytics is a new offering from the Office team that focuses on the user. It analyzes a persons communications and schedule to provide insights into their work experience, with measures like time spent in meetings, time spent in email, work life balance, etc.

Take back your time with Delve Analytics 2

Delve analytics doesn’t really belong in a blog post about SharePoint because it doesn’t analyze any SharePoint or OneDrive data, so I’ll keep this section short. For the moment at least, it is restricted to Exchange email data as a source.

Delve Analytics requires an Office 365 E5 license or it can be purchased separately. Unlike the rest of the analytics options here, there is an extra cost associated with it.

Summary

The analytics options available in Office 365 and in SharePoint have improved drastically, but are still in their infancy. Each of the approaches are targeted at different audiences (IT Pro, site admins, content authors, individuals). This approach is bot good and bad. Tailoring an approach to an audience means that the specific audience will be satisfied, but the different approaches can become somewhat disjointed. It depends on what is necessary.

Analytics at the moment are also limited to specific time slices, if time can be sliced at all and to specific dimensions/measures. This is no problem if recent activity is the only thing of interest, but if more fine grained time slices or year-over-year analyses are needed, then the out of the box approaches will fall short.

Finally, most of the reports focus on activity, there is very little information provided about the SharePoint or Office 365 inventory.

The good news in all of this is not only that Microsoft has made analytics a priority, but that all of its features in this area use publicly available APIs. this means that there is plenty of room for third party vendors to step in to fill gaps and to provide complete analytics focused solutions. In that vein, I’m very proud to announce that my company, UnlimitedViz will soon be releasing a product, tyGraph for Office 365 to do exactly that.

 

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.