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/tab...
Need Help
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
        }
    }
}