Moving from PBIRS to PBI Service reports thanks to SimplePBI and SwaggerAPI

[ibarrau]

1. Understanding the PBIRS API

The first thing we need to do in order to use the Power BI Report Server API is to have the documentation at hand:

• https://app.swaggerhub.com/apis/microsoft-rs/pbirs/2.0

In that section, we can find all the endpoints/requests that will help us understand what we have inside the server. Testing any GET request is super easy. Just enter a browser and enter the base address + what we want. For example, to retrieve the Power BI Reports:

• http://localhost/Reports/api/v2.0/PowerBIReports

Localhost represents the server where the Report Server is installed. To facilitate checking the links, we will simply assume that we have access to the server in that way.

Although the API seems to be open within the server, the reality is that it is not accessible by code. So our first challenge will be to authenticate the API with Python. We can achieve this using the requests_ntlm2 library. By creating an object HttpNtlmAuth(String Username, String Password), we can use it as the "auth" parameter in the famous "requests" library in Python.

Let's see an example to retrieve Power BI reports, assuming that we have already imported the aforementioned library along with requests and json.

If we enter the username and password to log in to the Report Server, we can use the API in this way.

NOTE: If you haven't configured users for the Report Server, you can use the Windows accounts that you use to log in to the operating system.

IMPORTANT: Power BI Report Server only stores the version of the file saved from Desktop. Data refreshes generated by SQL Agent are not visible in the report. It will always download the published version, which means that until you update the data from the service, the report will not be up to date.

2. Getting pbix from PBIRS to import into Service

Now that we know how to use the API, let's assume that we need to migrate a report that we identified from the list of reports. We read the ID of the report we want and saved it. Having the ID of the report that we want to bring to Power BI Service, we can keep the same name if we extract it from one request and the content of the pbix file from another. Let's see the code.

The script begins with what we saw before, assuming the three imported libraries. Notice that we have an additional variable called report_id compared to what we saw earlier. We will use that variable for our requests. The first request will be to retrieve the metadata of the report. It explores the JSON response to find the "Name" attribute and concatenates the string ".pbix" to complete the file name in the format "[Resulting Name].pbix" and stores it in the report_name variable.

The second request to the API will retrieve the content of the report using the ID. The response will be the content of the API response.

3- Importing into Service

In this final step, we will use the Python library called SimplePBI, which allows us to use the Power BI Rest API. The last response will be in the expected format to use our library. The Imports object contains a method to import the content into a specific workspace using a variable.

The first three parameters will be used:

• workspace_id: the workspace where we want to publish the report.
• fileContent: the content of the file that we saved in the result variable.
• datasetDisplayName: a string with the name of the file that should end with .pbix, just as we set in the report_name variable.

NOTE: We will assume that we know how to use the Power BI library or API, specifically how to register an app in Azure with ReadWrite.Dataset permissions and add the app as a member of the workspace we are using with the appropriate Power BI Service options that allow the use of a Service Principal. If you don't know you easily search a post or video online.

The last thing we will do in the script is to create the token and imports objects to call the desired method. It would look like this:

By following these steps, we will have successfully migrated a report from Power BI Report Server to Power BI Service. From this start point you can build a complex migration like reading all reports at the server and migrate to Service creating workspaces like folders.

If you want to see the complete script, you can find it on my GitHub repository.

Original post by LaDataWeb in spanish