SAP to SQL Server PowerBI Integration without PI licences
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
- 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.
- HTTP_SCRAMBLE -For Encrypting the Password
- FTP_CONNECT -For connecting to the FTP Server’s user directory
- SAP_CONVERT_TO_TEX_FORMAT –To convert my internal table data into flat file
- 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.
- FTP_DISCONNECT-For Disconnecting the connected FTP Session
- 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:
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….