Extract SAP General Ledger – G/L Account Line-Item…

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

2. Select the SAP T-Code/ABAP Report Read Tool from the drop down list.

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

In the xCS Configuration Control  –>  Parameters  –> Selection Tab

4. Enter the Transaction code FBL3N in the Search String and click on Search 

5. Select a SAP Variant from the drop down.

  Note: it is also possible to manually enter the filter values in the Filters and Variable Tab.

 

In the xCS Configuration Control  –>  Parameters –> Header Fields –> Data Preview Tab

6. Click on  Raw Preview.

   The report output from SAP will be reformatted to a flat table format .

7 . Use the Right Click Context Menu on specific rows to format the report output.

•     Add Header Row No: Select the row which contains the Column headings
•     Parse Header Field:  Transposes Header Fields and their values into a column.
•     Ignore Row:  Ignores Totals Rows and unrequired header rows.

 

8.  Click on Confirm Layout to check and confirm the final  layout of the data.

In the xCS Configuration Control –> Parameters –> Header Fields –> Fields Tab

9. Review the list of Fields and amend field lengths or types if required.

     In this example I will use Power BI to change fields types of the date and amount fields.

10. Click on Save.

 

 

PCS SAP Table Tool Configuration.

Listed below are the additional fields and related SAP table that I require to enrich my SAP General Ledger Line Item  report.

• T001: Fields Required Company Code name and Chart of Accounts.
• SKA1: General Ledger Account Group.
• T077Z: Account Group Description.
• SKAT: General Ledger Account Description.

The steps outlined below  need to be repeated for each table.

 

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 T001_Company_Code_MD. This will be used later in Power BI.

 

4. Enter the Table Name.

5. Click on Search.

6. Select the required fields.

7. Click on Save.

 

 

PCS OData URL

The OData URL will be used in Power BI OData feed source.

In the File Management tab of the xCS Console.

1. Right click on your email and select and select Copy OData URL. 

 

 

PCS OData Feed User Name and Password

The OData User Name and Password will be required in Power BI to authenticate the OData Feed connection.

In the Settings tab of the xCS Console.

1. Create  a password for the Local Host.  The Email Id is used as the User Name in Power BI.

2. Click on Save Settings. 

 

 

Part 2: 

In the Power BI Query Editor

1. Select the OData Feed from the New Source Drop down.

2. Paste in the OData URL obtained in Part 1 above.

3. Select the Basic Authentication type and enter your User Name and the password you created in Part 1 above.

4. Click on Connect.

 

 

4. Select the five PCS OData data sources that were created in Part 1 above.

5. Click on OK.

 

 

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

7. I used the Merge Query function to combine the fields required from the Master Data Sources with the FBL3N GL Account Items into a new Query called  SAP GL Line Items (FBL3N) which I used when creating my Powe BI report.  I also used the Power BI functions to change the data types on the Amount and Date Fields.

8. 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.  I used the SAP GL Line Items (FBL3N) from the step above to create the following report.

 

 

 

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 which can be accessed from Power BI using the Odata feed.  In this blog I created  PCS data sources to extract data from the SAP General Ledger – GL Account Line Item Display Report (T- code FBL3N) data source and some supporting SAP Master Data tables. Once my Power BI model and report were built I was able to refresh the Power BI report and the data was automatically reloaded from SAP.