Tag Archives: SharePoint Online

Use Power BI to Help Manage Your SharePoint Sites

Note – This article first appeared on April 12, 2017 on the Microsoft Partner Network

When it comes to Business Intelligence, SharePoint is most often used as a platform to access dashboards and reports. With the recent availability of the Power BI web part, Power BI joins SQL Server Reporting Services and Excel as a go-to reporting tool within SharePoint.

Occasionally, list data is used as a data source for these reports. This doesn’t work for large amounts of data, but for smaller lists, this is perfectly adequate. Given that Power BI has native connectors for both SharePoint lists and libraries, it is perfectly suited for this sort of task. Combining these two results in some interesting possibilities, as the following article demonstrates.

We work extensively with modern Groups in Office 365. Each group gets its own SharePoint site, and within that, its own OneDrive, or “Shared Documents” library. Depending on the usage of the group, the storage in that library can grow quickly, and it’s not always easy to spot where all the content is being stored. By building a Power BI report that uses the OneDrive as a data source, we can create a report of storage allocation by file and folder, and then show that report on the home page of the SharePoint site.

There are several steps to building this report. It all starts with Power BI Desktop.

Get the Data

To start with, we Launch Power BI Desktop, and Select “Get Data”. Then we select the “SharePoint Folder” file source, and enter the URL of the SharePoint site. Even though we are prompted for the URL of the folder, we must enter the URL of the site itself. The query editor can be used later to filter out any unwanted folders. Only user created document libraries and folders will be returned.

The query will return a number of columns that are irrelevant to this report, and they can be removed. We need to create a column for the URL to the files themselves. The attributes column can be expanded to get the size of any files in bytes. We also use the split function to split the folder path by the “\” delimiter which will allow us to create a folder hierarchy. Finally, we set the appropriate data types on columns, and give them user friendly names.

The scope of this article does not allow for a complete step by step walkthrough of the query editor, but the code below can be pasted into the advanced editor (after replacing the URLs appropriately).

let
  Source = SharePoint.Files("https://yoursharepointsiteurl", [ApiVersion = 15]),
  #"Removed Columns" = Table.RemoveColumns(Source,{"Content"}),
  #"Added Custom" = Table.AddColumn(#"Removed Columns", "Folder", each [Folder Path]),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "URL", each [Folder Path] & [Name]),
  #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Folder Path"}),
  #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","https://unlimitedviz.sharepoint.com/sites/Presentations/Shared Documents/","",Replacer.ReplaceText,{"Folder"}),
  #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Folder", "FolderBase"}}),
  #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Trim([FolderBase],"/")),
  #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom", "Folder"}}),
  #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"FolderBase", "Date accessed"}),
  #"Expanded Attributes" = Table.ExpandRecordColumn(#"Removed Columns2", "Attributes", {"Size"}, {"Size"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Attributes",{{"Size", Int64.Type}}),
  #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Size", "Size (bytes)"}}),
  #"Added Custom3" = Table.AddColumn(#"Renamed Columns2", "Size (KB)", each [#"Size (bytes)"] /1024),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Size (KB)", type number}}),
  #"Added Custom4" = Table.AddColumn(#"Changed Type1", "Size (MB)", each [#"Size (KB)"] /1024),
  #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"Size (MB)", type number}, {"Date created", type datetime}, {"Date modified", type datetime}}),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type2","Folder",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Folder.1", "Folder.2", "Folder.3"}),
  #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Folder.1", type text}, {"Folder.2", type text}}),
  #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Folder.1", "Folder"}, {"Folder.2", "Subfolder 1"}, {"Folder.3", "Subfolder 2"}})
 in
  #"Renamed Columns3"

Build the Report

When the Query is complete, we click on the load the data into the model. We don’t need to do a lot of model editing for this report, it’s relatively straightforward. There is only one table, and the Date Created field gives us enough time intelligence that we don’t need to create a date table. There are two edits to the model that I used that bear mention.

One thing that I wanted to show was the accumulation of storage over time. With the size of the file and the create date, I could show the total size that was added for a given day, month or year, but that doesn’t show the accumulation. To do that we need to create a calculated measure, “Cumulative Size”. The formula below calculates a running total of file size based on date:

Cumulative Size (MB) =
 CALCULATE (
  SUM ( Files[Size (MB)] ),
  FILTER (
  ALL ( Files[Date modified] ),
  Files[Date modified] <= MAX ( Files[Date modified] )
  )
 )

It’s not strictly necessary, but it’s convenient to create a folder hierarchy by dragging subfolder1 onto Folder, and then dragging in subfolder2 to the bottom of it. That allows all levels of the folder hierarchyto be managed as one.

Finally, we add our visual elements to the report. The report itself can be seen above. In this case, the Size by Folder chart uses the folder hierarchy as the x axis so that clicking on a data bar (while in drill down mode) will open a lower level folder. Marking the data category of the URL field will cause the report to display a clickable URL in any tabular visuals, and setting the “URL icon” property (in the Values section) of the table will display a link icon instead of the long URL. Doing this will allow the user to open any of these files directly from the report. The Growth Rate chart used the Cumulative Size calculated measure created above.

Embed the Report

Once completed, we publish the report into Power BI. It is important to select the correct workspace for this. Since we will be embedding the report into a SharePoint page, it is important to ensure that all viewers will have access to the report. By publishing the report to the same Power BI Workspace that is used by the SharePoint site in question, this will be automatic. In this case, we are reporting against the “Presentations” team site that is associated with the “Presentations” group, so we publish this report to the “Presentations Power BI workspace.

Once published, we need to get the embed URL for SharePoint. This can be determined by opening the report in Power BI, selecting File- Embed in SharePoint Online.

Once we have the URL, we navigate to the SharePoint site and edit the home page (note – the home page needs to be a modern SharePoint page). Once in edit mode we add a new web part, and select the Power BI web part. When prompted, we enter the embed code retrieved above. Once the page is published, all is complete.

Finally, the data source in Power BI will need to be set up to refresh on the frequency required.

With a few simple steps, we have not only gained insights into the storage patterns of our team sites, but we have made those insights available to all members of the site in a highly interactive fashion, without making them open another application.

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.