SAP to SQL Server PowerBI Integration without PI licences

[former_member348242]

Introduction

The business requirement is to use MS Power BI for analytics by fetching Sales / Purchase details from SAP, worked around the same and started writing this blog.

Prerequisites and Mapping data:

Decided to take up this with MS SQL and MS Power BI and pushing sap data to SQL.

Prepared a basic flow as given below

            SAP ECC ---> FTP Folder--->SQL Server--->MS Power BI

Our SAP Environment :

Server : AIX

Data Base : SYBASE

Procedures

• First create a secured FTP Folder.


• Develop sap program and schedule to export CSV/Excel file data to FTP folder.

• Sap support below FM to export data to FTP folder.

1. HTTP_SCRAMBLE -For Encrypting the Password


2. FTP_CONNECT -For connecting to the FTP Server's user directory


3. SAP_CONVERT_TO_TEX_FORMAT -To convert my internal table data into flat file


4. FTP_R3_TO_SERVER - To transfer internal table data to FTP server directory.
   

    CALL FUNCTION 'FTP_R3_TO_SERVER'
   
             EXPORTING
   
               handle         = mi_handle
   
               fname          = path          "file path of destination system
   
               character_mode = 'X'
   
             TABLES
   
               text           = it_data1
   
             EXCEPTIONS
   
               tcpip_error    = 1
   
               command_error  = 2
   
               data_error     = 3
   
               OTHERS         = 4.
   
   
   
           IF sy-subrc <> 0.
   
             MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
   
             WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
   
             RAISING invalid_output_file.
   
           ELSE.
   
             WRITE: / 'File Transfer to FTP folder Successfully'.
   
           ENDIF.

   
   


5. FTP_DISCONNECT-For Disconnecting the connected FTP Session


6. RFC_CONNECTION_CLOSE- This is used to disconnect the RFC connection between SAP and other system.

• Developed a .net tool and schedule to read the csv/excel and insert into SQL Server.

• Connect the power-bi to SQL server.

Power BI Out Put:



Conclusion:

This tool developed for Management/stakeholders to analyse the sales data which helps organization to take the decisions and planning based on the growth drop and achievement, comparing between the current and previous year sales data.

Further for any clarifications please comment so, that same can be explained in detail….

[Former Member]

Thanks for article which is very clear! I have a similar request. I need to pull data from SAP ISU system and load into SQL Server (outside SAP world). The tables are very huge in size. So, can I go in the way you have suggested above using SFTP? I am not an SAP guy but I am from SQL background. Could you please help me with the detailed steps and any guidance to pull several large tables data to SQL Server.

[former_member348242]

Can you please explain your requirements..which table data you want extract to SQL server i.e sap standard table data or ztable data...

[Former Member]

Thanks for your response. I am exactly not sure about the table whether it is a SAP standard table or custom table. However, will there be any difference between those two tables?

[former_member348242]

we can extract both the table data incrementally using SFTP.In our scenario we extract the hourly huge sales data in CSV format.Hourly the CSV file size contains approximate 50 to 60 thousands rows .