Forum Discussion
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 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
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.
7 Replies
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 }
