Integrate SharePoint Data into Power BI

Jul 11, 2024

Integrate SharePoint Data into Power BI

Introduction

  • Presenter: Jordan Murphy
  • Topic: Technique to import SharePoint data into Power BI for tenant-level reporting
  • Purpose: Allows SharePoint admins to create accessible reports for IT managers or site collection administrators using row-level security

Key Points

OData Feeds in SharePoint

  • Contain valuable data like modified dates, views, etc.
  • Jordan's query enables retrieval of sites and subsites
  • Essential for tenant-level reporting

Required Permissions

  • Need read access to run queries
  • Service accounts recommended for full access to all site data
    • Licensed accounts with relevant permissions used only for data refresh
  • Ensures business continuity and security

Value of Site Lists

  • List of sites contains key data (paths, views, modified dates, etc.)
  • Create Power BI reports
  • Useful for both tenant-wide and single specific sites

Setting Up in Power BI

Parameters and OData Feed

  • Create parameter for tenant root URL
  • Connect to OData feed with advanced settings
  • Includes paths and queries for content classes: STS_Site and STS_Web
  • Return subsites for more detailed reporting

Data Transformation

  1. Expand Records: Expand multiple levels to access required data
  2. Convert to Table and Index Column: Necessary for correct data formatting
  3. Filter Required Fields: Select relevant fields (author, description, modified time, views, etc.)
  4. Pivot Column: Transform data to table format for Power BI
  5. Set Data Types: Ensure correct data representation (numbers, dates, text)

Additional Data Handling

  • Filter out unwanted sites (e.g., OneDrive, app catalog, etc.) using the web template column

Creating Reports in Power BI

Setup Query for API Browser

  • Connect to root site and use similar steps as with the OData feed to explore data

Construct Report Page

  • Use site data to create visual elements like tables, conditional formatting, and more
  • Verify and label columns for better readability (e.g., relabel content class)

Additional Customizations

  • Conditional columns and filtering options for better report clarity
  • Example: Filtration based on site/subsite

Final Report Features

  • Create interactive tables and charts
  • Allow row-level security for site-specific data visibility
  • Publish and schedule auto-refresh for up-to-date reporting

Future Videos and Updates

  • Detailed explanations on retrieving specific data (e.g., list and library info, activity data)

Conclusion

  • The presented technique enables comprehensive SharePoint data analysis using Power BI
  • Stay tuned for more in-depth tutorials on specific aspects of SharePoint data usage