Streamlining Power BI Tenant Inventory Management Using a REST API Connector

Background & The Challenge

In one of my projects as a Power BI Administrator, a common requirement was to maintain a comprehensive inventory of tenant assets, including workspaces, reports, datasets, dashboards, and capacity metrics.

Historically, my team handled this using a legacy, multi-step extraction process:

• Data Extraction: Running manual PowerShell scripts to call various Power BI REST API endpoints.
• Storage: Dumping the raw JSON outputs into a SQL Server database.
• Analysis: Writing ad-hoc SQL queries against the database whenever we needed specific inventory information.
• Action: Using this queried data to perform quarterly clean-up tasks (e.g., deleting orphaned workspaces, updating capacity assignments, and removing unused reports).

The Pain Point

This legacy approach was highly inefficient. Every time we needed updated inventory data, an admin had to validate the PowerShell scripts, manually execute them to refresh the SQL database, and then write or run SQL queries to get actionable insights. The data was often stale, and the process was too reliant on manual intervention.

The Proposed Solution

To eliminate the manual overhead and the need for external database storage, I designed a streamlined solution: Consolidating the inventory tracking directly into a Power BI report using a Custom Connector.

Instead of pushing API data out to SQL via scripts, the solution pulls the data directly into Power BI:

1. Custom Power BI Connector: I developed a custom connector designed specifically to authenticate and call the required Power BI REST API endpoints (Workspaces, Datasets, Reports, etc.).
2. Direct Reporting: I built an end-to-end Power BI report that consumes data directly from this custom connector, visualizing all necessary inventory and capacity metrics in one place.
3. Automated Refresh: I configured an On-Premises Data Gateway connection to allow the dataset to refresh automatically on the Power BI Service.

The Value Add

• Zero Manual Intervention: No more managing or executing PowerShell scripts. The data stays fresh via standard Power BI scheduled refreshes.
• Self-Service Access: Whenever stakeholders or admins need inventory details for quarterly cleanups, they simply open the shared Power BI report.
• Actionable Insights: Admins can instantly identify unused assets, capacity bottlenecks, and workspace bloat without writing a single SQL query.
• Key highlights:
  🔐 Azure AD OAuth2 - no client secrets, no passwords
  📄 Full inline documentation inside Power BI Desktop
  🔄 Auto-pagination for large tenant environments
  ⚡ Works with both Desktop & On-premises Data Gateway

Some Screenshots of POC

I successfully built and tested this approach as a Proof of Concept (POC) end-to-end. I am sharing this idea with the Fabric community for others who might be struggling with tenant inventory management and looking for a more automated, native reporting approach.

Hopefully, This will be help lot of members who is facing similar type issues if Microsoft team can officially launch this connector so everyone can just use and do whatever they need. 

Quick Update:
I have created the custom conenctor for the Power BI as well as Fabic REST endpoints. As of not it covers...

    -> 56 Power BI REST API functions (Datasets, Reports, Dashboards, Pipelines, DAX execution & more...)
    -> 53 Microsoft Fabric functions (Lakehouse, Warehouse, Eventhouse, KQL & more...)

Shoutout / Special Thanks:
Boston Office D365 User Group www.youtube.com/@BostonOffice365UserGroup

Note:
AI was used to assist in formatting and refining this post for clarity.