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