Forum Discussion
Possible to Query Keywords in SQL Statement Datasource Using API?
Hello Power BI Community,
I recently gained access to the Power BI REST API. I was hoping there would be a way to do a keyword search on the SQL Statement that is part of part of a SQL Server datasource for a dataset for a report. See screen shot below for where I am refering to.
Our issue is that instead of using views or stored procedures in our SQL statements, 90% of our reports are using inline SQL code. It's almost impossible to do a dependecy check or impact analysis with our reports written this way and I was hoping the REST API would allow us to do an organization wide search of the SQL statement in all datasources across all datasets so we could search for specific tables and objects.
Using the Datasets - Get Datasources endpoint only gets me the datasource type and connectiondetails (being the server and database) for a given datasource, but not the sql statement.
Is there any way that I can query for the SQL statement?
Edit: Adding Greg_Deckler since his expertise has helped me in the past 🙂
Thank you
You have to run it against each dataset (that you have access to) separately. However you can sort of automate that by running the queries from PowerShell.
Invoke-ASCmd -Server "powerbi://api.powerbi.com/v1.0/myorg/<workspace name here>" -Database "<dataset name here>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\xxx\Downloads\dmv.xml
14 Replies
You're in for a treat. You will want to learn about XMLA endpoints, and DMX queries. You can run these against all your datasets and extract the complete M code for all connections (including the embedded SQL).
NOTE: This is only valid for Import Mode connections. For Direct Query you have no control over, or visibility of the queries that are generated on the fly for each visual.
Thank you, do you have any helpful links or resources you'd reccomend?
There's DAX Studio , but beyond that the air is pretty thin. Mostly trial and error.
