Forum Discussion

TCavins's avatar
TCavins
Helper V
1 year ago
Solved

Report Meta Data - name of SQL View etc

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. 

Need Help

14 Replies

  • collinq's avatar
    collinq
    Super User
    1 year ago

    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's avatar
      TCavins
      Helper V
      1 year ago

      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's avatar
        TCavins
        Helper V
        1 year ago

        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
        }
    }
}

         

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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's avatar
      TCavins
      Helper V
      1 year ago

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

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        1 year ago

        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