The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
78 | |
36 | |
33 | |
23 | |
14 |