Forum Discussion

Sania198's avatar
Sania198
Frequent Visitor
3 years ago
Solved

How to Pull Parameters (Server & Database) programmatically from SQL Database via PowerShell Script

Hi,

I can successfully update the parameters using: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters-in-group , however I need help in updating Parameters (Server & Database) programmatically pulling from SQL Database and add it to the already Published Power BI Dataset.

Table with Server & Database details is already created in SQL DB, using Rest API or PS Script I want them to update to the dataset automatically (without giving hardcoded variables(values)) which is already published to Power BI Service.

 

Exactly I'm looking for this option: https://youtu.be/MJVSu47iMmo?t=413 from (6:53 to 7:06) via PS Script 

 

Thanks in Advance! 

 

 

  • Sania198's avatar
    Sania198
    3 years ago

    I resolved the issue! 🙂

    Need to pass If condition in between Foreach loop to get the specific details and to update it to the dataset.

    Thanks for your responses lbendlin 

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    The video describes how to do this via PowerShell.  What have you tried and where are you stuck ?

    • Sania198's avatar
      Sania198
      Frequent Visitor
      3 years ago

      lbendlin 

      I can update parameters when I give a hardcoded values like below and use it in the below script

      $parameterValue1 = "Testing"
      $parameterValue2 = "Local.Check"
       
      $datasetParametersUrl = "groups/$workspaceId/datasets/$datasetId/Default.UpdateParameters"
       
      $body = @{
          updateDetails = @(
              @{
                  name = "Server"
                  newValue = $parameterValue2
              }
              @{
                  name = "Database"
                  newValue = $parameterValue1
              }
          )
      }

      $jsonPostBody = $body | ConvertTo-Json
       
      Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody `
                               -ContentType:'application/json'

      Write-Host "Successfully updated specified parameters" -ForegroundColor Green
       
      Now I have a SQL DB table which has all the Server and Database details, I need help in writing PS script where the Server & Database details need to pull from DB and add it to the published dataset in Power BI Service
       
      I am succesfully in pulling the SQL DB table data via PS Script, however don't know how to use that table to automatically add it to the dataset in PBI Service which has Server and Database details
       
      It would be very helpful if you can help me with the sample script to use the DB table data to update the parameters automatically to the published dataset 
      • Sania198's avatar
        Sania198
        Frequent Visitor
        3 years ago

        I tried different loops but ending with below error even though the authentication is going good:

        My modified script:

        # query to show changes
        $Query = '
        SELECT TOP (1000) [DATAB]
              ,[Server]
              ,[database]
          FROM [dbo].[vw_BIWhouse_Info]
        '
        #RestAPICommand
        $SQLCmd = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw
        foreach ($DB in $SQLCmd) {
               
               $DB.DATAB  | Where-Object {$DB.database -eq 'client'}
               $DB.Server | Where-Object {$DB.database -eq 'client'}

               $UD = $DB.DATAB  | Where-Object {$DB.database -eq 'client'}
               $US = $DB.Server | Where-Object {$DB.database -eq 'client'}

               $parameterValue1 = $UD
               $parameterValue2 = $US

        }
         
           Connect-PowerBIServiceAccount

         # Specifed workspace and dataset where the current dataset is stored
        $WorkspaceID = "00000000-0000-0000-0000-000000000000"
        $DatasetID = "00000000-0000-0000-0000-000000000000"
         
        $datasetParametersUrl = "groups/$WorkspaceID/datasets/$DatasetID/Default.UpdateParameters"
         
        $body = @{
             updateDetails = @(
         @{
             name = "Server"
             newValue = $parameterValue2
         }
         @{
             name = "Database"
             newValue = $parameterValue1
         }
        )
        }
         $jsonPostBody = $body | ConvertTo-Json


         Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody -ContentType:'application/json'
         
         Disconnect-PowerBIServiceAccount
         
        However, when I use the below script it updates the parameters pulling from DB which matches with CLIENT object but it iterates through each DB ID and throws below error for IDs which doesn't match

        Script:

        Connect-PowerBIServiceAccount

          $WorkspaceID = "00000000-0000-0000-0000-000000000000"
          $DatasetID = "00000000-0000-0000-0000-000000000000"

        $Query = '
        SELECT TOP (1000) [DATAB]
              ,[Server]
              ,[database]
          FROM [dbo].[vw_BIWhouse_Info]
        '

        $SQLCmd = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw
        foreach ($DB in $SQLCmd) {
               
               $DB.DATAB  | Where-Object {$DB.database -eq 'client'}
               $DB.Server | Where-Object {$DB.database -eq 'client'}

               $UD = $DB.DATAB  | Where-Object {$DB.database -eq 'client'}
               $US = $DB.Server | Where-Object {$DB.database -eq 'client'}

               $parameterValue1 = $UD
               $parameterValue2 = $US

          $datasetParametersUrl = "groups/$WorkspaceID/datasets/$DatasetID/Default.UpdateParameters"
         
          $body = @{
               updateDetails = @(
           @{
               name = "Server"
               newValue = $parameterValue2
           }
           @{
               name = "Database"
               newValue = $parameterValue1
           }
          )
          }
           $jsonPostBody = $body | ConvertTo-Json
         
          Invoke-PowerBIRestMethod -Url:$datasetParametersUrl -Method:Post -Body:$jsonPostBody -ContentType:'application/json'

        }

        Disconnect-PowerBIServiceAccount
         
        I only want to pull the database and Server details which matches "CLIENT" and updates it to the PBI Service dataset