SAP General Ledger - Extract General Ledger Accounting Data to Power BI with Python Scripts

[Jie_DvW]

The Power BI Connector for SAP (PCS) from DVW Analytics can be used to extract data directly from SAP into Power BI Reports and Dashboards.

In this blog series I will detail all the steps needed to extract data from SAP Tables and import the associated data into Power BI where it can be easily refreshed.

Problem

You are a financial analyst/manager or a Power BI developer and have a requirement to produce or enhance a dashboard which requires SAP General Ledger Accounting data from the SAP Finance General Ledger Module of your SAP System. In particular you require data from the “Big Two” SAP Financial Accounting (FI) Transaction Data Tables: BKPF (Accounting Document Headers) and BSEG (Accounting Document Segments).

Currently you are either manually extracting data from these table saving the extracts to locally stored folder locations and then uploading the data to Power BI . Due to the volume of data you are also having to manually extract the data multiple times.   This is an onerous, time consuming and repetitive task which is open to error.  You also have a major issue of not being able to automate the refreshing of your Power BI Report and Dashboard.  

You need a solution up and running quickly and do not have the time or resources to build a traditional database solution.

Solution

The DVW Power BI Connector for SAP (PCS) has a selection of tools that enable data to be extracted from various SAP sources including SAP Table,  Transaction Codes, ABAP Reports, SAP BW Queries and Info providers and SAP Queries. The data can then be loaded into Power BI using Python Scripts generated by PCS.  When the  Power BI report is refreshed the data is automatically reloaded from SAP.

The following diagram provides an overview of the requirement.

From Power BI use a dynamic list of Fiscal Years and Company Codes to extract Finance Document Header information from BKPF. The list of document numbers extracted will be used to select and extract Financial Document Segment information from BSEG. 

The next diagram illustrates the solution.

The  DVW PCS the SAP Table Tool  is used to create data sources to extract data from SAP Tables BKPF and BSEG. Python scripts will also be generate in DVW PCS which will be used in Power BI to facilitate the injection of filter values into the SAP data calls.

I've outlined below the steps taken in two parts. The first part details the configuration steps taken in the Power BI Connector for SAP (PCS) and the second part outlines the Power BI Desktop steps.

If you would like to follow the steps described in this blog, you will need:

• Power BI
• DVW PCS for Power BI 
• Access to an SAP ECC or SAP S/4HANA system

Part 1 : 

PCS SAP Table Tool Configuration.

Open the DVW xCS Console

1. In the File Management Tab click Create

In  the xCS Configuration Control -->Basic Tab

1. Select the relevant SAP System from the drop down and enter a valid SAP user name and password.

2. Select the SAP Table Data Read Tool from the drop down list.

3. Enter a file name BKPF. This will be used later in Power BI.

In the parameters tab:

4. Enter the Table Name.

5. Click on Search.

6. Select the required fields.

7. Click on Save.

8. Repeat the above steps for the SAP table BSEG.

PCS Python Scripts - Dynamic Filter Input

Generate a Python script to a use a selection of dynamic filters to retrieve records from BKPF.

In the Scripts tab:

1. Select the BKPF Data Entity from the dropdown.

2. Select the Target Power BI.

3. Select the Dynamic Filter Input tab.

4. Check the Dynamic Filter Box.

5. Click on Refresh.

6. Click on Save and Copy.

This "BKPF python script" will be used in Power BI in Part 2.

PCS Python Scripts - Dynamic Table Input

Generate a Python script to a use a list of document numbers to retrieve records from BSEG.

In the Scripts tab:

1. Select the BSEG Data Entity from the dropdown.

2. Select the Target Power BI.

3. Select the Dynamic Table Input tab.

4. Check the Dynamic Table box.

5. Click on Refresh.

6. Click on Save and Copy.

This "BSEG  python script" will be used in Power BI in Part 2.

Part 2: 

In the Power BI Query Editor

Create Query "1 Filter Input Values"

Use the Enter Data to create a Table called "1 Filter Input Values" with the columns shown in the picture below.

Create Query 2 BKPF (Accounting Document Headers)

1. Create a New Blank Source Query with the source query "1 Filter Input Values"

2. From the Transform tab select Run Python script.

3. Paste in the BKPF Python script created in Part 1 above.

4. Expand the BKPF Table.

5. Rename the query to  "2 BKPF (Accounting Document Headers)"

Create Query 3 BSEG (Accounting Document Segments)

1. Create a New Blank Source Query with the source query "2 BKPF (Accounting Document Headers)".

2. Remove all the columns except for CompanyCode, FiscalYear and AccountingDocumentNumber.

3. From the Transform tab select Run Python script.

4. Paste in the BSEG Python script created in Part 1 above.

5. Expand the BSEG Table.

6. Rename the query to  "3 BSEG (Accounting Document Segments)"

The data is now available in Power BI and can be transformed further or can be combined with other data.

Once the transformations are complete select Close & Apply.

In Power BI 

The sap data is now in available in Power BI and Reports and Dashboards can be built using it.  

Power BI - Refresh data

When refreshing Power BI reports the data is freshly retrieved from SAP. The calls into SAP can be seen and monitored in the DVW xCS Console --> Monitor tab.

Recap

Using the DVW Power BI Connector for SAP I was able to create data sources to extract data from SAP Tables.  Python script were generated in the DVW Power BI Connector which allow dynamic filters to be used to select data from the SAP Accounting Document header table (BKPF) and the extracted Accounting Document numbers were used to select and extract SAP Accounting Document Segment information (BSEG).  Once the Power BI model and report was built I was able to refresh the Power BI report and the data was automatically reloaded from SAP.