1

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.

Alex Jokerst
  • 35
  • 1
  • 6
  • To get tables use getschema (see https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=dotnet-plat-ext-7.0&force_isolation=true) get schema is a query to database the returns the databases, tables, fields of the database. – jdweng Jan 04 '23 at 15:02
  • Sorry... I should have been more specific. I meant tables and queries that are being utilized by the Powerbi table. Not all of the tables in the database. I'll edit my question. – Alex Jokerst Jan 04 '23 at 15:29
  • The reporting tool I use most stores report definitions and data models as xml (text). So if I need to know which reports use a certain table or column to identify reports to use to test an upcoming change, or which reports the change may break, it's relatively easy to do. Power BI files are binary and the API is very limited. I don't see a way to know which reports will be broken by a database schema change. I'll be watching for a good answer to this question. – dougp Jan 04 '23 at 23:15

1 Answers1

1

.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):

enter image description here

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.

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • It looks like that will work for one report, but what about for thousands? I see Power BI Desktop is used in the process. I'm not sure how I would automate that step. – dougp Jan 05 '23 at 16:23
  • @dougp You'd have to use RPA tool for this / write an UI automation script. Not pretty, but better than resaving this many files manually. And the steps are few and straightforward. – AdamL Jan 09 '23 at 10:27
  • Robotic Process Automation (https://powerautomate.microsoft.com/en-us/rpa-tool/)? So to make management of an allegedly mature, full-featured BI system efficient, I need to write a program that automates processes by basically opening the desktop app and using SendKeys? This is what we consider "modern" and "cloud"? – dougp Jan 09 '23 at 18:45