Power BI Data Refresh through SAP BW Process Chain

[hadi_jafari]

Introduction

In many SAP BW projects, data loading is orchestrated by SAP BW process chains, while business users consume the data through Microsoft Power BI dashboards. One common requirement is to automatically refresh the Power BI dataset immediately after the BW process chain finishes successfully.

While the Power BI Service exposes REST APIs for triggering dataset refreshes, these APIs are not available in Power BI Report Server. As a result, a different approach is required.

In this blog post, I will demonstrate a simple solution for triggering a Microsoft Power BI Report Server data refresh directly from an SAP BW process chain.

Solution Overview

The implementation consists of the following steps:

1. Create a data flow in SAP BW.
2. Create a SQL Server stored procedure that triggers the Power BI dataset refresh.
3. Develop an ABAP report that executes the stored procedure.
4. Add the ABAP report to an SAP BW process chain.

Sample Scenario

For demonstration purposes, I created a simple end-to-end data flow. This is the flow I've developed:

1. Excel [Local]
2. DSO [BW]
3. Query [BW]
4. Dashboard [Power BI]

Creating Stored Procedure in Power BI Report Server

The Power BI dashboard used in this example is named "Dashboard to Test PBI Data Refresh".

A scheduled refresh has already been configured for this dashboard. Instead of waiting for the scheduled execution time, we can trigger the refresh by calling the AddEvent procedure in the Report Server database.

The following stored procedure encapsulates this logic.

USE [ReportServer]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[MyRefreshData]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DASHBOARD_NAME  NVARCHAR(100),
@DASHBOARD_ID    VARCHAR(100),
@SUBSCRIPTION_ID VARCHAR(100),
@RETURN_VALUE    INT

SET @DASHBOARD_NAME = 'Dashboard to Test PBI Data Refresh'

SET @DASHBOARD_ID = (
 SELECT TOP 1 [ItemID]
 FROM [ReportServer].[dbo].[Catalog]
 WHERE [Name] = @DASHBOARD_NAME
)

SET @Subscription_ID = (
 SELECT TOP 1 SubscriptionID
 FROM [ReportServer].[dbo].[ReportSchedule]
 WHERE [ReportID] = @DASHBOARD_ID
)

WAITFOR DELAY '0:0:3'

EXEC @RETURN_VALUE = [ReportServer].[dbo].[AddEvent]
 @EventType = N'DataModelRefresh',
 @EventData = @Subscription_ID

END

Note: Replace "Dashboard to Test PBI Data Refresh" with the name of your own Power BI report.

Executing Stored Procedure from SAP BW

Next, we need an ABAP report that executes the stored procedure.

Before running the report, create a secondary database connection to the SQL Server database using transaction DBCO.

The following ABAP report opens the database connection, executes the stored procedure, and then closes the connection.

REPORT ZPBI_DATA_REFRESH.

DATA: LV_DBCO  TYPE DBCON_NAME.

LV_DBCO = 'SQL206'.

*Open database connection
EXEC SQL.
  CONNECT to :LV_DBCO
ENDEXEC.

IF SY-SUBRC <> 0.
  MESSAGE 'Error in opening the connection' TYPE 'E'.
ENDIF.


*Execute the proceudre
EXEC SQL.
  execute reportserver.dbo.myrefreshdata
ENDEXEC.

IF SY-SUBRC <> 0.
  MESSAGE 'Error in executing the procedure' TYPE 'E'.
ENDIF.


*Close database connection
EXEC SQL.
  DISCONNECT :LV_DBCO
ENDEXEC.

IF SY-SUBRC <> 0.
  MESSAGE 'Error in closing the connection' TYPE 'E'.
ENDIF.

Adding ABAP Program to the Process Chain

The final step is to execute the ABAP report automatically from the SAP BW process chain.

Create an ABAP Program process variant, assign program ZPBI_DATA_REFRESH, and place it after the successful completion of your BW data loading processes.

With this configuration, every successful execution of the process chain automatically triggers the Power BI Report Server dataset refresh, ensuring that business users always see the latest data without any manual intervention.

Conclusion

This approach provides a straightforward integration between SAP BW and Power BI Report Server by leveraging a SQL Server stored procedure and a simple ABAP report. If you are using Power BI Report Server together with SAP BW, this technique offers a reliable way to synchronize your ETL process with Power BI data refreshes.