Report Meta Data - name of SQL View etc

[TCavins]

Is there a way to automatically retrieve apps/workspaces, reports, datasets and the underlying name of the table or view in SQL Server?  I need a way for our documentation to know which SQL views/tables are used in which reports and where those reports are located.

I've tried Powershell Get-PowerBIDatasource but it doesn't return a value. 

[Poojara_D12]

Hi @TCavins 

Yes, it is possible to automatically retrieve metadata that links Power BI apps, workspaces, reports, and datasets to the underlying SQL Server tables or views, but it requires a combination of tools and APIs, as there’s no single command that provides all this information out of the box. The Power BI REST API is your primary tool for programmatically retrieving workspaces, reports, datasets, and their metadata. However, to identify exact SQL Server objects (tables/views) used in a dataset, you need to dig deeper—typically by extracting the data source and M code (Power Query) or the data model. For datasets using Import or DirectQuery mode, you can use the GetDatasources and GetDatasetToDataSource REST API endpoints, but these only return high-level connection info (e.g., server and database), not the actual table or view names. To go further, you can connect to the dataset using XMLA endpoint (enabled for Premium or PPU workspaces) and use tools like Tabular Editor, DAX Studio, or TOM (Tabular Object Model) scripts in PowerShell or C# to extract the model metadata—including table names, DAX queries, and sometimes even SQL views referenced in M queries. The reason Get-PowerBIDatasource might not return values is because it often depends on the workspace type and permissions, and may not expose detailed model-level metadata. For complete documentation automation, consider building a pipeline that uses REST APIs to map workspace/report/dataset IDs and XMLA queries to extract the detailed schema lineage from the models—this gives you the clearest picture of which SQL views or tables are used in which reports and where they reside.

[TCavins]

@Poojara_D12 We do not have a premium workspace. Does this alter anything that you mentioned?

[v-lgarikapat]

Hi @TCavins ,

Thanks for reaching out to the Microsoft fabric community forum.

@Poojara_D12 ,

@collinq 

Thanks for your prompt response

Even without Premium, you can still gather useful metadata from your Power BI environment using a mix of techniques. First, by using the REST API under your own account, you can list the workspaces, reports, and datasets you have access to, and check which SQL Server each dataset connects to though it won’t tell you the exact table or view names. Since you don’t have XMLA access, the next best move is to open the PBIX files (if you’re allowed to), check the table names in the Model view, and dig into the Advanced Editor in Power Query to see the M code, which often includes the actual SQL views or tables used. Finally, on the SQL Server side, you can run a query against INFORMATION_SCHEMA.VIEWS to pull view definitions, and if your team uses consistent naming or tags in those views, you can start connecting the dots between Power BI reports and their underlying SQL sources

We truly appreciate your continued engagement and thank you for being an active and valued member of the community. If you’re still experiencing any challenges, we’re more than happy to assist you further.

We look forward to hearing from you.

Best Regards,

Lakshmi 

[TCavins]

If I upgrade my account to Premium Per User, would I then have access to the XMLA? I think I'm at a dead end using Powershell to get the actual SQL views/table names as everything I try returns blank data.

[v-lgarikapat · Accepted solution]

Hi @TCavins ,

XMLA Read/Write Access ,  You can connect to your Power BI datasets using tools like Tabular Editor, SSMS, or even PowerShell with TOM (Tabular Object Model).

Direct Access to the Semantic Model: This lets you inspect and script out metadata, including:

Table names

Column names

DAX measures

Relationships

Partitions and source queries (including SQL views, if used)

For Ref 

Solved: PowerBI Premium Licensing / XMLA / Access - Microsoft Fabric Community

Power BI Premium Per User - Microsoft Fabric | Microsoft Learn

Announcing the general availability of read/write XMLA endpoints in Power BI Premium | Microsoft Pow...

Best Regards,

Lakshmi.

[TCavins]

Thank you.  I do not think this will be cost affective as I'm looking at trying this out for all of our existing apps and we'd have to make everyone who accesses them a premium user as well.

[v-lgarikapat]

Hi @TCavins Thanks for confirming.

Best Regards,

Lakshmi.

[v-lgarikapat]

Hi @TCavins ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[TCavins]

I've been on PTO and will continue looking into this this week.  I've tried various options in Powershell before but I'll give this a try as well.

[v-lgarikapat]

Hi @TCavins ,

Thanks for the update. Let me know how it goes when you try it

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @TCavins ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[collinq]

Hi @TCavins ,

There is no way to get this directly.  But, you can work your way there with PowerShell I believe.

After you know which report you want, you can use the Get - PowerBIDataset for each report in the workspace and select the items that you want to have exported to your output file (like "dataset name" and "sserver" and "database" and "connection" using Get -PowerBI Datasource and then you can manually parse out the M queries.

[TCavins]

@collinq @v-lgarikapat 

I'm using the following code but I don't know how to break down the datasource to get information.

Install-Module -Name MicrosoftPowerBIMgmt

Connect-PowerBIServiceAccount

Login-PowerBI

$Workspace = Get-PowerBIWorkspace –All

$DataSets =

   ForEach ($workspace in $Workspace)
    {
    	Write-Host $workspace.Name
    	ForEach ($dataset in (Get-PowerBIDataset -WorkspaceId $workspace.Id))
        {		
        	[pscustomobject]@{
            		WorkspaceName = $Workspace.Name

            		WorkspaceID = $workspace.Id

            		DatasetName = $dataset.Name

            		DatasetID = $dataset.Id

	    		DataSource = Get-PowerBIDatasource -DatasetId $dataset.Id
            	}
        }
    }

   $Dir = "C:\pbi_test.csv"

   $DataSets | Export-Csv $Dir -NoTypeInformation -Encoding UTF8

The datasource output from the code above gives me this. Any advice on how to get into these values to see the source?

DataSource

Microsoft.PowerBI.Common.Api.Shared.Datasource

Microsoft.PowerBI.Common.Api.Shared.Datasource

Microsoft.PowerBI.Common.Api.Shared.Datasource

Microsoft.PowerBI.Common.Api.Shared.Datasource

System.Object[]

System.Object[]

System.Object[]

[TCavins]

Made progress with the DataSource but fields I want are blank.

Connect-PowerBIServiceAccount

#Get all test workspaces
$workspaces = Get-PowerBIWorkspace

#loop through contents to get SQL sources

foreach ($workspace in $workspaces) {
    $datasets= Get-PowerBIDataset -WorkspaceId $workspace.Id

    foreach($dataset in $datasets) {
        $datasources = Get-PowerBIDatasource -DatasetId $dataset.ID

        foreach ($datasource in $datasources) {
            $output = "Workspace: " + $workspace.Name + "`n" +
"`tSemanticModel: " + $dataset.Name + "`n" +
"`tServer: " + $datasource.ConnectionDetails.Server + "`n" +
"`tDatabase: " + $datasource.ConnectionDetails.Database + "`n" +
"`tKind: " + $datasource.ConnectionDetails.Kind + "`n" + 
"`tPath: " + $datasource.ConnectionDetails.Path + "`n" + 
"`taccount: " + $datasource.ConnectionDetails.Account + "`n" + 
"`tUrl: " + $datasource.ConnectionDetails.Url + "`n" + 
"`tConnection: " + $datasource.ConnectionString + "`n"
            Write-Output $output

            #time delay
            Start-Sleep -Seconds 2
        }
    }
}