22:09
Get ALL SharePoint Sites & Subsites Metrics with Power BI
Christine Payton
Overview
This video demonstrates how to extract SharePoint site and subsite metrics, including views and last modified dates, into Power BI using OData feeds. It highlights the importance of using a service account for data access and explores the structure of OData feeds to identify useful data points. The tutorial walks through creating a Power BI query to gather site information, pivot the data into a usable table, and format it for reporting, including creating clickable links for site paths and categorizing sites as 'site' or 'subsite'. The presenter also previews potential future videos on extracting data about lists, libraries, and activities within SharePoint.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- SharePoint's backend OData feeds contain valuable data for reporting.
- A technique, shared by Jordan Murphy, allows querying sites and subsites from OData into Power BI.
- This enables tenant-level reporting, which is difficult to achieve through standard SharePoint admin tools.
- The initial step focuses on retrieving a list of all sites, which is crucial for subsequent queries.
Understanding how to access and report on SharePoint data in Power BI empowers administrators and stakeholders with insights into site usage and activity, which is otherwise hard to consolidate.
Getting a list of all SharePoint sites with their latest modified dates and view counts directly into Power BI for easier analysis and sharing.
- Data extraction requires read permissions on the accessed sites.
- Modern SharePoint often lacks explicit permissions for administrators on all sites, leading to security trimming.
- A dedicated service account with broad read access is recommended for comprehensive tenant-level reporting.
- Service accounts are best practice for data refreshes due to business continuity and security advantages over regular user accounts.
Properly managing permissions and using service accounts ensures that your data extraction is comprehensive, secure, and reliable, preventing data gaps and access issues.
Using a licensed service account, not regularly logged into by a person, to connect to SharePoint OData and pull data, ensuring all sites are accessible for reporting.
- The OData feed contains extensive information about lists, libraries, recycle bins, and activities.
- Data can be browsed by navigating through the API endpoints in a tool like Power BI's OData connector.
- Specific details like list versions, last modified dates, views, fields, and recycle bin contents are accessible.
- Activity data is available but can be complex to process due to its reliance on item IDs and potential for throttling in large environments.
Understanding the breadth of data available in the OData feed allows you to identify and extract specific metrics relevant to your reporting needs beyond just site lists.
Browsing the 'lists' endpoint to find information such as the number of versions, last modified date, and title for each list within a SharePoint site.
- Create a parameter for the tenant root URL to easily switch between different reporting scopes (e.g., single site vs. all sites).
- Connect to the OData feed using the root URL and the specific API path for sites and webs (STS_site, STS_web).
- Expand nested 'record' and 'table' structures within the OData response to access raw data.
- Transform the data by converting it to a table, adding an index column, and pivoting the 'key' and 'value' columns to create a structured dataset.
This structured query process transforms raw, nested OData into a flat, usable table in Power BI, forming the foundation for all subsequent analysis and reporting.
Using Power BI's 'Transform Data' feature to navigate through nested OData records, pivot columns, and filter for essential fields like 'Title', 'URL', 'LastModified', and 'Views'.
- Filter the dataset to include only relevant fields, removing blank or unnecessary columns.
- Correct data types, especially for date/time fields and numerical views, ensuring accurate calculations.
- Relabel 'content class' to more user-friendly terms like 'Site' or 'Subsite' using conditional columns.
- Filter out unwanted site types such as OneDrive personal sites, publishing hubs, and app catalogs using the 'web template' column.
Cleaning and transforming the data ensures accuracy, usability, and relevance for reporting, making the final Power BI visuals meaningful and actionable.
Creating a conditional column where 'content class' values like 'STS_Site' are renamed to 'Site' and 'STS_Web' are renamed to 'Subsite' for clearer reporting.
- Load the cleaned site data into Power BI for visualization.
- Create a table visual displaying key metrics like site title, template, views, and last modified date.
- Configure the 'Path' column as a Web URL data category to enable clickable links within the report.
- Apply conditional formatting to make the site path a clickable hyperlink, allowing direct navigation from the report.
Visualizing the cleaned data in Power BI transforms raw metrics into an interactive report that provides quick access to site information and usage statistics.
Building a Power BI table with site titles, recent views, and last modified dates, where the site path is formatted as a clickable URL.
- The current technique focuses on site lists, but functions can be created to query specific data across all sites.
- Future videos will cover extracting data on lists, libraries, and user activities.
- This approach can help identify top-used libraries, monitor activity, and manage list view thresholds.
- The extracted data can be published and scheduled for refresh in Power BI for up-to-date reporting.
This foundational technique opens the door to more advanced SharePoint reporting, enabling deeper insights into content management, user engagement, and potential performance issues.
Planning to create a future report that shows the most frequently used SharePoint lists and libraries across the entire tenant.
Key takeaways
- Leveraging SharePoint's OData feeds in Power BI provides a powerful method for tenant-wide reporting on site metrics.
- A service account with appropriate permissions is crucial for accessing all necessary data without security trimming.
- Understanding the structure of OData feeds allows for the extraction of diverse data points beyond basic site lists.
- Power Query in Power BI is essential for transforming raw OData into a clean, structured, and usable dataset.
- Data cleaning, type correction, and user-friendly relabeling are critical steps for accurate and insightful reporting.
- Configuring fields as Web URLs in Power BI enables interactive reports with clickable links for direct navigation.
- This technique serves as a foundation for more advanced reporting on SharePoint content, activities, and usage patterns.
Key terms
OData FeedPower BISharePoint Admin PortalTenant-Level ReportingService AccountSecurity TrimmingAPI PathSTS_SiteSTS_WebWeb TemplateData CategoryWeb URL
Test your understanding
- What is the primary benefit of using OData feeds in Power BI for SharePoint data?
- Why is it recommended to use a service account instead of a regular user account for accessing SharePoint OData feeds?
- How can you explore the different types of data available within the SharePoint OData feed?
- What steps are involved in transforming the raw OData response into a usable table in Power BI?
- How can you make the site paths in a Power BI report clickable links?