Aroh's Microsoft 365 Musings: How to display Sharegate administrative dynamic reports via Power BI on SharePoint Online

After working in the SharePoint domain for a couple of years as SharePoint consultant , I am fortunate enough to help customers from many verticals such as education, utilities, banking, and Government sectors.

A common thing which showed me why SharePoint projects (in general ,all the non-projects) get delayed are as follows:

No proper requirement analysis from the key stakeholders as a partner. When project starts and new requirements come within the project which were not factored in the initial project plan. This resulted in change requests and depending on the change the timeline gets shifted.
No foresight in the SharePoint’s capacity planning in terms of SharePoint On-Premises environment. Please read my earlier blog about why SharePoint is slow for more details.
None or very limited SharePoint Governance.

It’s not a new buzzword but SharePoint governance has become far more critical in SharePoint Online environment.

In most of organization’s I have had worked, they have a little or no governance at all. SharePoint Governance Plan is most the important success factor.

When a SharePoint platform is given to end users, it starts out easy. However, they keep adding more content with no information architecture in place, it gets complicated. The concepts of term store, management metadata services, search service, user profile service could be new for end users and educating them is important.

In a nutshell, SharePoint Governance includes:

Information Management (Site planning, Navigation, Management metadata, search etc.
Application Management (Customization policy, Solutions, 3^rd Party)
Communication and training (communication plan, training plan, support plan)
Roles and Responsibilities (Team definition and responsibilities)

Please refer to this excellent blog about SharePoint Governance.
Without governance it would be like a city without any authority roles and guideline as illustrated below:

Figure 1 Courtesy: Sharegate

To put the context with perspective in relation of the SharePoint governance, I had to worked for a client who had absolutely zero SharePoint governance. They asked us to display site collection quota on one of the site collections and a report in excel of folder sizes to end users every month end so that end users can clean the unnecessary files on their SharePoint 2013 On-Premises environment.

I created Server Object model to achieve this in my blog.
Furthermore, we had to give site access report via PowerShell who accessed the SharePoint site monthly in manual way.

Now, if they had 30 site collections and number of folders of each site collection the effort would be much more.

In the SharePoint Online context, it would be much more complicated as we have any access to SharePoint app model to manipulate the folder size using CSOM and REST.

Sharegate is one of the best tools that I have come across for this very purpose. It has in built reports permissions report which graphically shows which use has what permissions matrix for sites (or OneDrive), site collection with basic details, external users and many more.

In new version of Sharegate, you can easily schedule tasks every day, every week, every month etc. which makes SharePoint administrator’s job much more simpler without PowerShell.

In this blog post, I will detail steps to generate SharePoint administrative reports using Sharegate and display this report on the SharePoint Online site.

Step1: Open Sharegate desktop client (version 5.17.1.0), navigate to reporting section and click on Site with basic details as shown:

Step2: Select the sites to be included in the report and click on “Run” button.

Step3: Verify the results and click on Export button.

Step4: Save the file to the desktop.

Step5: Microsoft has given us a FREE business intelligence tool called Power BI. Download to your desktop as client.

Step6: From Home tab, click Get Data and locate Excel option.

Step7: In the Navigator, the results page has to be adjusted for Size and Date. Click on the Edit button.

Step8: In the Title column, remove blank rows, in the Size column, click on the “Replace Values” option and Replace value MB to blank as shown:

Step9: Do the same process for the “Last Modified Date”, select the Data Type, Date field.

Step10: Click on the Close and Apply,

The Power BI report is saved as .pbix format

Step11:

Now, on right side of Power BI client, we can see Visualizations and Fields that we selected in above steps. Select the Multi-row card visualization pane