Is there a way to get the PowerBI SQL tables and queries being used via powershell?-CodePudding

I am trying to get the tables and the queries that are being used by a PowerBI file. So far, I am able to get all of the data sources using the REST api. But I am having trouble finding a way to get the queries and tables.

EDIT: I just want the tables and queries that the PowerBI file is utilizing. For example, if I create a powerbi file that utilizes the sales database then for a specific visual it uses the "Shirts" table with this query: select * from sales.shirts where color = 'red'. I want to be able to get the table and query.

CodePudding user response：

.pbix is, just like .xlsx, a zipped collection of other files. You can extract the contents, see what's inside and try to parse it. There are mostly json files (without extensions):

DataModel looks promising; however it seems scrambled (not plain json but some proprietary format).

To get around this, from PBI Desktop save report file as .pbit, and then after extracting the contents, you'll find DataModelSchema file that is in plain json, containing all the information you need.

Import the file:

$json = cat 'C:\tmp\pbit_extract\DataModelSchema' -Raw -Encoding unicode | ConvertFrom-Json

And get the queries:

$json.model.tables.partitions.source | fl

Result:

name       : Calendar-xxxx0000-*
expression : {let,     Source = Sql.Database("server", "database"),,     dbo_Calendar = Source{[Schema="dbo",Item="Calendar"]}[Data], in…}          
                                                                                                                                                                 
name       : Periods-yyyy1111-*
expression : {let,     Source = Sql.Database("server", "database", [Query="select top 10 * from dbo.Periods"]), in,     Source}

If you want to write a script for this task, it may be challenging to automatically convert pbix to pbit, but for now it seams more feasible than decoding DataModel file in pbix.