How to connect to SAP BI and Export data in CSV/XLSX?

SAP BI — Power BI connector
Source: https://www.sap.com/ https://powerbi.microsoft.com/ https://icon-library.com/

Introduction

Connecting with SAP financial systems and retrieving data from there has always been challenging as we do not have direct connectors or libraries present for that.

To solve this problem statement by getting smarter and being more effective in data scooping at granular level, this is a programmatic approach using Microsoft’s Power BI.

Research

I explored many other BI tools like Alteryx, Tableau Prep and few other connector implementations, however concluded Power BI’s native connector as appropriate to connect with SAP BW and Power BI REST APIs to access the resources.

Power BI REST API provides service endpoints for embedding, administration, and user resources.

Goal

Connect to SAP financial system and export data in .CSV or .XLSX for data reconciliation. Since the regular exports from Power BI service / APIs only support export formats as .PDF, .PPTX and .PNG, which provide the screenshot of the first page of the report, we do not get the entire report export in a data usable format. Hence, we are leveraging this solution which can generate a .CSV export and the same can be used for reconciliation!

Prerequisites

• Power BI Desktop — September 2021 version
• Power BI Report Builder
• Azure AD application registration

Problem Statement

Connect to SAP BW and export a report in CSV/XLSX format programmatically.

If you are looking for an automated approach for having SAP extracts in .CSV/.XLSX format for data reconciliations, data storage or email transmission, here is the solution.

Resolution Idea

Connect SAP BW→ select required reports → apply time ranges & filters → render report → Load / Transform data → Publish Report

Power BI Report -> Power BI Dataset created-> PBI Report Builder -> Create new Paginated Report from PBI dataset -> Publish

OAuth2 authentication -> Get Access Token (PowerShell cmdlets)

PBI REST APIs -> Export Paginated Report

This spans into 5 major areas as below:

Part A: Connection to SAP BI

• Power BI Desktop — Select data source as ‘SAP BW Message Server’ highlighted in green.

Source: https://docs.microsoft.com/

• Perform selection of required reports, apply time ranges & filters, and render report → Load / Transform data into PBI data model → Publish Report in Power BI Service
• These published reports can be exported however, for regular reports, file formats supported are .PDF, .PPTX and .PNG. There is no capability to export these reports in .CSV or .XLSX through an automated way.

Hence, the ‘Paginated Reports’ !

Part B: Paginated Reports

What are those ?

Paginated Reports are

• Designed to be printed or shared

• Formatted to fit well on a page

• Display all the data in a table, even if the table spans multiple pages

• Pixel perfect

• Power BI Report Builder is the standalone tool for authoring paginated reports for the Power BI service.

What export formats are available for paginated report in the Power BI service?

• You can export to Microsoft XLSX, Microsoft Word, Microsoft PowerPoint, PDF, .CSV, XML, and MHTML.

On publishing Power BI Report an underlying Power BI Dataset gets created for the report. Use this dataset as Data source for creation of paginated report. For this we make use of Power BI Report Builder desktop application. This creates a paginated report with .rdl file extension. The paginated report is to be published to Power BI Service to access it.

Paginated reports serve the reports having large volume of data. Only thing it depends on the amount of memory that’s available for Paginated Reports in your Premium capacity.

Key point: The workspace in which these reports are published needs to have Premium capacity.

• Capacity with Diamond icon.

Source: Power BI service https://app.powerbi.com/

To create Paginated Reports, need to use Power BI Report Builder application.

1. Open Report Builder and select New
2. Select data source as Power BI dataset
3. Select the dataset created for the regular report published using SAP BW source
4. Do Test Connection, should be successful
5. Select report fields as Row group, Column group and Functions
6. Perform report property settings as per required UI
7. Save file as .rdl
8. Publish this report on Power BI service.
9. On login in to Power BI service app.powerbi.com, the report will be seen under /rdlreports or type as ‘Paginated Report’.
10. The report id of this report is to be used for the export REST APIs for exporting it as CSV/XLSX.

Part C: Azure Service principal / application

To access the resources of Power BI service, we need to register and Azure AD application on Azure Portal. This is also called service principal.

This is needed to invoke the Power BI REST APIs.

Key point: Need to grant the service principal permissions in PBI Service.

Grant Service Principal Permissions in Power BI Service

• Request Power BI admins to add your service principal added to the requires Azure AD group.

Key point: Add Service Principal as Admin using Workspace Access interface.

Part D: Power BI REST API access token

For export operation we have the Power BI Export REST APIs available. To invoke any such APIs we need authentication, the access_token.

OAuth2 authorisation endpoints:

Below are the OAuth2 authorisation endpoints of Azure application:

Source: Azure AD App registration https://portal.azure.com/

• Perform authentication using below endpoint:

https://login.microsoftonline.com/<<TENANT_ID>>/oauth2/authorize?client_id=<<APPLICATION_ID>>&response_type=token&redirect_uri=<<REDIRECT URL set at app level>>&resource=https://analysis.windows.net/powerbi/api

If you hit this URL in browser, it will redirect to Microsoft Login page. On successful login, it will redirect to below URL:

https://<<REDIRECTURI>>#access_token=eyJ0eXAiOiJKV1DWDWQiLCJhbGciOiJS..zNE_zMTzaAn5SuQ&token_type=Bearer&expires_in=4026&session_state=e1871fa6–49d5–4840–93s0–7c79989402ca

OR

Power Shell cmdlets:

Connect-AzAccount

This helps to connect to Azure with an authenticated account for use with cmdlets from the Az PowerShell modules.

Once this performs a successful authentication, access token can be obtained from:

Get-AzAccessToken -ResourceUrl “https://analysis.windows.net/powerbi/api"

This will return a token which should be used for authentication.

Use this access_token as Bearer Authorisation token for invoking Export APIs further.

Part E: Export paginated reports

1. Reports — Export To File In Group // initiates export

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/export-to-file-in-group

This will give export_id in response after successful export.

curl -X POST -H “Content-Type: application/json” -H “Authorization: Bearer <<access_token>>” -d ‘{“format”: “CSV”}’ https://api.powerbi.com/v1.0/myorg/groups/<<group_id>>/reports/<<report_id>>/ExportTo

2. Reports — Get Export To File Status In Group // check status of export, if successful and 100%, then proceed with download file

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-export-to-file-status-in-group

Use export_id of Step#1 and perform polling till it is successful. If the export is completed 100% and Successful, we can proceed for file download.

curl -X GET -H “Content-Type: application/json” -H “Authorization: Bearer <<access_token>>” https://api.powerbi.com/v1.0/myorg/groups/<<group_id>>/reports/<<report_id>>/exports/<<export_id from step2>>

3. Reports — Get File Of Export To File In Group // download file

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-file-of-export-to-file-in-group

If Step#2 is completed, we are good to perform file download. Provide file name with .csv/.xlsx extension for output file for the export.

curl -X GET — output “export_report.csv” -H “Content-Type: application/json” -H “Authorization: Bearer <<access_token>>” https://api.powerbi.com/v1.0/myorg/groups/<<group_id>>/reports/<<report_id>>/exports/<<export_id from step2>>/file

Here we have the SAP data exported report in .CSV finally!

References :

https://docs.microsoft.com/en-us/rest/api/power-bi/
https://portal.azure.com/
https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app?tabs=customers%2CAzure

Conclusion

This is a programmatic way to build a mechanism which uses Power BI native connector to extract data from SAP systems in CSV/XLSX format with an immediate turn around.

Happy Exporting!