Forum Discussion

BrandedSaiyan's avatar
BrandedSaiyan
Advocate II
4 years ago
Solved

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 data...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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.

  • lbendlin's avatar
    lbendlin
    4 years ago
    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
lbendlin's avatar
lbendlin
Super User
4 years ago

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.

  • BrandedSaiyan's avatar
    BrandedSaiyan
    Advocate II
    4 years ago

    Thank you, do you have any helpful links or resources you'd reccomend? 

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      There's DAX Studio , but beyond that the air is pretty thin. Mostly trial and error.

      • BrandedSaiyan's avatar
        BrandedSaiyan
        Advocate II
        4 years ago

        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.