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.

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.