Forum Discussion

mangchaaBI's avatar
mangchaaBI
Helper II
3 years ago
Solved

List All Data Source / Database / Tables/Views Used

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 creati...
Need Help
  • gramc's avatar
    gramc
    2 years ago

    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.

gramc's avatar
gramc
Frequent Visitor
3 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    gramc
    Frequent Visitor
    2 years ago

    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_'s avatar
      Alex_BI_
      Regular Visitor
      2 years ago

      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's avatar
        gramc
        Frequent Visitor
        2 years ago

        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.