Power BI Premium Data lineage only shows tables of dataset but not database

[NewUser777]

Goal: Get all source database actual  tables of reports which are embedded in Powerbi reports

I have a bunch(20) of Powerbi reports which are using oracle as the database via the datagateway and would like to know how can I get all the tables used by the report from Power BI premium service . So far I was not able to find any option. 

Hence want to know if there is any Powershell script or any other option to get the information.

Note: we dont have enterprise data lineage tool 

[TomMartens · Accepted solution]

Hey @NewUser777 ,

getting the tables from the source is not that easy but possible. If your workspaces containing the relevant datasets connecting to your oracle source you can use the XMLA endpoint of the workspace to connect any frontend to the endpoint.

The image below shows the connection of SQL Server Management Studio to that endpoint. I'm using the DAX query editor to run a DMV namely the TMSCHEMA_PARTITIONS:

As you can see the column QueryDefinition contains everything you need to know, it just retrieves transformations. As it's possible to connect to the XMLA endpoint using Power BI Desktop, you can run the DMV and use the magic to extract the Oracle table names using some Power Query magic:

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom

[NewUser777]

Thanks for this idea , I have built a report now pointing to 20 datasets in Prod  . Do you think will there be performance issue to run this on Power BI Premium Gen2 platform?