loading...

Upload RDL file(s) to Power BI via API

kenakamu profile image Kenichiro Nakamura ・3 min read

Today, my good friend asked me how to upload RDL file(s) to Power BI.
And my answer was "What? Power BI supports RDL now???" :)

At the end, this is my research results.

RDL on Power BI (premium)

Yes, it's true that Power BI Online supports RDL as "Paginated Report".
I followed Tutorial: Create a paginated report and upload it to the Power BI service to learn how to do it manually.

How to upload RDL via API

Now time to research how to upload RDL. Power BI team provide comprehensive API documentation at Power BI REST APIs

I looked through the documentation and figured out this is how it works.

  1. Use Post Import In Group API to import RDL file from local disk, which creates RDL (or Paginated Report) repot in Power BI Online.
  2. Use Get Import In Group to get the import result which contains created report information.

If I only want to upload RDL files, then that's it. However, I had to set data source credential manually, so I did further research. And this is how to set credential.

  1. Use Reports - Get Datasources In Group API to get the datasource for the report, which returns gateway and datasource information.
  2. Use Gateways - Update Datasource API to set the credential.

Okay that's it!

PowerShell exmple

There is an official Power BI PowerShell module, and I partially used it. I may need to contribute the module so that it supports entire workflow.

This is far from perfect but I hope you get some idea.

function Publish-ImportRDLFile 
{
    param
    (
        [string]$RdlFilePath,
        [string]$GroupId,
        [string]$nameConflict = "Abort"
    )

    # Get file content and create body
    $fileName = [IO.Path]::GetFileName($RdlFilePath)
    $boundary = [guid]::NewGuid().ToString()
    $fileBody = Get-Content -Path $RdlFilePath -Encoding UTF8

    $body = @"
----FormBoundary$boundary
Content-Disposition: form-data; name="$filename"; filename="$filename"
Content-Type: application/rdl

$fileBody 
----FormBoundary$boundary--

"@

    # Get AccessToken and set it as header.
    $headers = Get-PowerBIAccessToken

    if ($GroupId) {
        $url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/imports?datasetDisplayName=$fileName&nameConflict=$nameConflict"
    }
    else {
        $url = "https://api.powerbi.com/v1.0/myorg/imports?datasetDisplayName=$fileName&nameConflict=$nameConflict"
    }

    # Create import
    $report = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -Body $body -ContentType "multipart/form-data"   
    $report.id
}

function Set-BasicPassword-To-RDL 
{
    param
    (
        [string]$Id,
        [string]$GroupId,
        [string]$UserName,
        [string]$Password
    )

    # Get AccessToken and set it as header.
    $headers = Get-PowerBIAccessToken

    # Get import result first. As it may take a bit of time until report is created, so repeat until I can get report Id
    $reportId = $null
    while($reportId -eq $null)
    {
        if ($GroupId) {
            $url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/imports/$Id"
        }
        else {
            $url = "https://api.powerbi.com/v1.0/myorg/imports/$Id"
        }

        $import = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
        $reportId = $import.reports.id
    }

    # Datasource information
    if ($GroupId) {
        $url = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/reports/$reportId/datasources"
    }
    else {
        $url = "https://api.powerbi.com/v1.0/myorg/reports/$reportId/datasources"
    }

    $datasources = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
    $gatewayId = $datasources.value[0].gatewayId
    $datasourceId = $datasources.value[0].datasourceId

    # Finallly set credential. No Uri difference between myworkspace and group workspace.
    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$datasourceId"

    # This is a sample for basic authentication. Find more samples at https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#examples
    $body = @"
{
  "credentialDetails": {
    "credentialType":"Basic",
    "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"$UserName\"},{\"name\":\"password\", \"value\":\"$Password\"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "None",
    "privacyLevel": "None"
    }
}
"@
    $datasources = Invoke-RestMethod -Uri $url -Method Patch -Headers $headers -ContentType "application/json" -Body $body
}

# Connect to Power BI.
Connect-PowerBIServiceAccount

$groupId = "<group id>"
# Create Import
$id = Publish-ImportRDLFile -GroupId $groupId -RdlFilePath "<path to rdl file>"
# Set password
Set-BasicPassword-To-RDL -Id $id -GroupId $groupId -UserName "<username>" -Password "<password>"

Summary

Power BI provides powerful Web API for developer. Whenever you need some automation, check it out!

Reference

Power BI Community Get Help: REST import rdl files

Discussion

pic
Editor guide
 

Interesting... I just run the script again and it simply works in my environment so it could be some setup difference? If you signup for new trial, does it work? Anyway I recommend you to create support ticket to see if support team give some idea.

 

Actually I don't need a trial, as I am uploading my RLD report for the embed scenario (aka "embedding Power BI content into an application for your customers"). So, the REST API client is authorized through a service principal ID and secret. My code works fine for PBIX files, while it does not for RDL ones. Perhaps there is some issue when the token refers to a service principal, not a user.

Thanks for your feedback!

 

Excellent example. I used your post to develop a solution to upload Paginated Reports with a live connection to Azure Analysis Services. Thank you!

One thing that is not clear from the API documentation is how to change the reports name. How would you go about that?

 

I also try to figure it out but I couldn't. Maybe we need to re-create it with different name?

 

Helllo Kenichiro,

can you please confirm that this example does actually work? I have been working on import an RDL report into the Power BI service by means of the REST API for a few days now, but I have not managed to upload the report so far. Although I am coding in Go with direct HTTP request, I have tried also your example as well as a C# one, so that I could find a working solution and then compare the flow with mine in Go.

Typically the error "400 Bad Request RequestedFileIsEncryptedOrCorrupted" is returned.

I also noticed that a plenty of people have faced the same issue, just a few topics found on the Power BI forum confirming that:

Therefore, if you can provide further details on your approach, it would be appreciated.

Best reagrds,
Michał