Forum Discussion

Arie's avatar
Arie
Advocate IV
8 years ago

Sample publish script in Powershell?

Hi,

 

Does anyone have a sample script for publishing a PBIX to the Power BI Service via the REST API?

I have found the reference on https://msdn.microsoft.com/en-us/library/mt243840.aspx, but can not get it to work.

 

Hope someone can help me!

 

Regards, Arie

1 Reply

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee

    The only PowerShell that currently avaialble to find under Github is the following:

    powerbi-powershell/copyWorkspace.ps1

     

    Which copies the workspace, and then export/Import the PBIX file.

     

    For the Export and import part, check:

    # PART 3: Copying reports and datasets using Export/Import PBIX APIs

    In the scripts, import part should be in Bold :

    # PART 3: Copying reports and datasets using Export/Import PBIX APIs
    # ==================================================================
    $report_ID_mapping = @{}      # mapping of old report ID to new report ID
    $dataset_ID_mapping = @{}     # mapping of old model ID to new model ID
    $failure_log = @()  
    $import_jobs = @()
    $source_group_path = get_groups_path($source_group_ID)
    $target_group_path = get_groups_path($target_group_ID)
    
    $uri = "https://api.powerbi.com/v1.0/$source_group_path/reports/"
    $reports_json = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET
    $reports = $reports_json.value
    
    # For My Workspace, filter out reports that I don't own - e.g. those shared with me
    if ($source_group_ID -eq "me") {
        $reports_temp = @()
        Foreach($report in $reports) {
            if ($report.isOwnedByMe -eq "True") {
                $reports_temp += $report
            }
        }
        $reports = $reports_temp
    }
    
    # == Export/import the reports that are built on PBIXes (this step creates the datasets)
    # for each report, try exporting and importing the PBIX
    New-Item -Path $temp_path_root -ItemType Directory 
    "=== Exporting PBIX files to copy datasets..."
    Foreach($report in $reports) {
       
        $report_id = $report.id
        $dataset_id = $report.datasetId
        $report_name = $report.name
        $temp_path = "$temp_path_root\$report_name.pbix"
    
        # only export if this dataset hasn't already been seen
        if ($dataset_ID_mapping[$dataset_id]) {
            continue
        }
    
        "== Exporting $report_name with id: $report_id to $temp_path"
        $uri = "https://api.powerbi.com/v1.0/$source_group_path/reports/$report_id/Export"
        try {
            Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET -OutFile "$temp_path"
        } catch {
            Write-Host "= This report and dataset cannot be copied, skipping. This is expected for most workspaces."
            continue
        }
         
        try {
            "== Importing $report_name to target workspace"
            $uri = "https://api.powerbi.com/v1.0/$target_group_path/imports/?datasetDisplayName=$report_name.pbix&nameConflict=Abort"
    
            # Here we switch to HttpClient class to help POST the form data for importing PBIX
            $httpClient = New-Object System.Net.Http.Httpclient $httpClientHandler
            $httpClient.DefaultRequestHeaders.Authorization = New-Object System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", $token.AccessToken);
            $packageFileStream = New-Object System.IO.FileStream @($temp_path, [System.IO.FileMode]::Open)
            
    	    $contentDispositionHeaderValue = New-Object System.Net.Http.Headers.ContentDispositionHeaderValue "form-data"
    	    $contentDispositionHeaderValue.Name = "file0"
    	    $contentDispositionHeaderValue.FileName = $file_name
     
            $streamContent = New-Object System.Net.Http.StreamContent $packageFileStream
            $streamContent.Headers.ContentDisposition = $contentDispositionHeaderValue
            
            $content = New-Object System.Net.Http.MultipartFormDataContent
            $content.Add($streamContent)
    
    	    $response = $httpClient.PostAsync($Uri, $content).Result
     
    	    if (!$response.IsSuccessStatusCode) {
    		    $responseBody = $response.Content.ReadAsStringAsync().Result
                "= This report cannot be imported to target workspace. Skipping..."
    			$errorMessage = "Status code {0}. Reason {1}. Server reported the following message: {2}." -f $response.StatusCode, $response.ReasonPhrase, $responseBody
    			throw [System.Net.Http.HttpRequestException] $errorMessage
    		} 
            
            # save the import IDs
            $import_job_id = (ConvertFrom-JSON($response.Content.ReadAsStringAsync().Result)).id
    
            # wait for import to complete
            $upload_in_progress = $true
            while($upload_in_progress) {
                $uri = "https://api.powerbi.com/v1.0/$target_group_path/imports/$import_job_id"
                $response = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET
                
                if ($response.importState -eq "Succeeded") {
                    "Publish succeeded!"
                    # update the report and dataset mappings
                    $report_id_mapping[$report_id] = $response.reports[0].id
                    $dataset_id_mapping[$dataset_id] = $response.datasets[0].id
                    break
                }
    
                if ($response.importState -ne "Publishing") {
                    "Error: publishing failed, skipping this. More details: "
                    $response
                    break
                }
                
                Write-Host -NoNewLine "."
                Start-Sleep -s 5
            }
                
            
        } catch [Exception] {
            Write-Host $_.Exception
    	    Write-Host "== Error: failed to import PBIX"
            Write-Host "= HTTP Status Code:" $_.Exception.Response.StatusCode.value__ 
            Write-Host "= HTTP Status Description:" $_.Exception.Response.StatusDescription
            continue
        }
    }

     

    Regards,

    Michael