Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
@mangchaaBI , One option is to use power bi cmdlets
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
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
}
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.
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.