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….