Importing Data from SAP
Introduction
Data can be extracted from SAP R/3 using its RFC interface with the expression language ExtractSap function or SQL scripting --#ImportSapQuery command. In addition to the RFC interface, data can be fetched from SAP HANA with ODBC using the expression language ImportODBCSecure function or SQL scripting ImportOdbcQuery command.
QPR ProcessAnalyzer is using SAP's “SAP NetWeaver RFC SDK” that allows developers to connect to remote-enabled function modules in SAP (RFCs). In SAP QPR ProcessAnalyzer is calling RFC (Remote Function Module) BBP_RFC_READ_TABLE that is available in all (modern) SAP systems to read individual tables. This setup requires no installations of 3rd party components into the SAP. All that is needed is a user, password and right authorizations for that user.
Alternatively on HANA systems QPR ProcessAnalyzer can use ODBC connection to data.
This connection can be made from the QPR ProcessAnalyzer server as long as all firewall rules are set up correctly or via QPR ScriptLaucher which is used more often.
QPR ScripLauncher
Please find instructions on how to install QPR ScriptLauncher from our wiki: QPR ProcessAnalyzer ScriptLauncher - QPR ProcessAnalyzer Wiki (onqpr.com) ScriptLauncher needs to be installed into a machine within your intranet from which it can connect to both SAP and QPR ProcessAnalyzer server.
Authorizations / Requirements in SAP side
As stated above, connection will be done with SAPs .NET Connector to the Function module BBP_RFC_READ_TABLE. If that is not available older RFC_READ_TABLE can be used as well. Or if you have created custom versions of BBP_RFC_READ_TABLE those can be used as well: as long as the Remote Function Module’s interface is the same as in BBP_RFC_READ_TABLE. In order to establish a connection to the RFC BBP_RFC_READ_TABLE a user needs to be created in the SAP with following authorization objects:
- OBJECT: S_RFC (= What functions we can call)
- ACTVT = 16 (execute)
- RFC_NAME = RFC1, RFCPING, RFC_METADATA, SYST, SDTX ,BBPB
- RFC_TYPE = FGUR (Function Group)
- OBJECT: S_TABU_DIS (= What tables we can read)
- ACTVT = 03 (Display)
- DICBERCLS = * (all tables) OR individually named tables that QPR PA is allowed to read
User created for the extractions can be a system user without any access to SAP GUI.
Note: if RFC call to the BBP_RFC_READ_TABLE can be established with other authorization settings, that is OK for QPR.
Alternative for HANA
For HANA systems QPR ProcessAnalyzer can create the connection via HANA ODBC Connection as well as via SAP NetWeaver RFC Library.
Data conversion in RFC interface
Data extracted from SAP using the RFC interface has specific data types. Data will be converted into respective data types in QPR ProcessAnalyzer as follows:
| SAP data type | Expression language: ReadSap function |
SQL scripting: --#ImportSapQuery |
|---|---|---|
| I (integer) | long | INT |
| F (floating point number) | double | FLOAT |
| P (fixed point number, packed number) | double | FLOAT |
| D (date) | datetime | DATETIME2 |
| T (time) | timespan | TIME |
| C (text) | string | NVARCHAR(length) or NVARCHAR(max) if length is greater than 8000 |
| N (numeric text) | string | NVARCHAR(length) or NVARCHAR(max) if length is greater than 8000 |
| X (binary data) | byte[] | VARBINARY(length) or VARBINARY(max) if length is greater than 8000 |
Conversions are determined by the ConvertDataTypes setting for each data type separately. If no conversions are explicitly set, --#ImportSapQuery (in SQL scripting) does not make conversions (to maintain backwards compatibility), and ExtractSap function (in expression language) will make all possible conversion. When there is no conversion defined, data is converted to strings as defined by the table above.
Date (D) and time (T) type of fields are usually related to the same timestamp and thus they are bound together as follows: If T type of column is defined directly after the the D type of column, the time part in the T column is added to the D column, and no new column is created for the the T column. This is done only if both D and T types are among the converted types.
In the C datatype, spaces from the beginning and end are removed (trimmed).
More information about SAP data types: https://help.sap.com/saphelp_nwpi71/helpdata/en/fc/eb2fd9358411d1829f0000e829fbfe/content.htm?no_cache=true.