December 26, 2017

How to use Power BI in the context of SharePoint?

1030 Views

Who should use PowerBI?

Anyone who knows the pain of presenting data in the form of charts/graphs, with added complexity of dynamic filters.

However, if you are a consultant, trust your skills to link even oranges to iPhones…if you ask me how, I’d say iPhones are from Apple, apples and oranges are fruits…any further explanation required :D ?

How to understand PowerBI?

At the minimum all you need to know is that each set of raw data/spreadsheet in PowerBI is known as a Field and its representation is termed Visualization.

What is the link between SharePoint and PowerBI?

The first use of PowerBI that you can see in your SharePoint platform is for a health check or maintenance of your SharePoint environment though not much without the help of the SharePoint admin who would run a few reports on the SharePoint farm.

Isn’t this blog relevant to the IT team?

Yeah, IT is always the first team to be blamed if anything goes wrong 😊. Well, while the IT team is diligently working towards implementing SharePoint governance and maintaining organizational security, I bet you’d enjoy maintaining your own set of site(s)/site collection(s) once you have the PowerBI reins, though still with the organizational SharePoint admin support.

How is PowerBI visualization different from other tools?

I’ve used very basic charts and analysis in this article to give a glimpse of how this application can be used, though my data analysis is on the entire SharePoint farm with 3 web applications, to show the complexity of data that can be handled, at the same time making it appealing to readers.

The explanation of the data set is continued in the following sections.

Content Freshness – Count by Year

The freshness of content plays a vital role in picking sites/subsites to migrate. Hence, a Pie Chart is a preferred representation of content freshness information owing to display of count of sites/subsites along with the percentage.

Data Set: All SharePoint Items

  • Legend – Last Modified Year (This is Year extraction from Item Last Modified)
  • Values – Count of SP Web URL

Content Size by Web App and Year

Content size is another deciding factor to estimate duration and effort of migration. Hence, similar to Site Freshness – Count by Year, a Pie Chart suits the representation of split of content size over multiple web apps by year.

Data Set: All SharePoint Items

  • Legend – Web App URL
  • Details - Last Modified Year (This is Year extraction from Item Last Modified)
  • Values – Total Item Size (in MB)

InfoPath Forms

InfoPath forms require a post migration fix, that may also be dependent on code behind the form. As multiple InfoPath forms can exist in a list or a document library, grouping the count of InfoPath forms by list/document library URLs in a Stacked Bar Chart representation helps to view distribution of InfoPath Forms by last access of list/document library. An additional Slicer for Code Behind (Yes/No) filters out lists/library URLs where such forms are used.

Data Set: InfoPath Forms

  • Axis – Last Modified Year
  • Legend – List/Document Library URL
  • Values – Count of Form URL

Workflow Association

Workflows can be split as below:

  • Globally Reusable – created on a SP farm level
  • Reusable – created on a site collection level
  • Non-Reusable – created on Content Type or List/Library. These can be further divided into:
    • OOB
    • SPD

Reiterating that FastTrack migration might not support an IA redesign, there may be events where Globally Reusable and Reusable workflows may require to be recreated multiple times depending on the SP web containing the list/library associated with them. Similar can be the case with workflows associated with content types if the content types are used on SP Webs that don’t maintain the same hierarchy or are moved to other site collection targets upon migration.

While the actual number of recreations is still a figure that’s awaiting the approved IA redesign, a Stacked Column Chart can be created to check the distribution pattern of workflows on lists over years.

Data Set: Workflow Association

  • Axis – Last Modified Year (This is Year extraction from Last Modified Date)
  • Legend - IsReusable
  • Values – Count of List URL

Distribution pattern over time of Non-Reusable workflows can be represented using Pie-Charts with details from the same field as below:

  • Legend – Last Modified Year (This is Year extraction from Last Modified Date)
  • Details – Type of Workflow
  • Values – Count of List URL

What does the raw data look like?

As in my case with the provided examples, I’ve created some content inventory in the form of spreadsheets for my SharePoint application on the farm that serves as input to the PowerBI application.

The following tabulation details all report data with respect to reference by name in Power BI.

Data

Details

All SharePoint Items

This table lists all items from all list/libraries across all web apps, and displays the following details corresponding to each item:

  • Web App URL
  • Site Collection URL
  • Site Collection Admin
  • SP Web URL
  • SP Web Name
  • List/Library Name
  • Item URL
  • Item Length – has values only for libraries
  • Item Type
  • Item Size (in MB)
  • Item Last Modified – Make sure UTC format is used to represent date-time in mmddyyyy hh:mm
  • Item version

P.S.: Some basic filtration is done to remove all system list/libraries before data is loaded in Power BI

InfoPath Forms

Report has the following details for all lists/libraries that have InfoPath forms:

  • Site Collection URL
  • SP Web URL
  • List/Document Library URL
  • InfoPath Form URL
  • Item Last Modified Date
  • Item Count (of list/ library)
  • Code Behind (Yes/No)

Site Collection Freshness by Year and Date

This is an extract from All SharePoint Items report to detail the following:

  • Site collection URL
  • Site Collection Admin
  • Last activity date on the Site Collection - this data is an extract from the latest date from ‘All SharePoint Items’ data

SP Web Freshness by Year and Date

This is an extract from All SharePoint Items report to detail the following:

  • SP Web URL
  • Site Owners
  • Last activity date on the SP Web – this data is an extract from the latest date from ‘All SharePoint Items’ data

Workflow Association

This report includes site collection, SP web and list/document library URLs along with list/library name, item count and last modified date with the following workflow information:

  • Scope – list/content type on which workflow is applicable
  • List/Library URL
  • Content Type Name
  • IsReusable – True/False
  • Workflow Name and Description
  • Type of workflow – OOTB/SPD
  • Last Modified Date – Data in this column is created before loading data in Power BI field with spreadsheet lookup from extracting list/library URL from Item URL of All SharePoint Items report and List URL of Workflow Association SMAT report

How to link data?

PowerBI is smart enough to link different data sets based on uniqueness of columns. The link between different datasets is termed as Field Relationship, which is nothing but a connector between two fields. However, if you don’t desire the default relationships, you could also set up your own.

Since a lot of data filtration depends on established relationships, a few salient features to build fields for setting up relationships are listed below:

  • Two fields can be related with only one column
  • The linking column must have unique values in at least one of the two fields
  • The Cross Filter Direction should be selected as ‘Both’ to allow either way filtration for the two fields

Since this guide is simplistic in nature, the following table helps to establish some basic relationships between various data sets that can be used to enhance level of filtration to check impact and complexity.

From: Table (Column)

To: Table (Column)

Cardinality

Cross filter direction

All SharePoint Items (SP Web URL)

SP Web Freshness by Year and Date (SP Web URL)

Many to One (*:1)

Both

InfoPath Forms (SP Web URL)

SP Web Freshness by Year and Date (SP Web URL)

Many to One (*:1)

Both

Workflow Association (SP Web URL)

SP Web Freshness by Year and Date (SP Web URL)

Many to One (*:1)

Both

Is there more to know?

With the myriad of visualization patterns and filters that Power BI offers to analyze data, it would be unfair to advocate only from the aforementioned patterns for analysis. However, this analysis aims to provide a fair estimate of count and pattern that can further be exploited.

Until my next article where I’ll demonstrate how to get started with the application, PowerBye-Bye!

Leave a Reply

Your email address will not be published. Required fields are marked *