cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrandedSaiyan
Advocate I
Advocate I

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.


BrandedSaiyan_0-1659032768129.png

 

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.

BrandedSaiyan_1-1659033179852.png

 


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

 

2 ACCEPTED SOLUTIONS

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.

View solution in original post

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

View solution in original post

15 REPLIES 15
lbendlin
Super User
Super User

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.

I have DAX studio, but I'm unfamilar with any function or code that extracts the M code. I'll do some digging, was just hoping to be pointed in the right direction. Thanks again. 

In DAX Studio connect to your dataset (local or in service)

Run two queries (individually)

select * from $SYSTEM.TMSCHEMA_PARTITIONS

select * from $SYSTEM.TMSCHEMA_EXPRESSIONS

 

Grab the QueryDefinition column from the first and the Expression column from the second

 

Rinse and repeat for all your datasets.

Thank you! Do you know if I'm able to run this against a shared power bi dataset? Or if it can be ran more in bulk rather than foiir just one dataset at a time? Much appreciated. 

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.

Circling back here. I've been unable to find a way to run these queries from powershell. Any help would be greatly appreciated.

 

Thanks!

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

This ended up working great! Below is the powershell script I ended up using. 

$Workspaces = Invoke-PowerBIRestMethod -Url 'admin/groups?$top=5000&$filter=startswith(name, ''PersonalWorkspace'') eq false and state eq ''Active'' and isOnDedicatedCapacity eq true   ' -Method Get | ConvertFrom-Json

$wsIds = $Workspaces.value.id

$DataSourceCode = @()

foreach ($i in $wsIds) {


        $wsInfo = Get-PowerBIWorkspace -Id $i -Scope Organization 

        $WSName = $wsInfo.Name

        $server = "powerbi://api.powerbi.com/v1.0/myorg/" + $WSName

        $DataSetsinWS = Get-PowerBIDataset -Scope Organization -WorkspaceId $i 

            foreach ($d in $DataSetsinWS) {

                    $dName = $d.Name

                    $DataSourceCode += Invoke-ASCmd -Server $server -Database $dName -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS"
  
                            
                                            }

                          }

Note that there is a subtle difference between "PARTITIONS"  and "EXPRESSIONS".

Thanks for pointing this out. I'm noticing for a report I'm currently working on I have 3 SQL Server datasources showing under PARTITIONS and 1 SQL Server source showing under EXPRESSIONS. I'm not able to determine the difference or why one shows under expressions. Any further insight is appreciated. 

Both are queries.  Partitions are loaded into the Vertipaq engine.  Expressions are not loaded into the engine but may participate in the data pull, especially when other queries depend on them (even when you explicitly state you don't want to refresh them !).

 

You can tell by the italicization of the query name in Power Query.  

Looks like I've got some research to do on the Vertipaq engine :). You're responses have helped me immensley. Thanks so much for you're help on this topic! I've updated my powershell script to the following. 

 

$Workspaces = Invoke-PowerBIRestMethod -Url 'admin/groups?$top=5000&$filter=startswith(name, ''PersonalWorkspace'') eq false and state eq ''Active'' and isOnDedicatedCapacity eq true and contains(name, ''[Development]'') eq false and contains(name, ''[Test]'') eq false and capacityMigrationStatus eq ''Migrated'' ' -Method Get | ConvertFrom-Json

$wsIds = $Workspaces.value.id

$DataSourceCode = @()

foreach ($i in $wsIds) {


        $wsInfo = Get-PowerBIWorkspace -Id $i -Scope Organization 

        $WSName = $wsInfo.Name

        $server = "powerbi://api.powerbi.com/v1.0/myorg/" + $WSName

        $DataSetsinWS = Get-PowerBIDataset -Scope Organization -WorkspaceId $i 

            foreach ($d in $DataSetsinWS) {

                    $dName = $d.Name

                    $query1 = "select " + ''''+ $dName + ''''+ " as DatasetName, [Name], [QueryDefinition] from `$SYSTEM.TMSCHEMA_PARTITIONS"

                    $DataSourceCode += Invoke-ASCmd -Server $server -Database $dName -Query $query1

                    $query2 = "select " + ''''+ $dName + ''''+ " as DatasetName, [Name], [Expression] from `$SYSTEM.TMSCHEMA_EXPRESSIONS"

                    $DataSourceCode += Invoke-ASCmd -Server $server -Database $dName -Query $query2
  
                            
                                            }

                          }

I really appreciate all your help, going to give this a try and get back to you! Thank you!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors