List All Data Source / Database / Tables/Views Used

[mangchaaBI]

Hi everyone,

I have multiple pBI reports created and I'm being asked by my team to list all datasource, tables, queries I used on my pBI reports

I usually connect to our SQL instance when creating the queries, so it would help a lot if there's a way to list all the queries I created into a single view, important information would be the datasource, the database and the table/view that was used in the query

Thanks again

[amitchandak]

@mangchaaBI , One option is to use power bi cmdlets

https://learn.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.data/get-powerbidatasource?...

How to use

https://learn.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps

You can also view all connections in the power bi service lineage view of dataset

[mangchaaBI]

hi @amitchandak this is the only result that I'm getting, am I doing it wrong? 

[gramc]

Hi, 

The connectiondetails is an array so you have to either use a hash table or unpack it. 

This example will do that. 

$datasource_array = @()
$pbidatasource = Get-PowerBIDatasource -DatasetId {INSERT ID}
        
            foreach($datasource in $pbidatasource){

                $datasource_row = New-Object psobject -property @{
                    "Name" = $datasource.name
                    "ConnectionString" = $datasource.ConnectionString
                    "ConnectionServer" = $datasource.ConnectionDetails.Server
                    "ConnectionDatabase" = $datasource.ConnectionDetails.Database
                    "ConnectionUrl" = $datasource.ConnectionDetails.Url
                    "GatewayID" = $datasource.GatewayId
                    "Datasoruceid" = $datasource.DatasourceId

                }
                $datasource_array += $datasource_row
            }

[Anonymous]

Works but VERY slow across 45,000 Datasets and 47,000 datasources. Also, to see all of yours, you need to add -Scope Organization but only if you are a Fabric Admin.

[gramc · Accepted solution]

The snippet is using an array operation that is O(n). If the amount of rows is a big number this will slow down to a crawl. Changing it to be a hash table should speed the collection up a bit. 

$datasource_hashTable = @{}

$pbidatasource = Get-PowerBIDatasource -DatasetId {INSERT ID}

foreach ($datasource in $pbidatasource) {
    $datasource_row = @{
        "Name"                = $datasource.name
        "ConnectionString"    = $datasource.ConnectionString
        "ConnectionServer"    = $datasource.ConnectionDetails.Server
        "ConnectionDatabase"  = $datasource.ConnectionDetails.Database
        "ConnectionUrl"       = $datasource.ConnectionDetails.Url
        "GatewayID"           = $datasource.GatewayId
        "Datasoruceid"        = $datasource.DatasourceId
    }
    $datasource_hashTable[$datasource.DatasourceId] = $datasource_row
}

You could also use the graph api and python (which has pandas), that might be even faster.

[Alex_BI_]

Hi @gramc,
Could you explain what this code is and how to use it.
At least a couple of sentences to understand the direction of the way.
Thanks!

[gramc]

hi, 

You can see the cmdlet documentation here: https://learn.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps,

You will need to install the Power BI cmdlets and log in to the Power BI service.

This script retrieves all connection details for a specified dataset. After you have logged on to the service and gotten the list of ids to loop through.