What is the complete output of Get-PowerBIDatasource in Powershell?

[pborah]

Context- I'm a workspace admin and I want to programtically export an inventory of all the datasources used in our tenant. I'm trying to create a script that will loop through each workspace, get the datasets in there, and get the datasources for each of those datasets. 99% of our reports are powered by SQL and my script is only concerned with those reports.

I'm almost there but I'm stuck at the last part where I want to retrive the SQL tables and views besides server and database names. I've gone through the documentations and can't seem to find anything on what Get-PowerBIDatasource actually outputs. With some research, I was able to determine that some of the outputs are - ConnectionDetails.Server, ConnectionDetails.Database, etc. Does the output hashtable contain table/view information as well? 

In the service I know that lineage view or even gateways do not have that info either and just contain connection string upto database. So am I right in assuming that getting to the root will involve looking into the report (definition) itself somehow? Or is it because I am not a gateway admin, I can't see tables and views for any of those parent nodes in lineage?

I'd also appreciate it if you can point me to some further documentations specific to the output of Get-PowerBIDatasource connection details.

Thank you.

[lbendlin]

You need to run DMV queries for that, against expressions and partitions.

I'm a workspace admin and I want to programtically export an inventory of all the datasources used in our tenant

You need to be tenant admin to see all workspaces and their semantic models and their data sources.

[pborah]

@lbendlin Are you talking about the final bit to retrieve table and view information? Because I can most certainly get all info upto database level for every workspace in our tenant. The workspace admin AD group is added to every single workspace in our tenant as organizational policy. You may be correct in situations when a workspace admin isn't added to every workspace in tenant (which wouldn't make sense) but such is not the case for us. Below is my code and it returns everything upto server and database flawlessly-

Connect-PowerBIServiceAccount

#Get all test workspaces
$workspaces = Get-PowerBIWorkspace -Filter "contains(tolower(name),'test')"

#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 + "`tSemanticModel: " + $dataset.Name + "`tServer: " + $datasource.ConnectionDetails.Server + "`tDatabase: " + $datasource.ConnectionDetails.Database + "`n"
            Write-Output $output

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

[lbendlin · Accepted solution]

The workspace admin AD group is added to every single workspace in our tenant as organizational policy.

ouch.  then yes - your access level should be sufficient. just run the DMVs against each of the workspace's semantic models.

[pborah]

@lbendlin @Anonymous I've been instructed to abandon the DMV query route because something else is in the works on the side of our infrastructure team. Regardless I trust your expertise in that this is the way to pry open a data model and look inside it as I did my own research. So I'm accepting this as a solution.

[lbendlin]

What do they have "in the works"? Scanner API? Purview?

[pborah]

@lbendlin I wish. A very tightly controlled SPA. Even purview is blocked by our internal firewall even though it's Microsoft. Out of my control. Lol.

[pborah]

Thank you, I'll give it a look. And I know. In an ideal world, I'd be a PowerBI admin at minimum with more access and authority. We're exploring a service principal account to be able to do this type of thing. We'll see where it lands.

[Anonymous]

Hi,@pborah 

Can you tell me if your problem is solved? If yes, please accept lbendlin's reply as solution.

Best Regards,

Leroy Lu

[pborah]

hi @Anonymous not yet. Will post a reply if successful. Thank you.

[Anonymous]

Hi, @pborah 
 

Thanks for the quick reply.
 

Looking forward to your latest reply.
 

I wish you all the best in your life.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu