Forum Discussion

odumna's avatar
odumna
Helper I
4 years ago

Is there posibility to call power admin api using sql queries ?

Hi

Is there posibility to call power admin api using sql queries?
For example, I need to get list of my organizational workspaces and save this information into some table on sql server database.


Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Run the API call in Powershell and push the resultset into a SQL Server database table.

    • odumna's avatar
      odumna
      Helper I
      4 years ago

      Thank you for answer. I will try to do it. Let you know if I have additional queations.

    • odumna's avatar
      odumna
      Helper I
      4 years ago

      is there some documentation about using cmdlets for powershell ?
      what protocols it is used, what path and ports should be opened for requests?  
      In my case, server (where my sqldatabase exists) doesn't have access to internet, so I need to ask my network team-members to open access to powerbi site.... or smth like this... 
       

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        These are standard Powershell functions.  Nothing but HTTPS.

         

        Something along these lines

         

        $dataset = "9941f7c0-xxxx-4382-959c-731a752e9a8f"

$URI = "https://api.powerbi.com/v1.0/myorg/datasets/$dataset/executeQueries"

$query = '

EVALUATE
<your DAX here>      
'
$body = '{ queries:  [ { query : "' + $query.Replace('"','\"') + '" } ],"serializersettings":{ "includeNulls":true} }'
$res = Invoke-RestMethod -Headers $authHeader -Uri $URI -Body $body -Method POST
$rows = $res.Substring(3) | ConvertFrom-Json | Select-Object -Expand results  | Select-Object -Expand tables  | Select-Object -Expand rows
,$rows | Write-SqlTableData -ServerInstance "server" -DatabaseName "database" -SchemaName "dbo" -TableName "table"