Importing Data from SAP: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
 
(49 intermediate revisions by the same user not shown)
Line 1: Line 1:
Data can be imported from SAP based on the RFC interface using [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSapQuery|--#ImportSapQuery]] command in SQL scripting and ReadSap function in the expression language. Data fetched from SAP contains datatype information, and data is converted into respective datatypes of the in-memory core and SQL Server as follows:
==Introduction to connecting SAP==
Data can be extracted from SAP R/3 using its RFC interface. In QPR ProcessAnalyzer, the extraction is done using the expression language [[ExtractSap_Function|ExtractSap function]] or SQL scripting [[SQL_Scripting_Commands#--.23ImportSapQuery|--#ImportSapQuery]] command. In addition to the RFC interface, data can be fetched from SAP HANA through its ODBC connection using the expression language [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function or SQL scripting [[SQL_Scripting_Commands#--.23ImportOdbcQuery|ImportOdbcQuery]] command. Note that the RFC interface also available in SAP HANA.
 
For the RFC interface, QPR ProcessAnalyzer is using SAP NetWeaver RFC Library 7.50 provided by SAP allowing developers to connect to remote-enabled function modules in SAP. In SAP, QPR ProcessAnalyzer is calling the 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.
 
In QPR ProcessAnalyzer, scripts can be run in the server side mode which will make SAP connection in the server machine, or using QPR ScriptLaucher which will make SAP connection in the QPR ScriptLaucher itself. Connection to SAP can be made from QPR ProcessAnalyzer server as long as all firewall rules are set up correctly to access SAP. It that's not possible, use QPR ScriptLaucher which need to be installed into an on-premise machine inside company firewall to reach SAP. In addition, QPR ScriptLauncher needs have a connection QPR ProcessAnalyzer server. Please find instructions how to [[QPR_ProcessAnalyzer_ScriptLauncher#Installing_ScriptLauncher_.28starting_from_2022.6.29|install QPR ScriptLauncher]].
 
==ETL script examples==
Follow the links to find example scripts:
* [[Example_to_Extract_Data_from_SAP|Extract data from SAP into Snowflake staging tables]]
* [[Example_to_Transform_SAP_Data_into_Eventlog_in_Snowflake|Transform staged SAP data into eventlog using Snowflake]]
 
==Authorizations / Requirements in SAP==
If the BBP_RFC_READ_TABLE is not available in SAP, the older RFC_READ_TABLE can be used as well. If you have custom versions of the BBP_RFC_READ_TABLE, those can be used as well if 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 (defines what functions we can call)
** ACTVT = 16 (execute)
** RFC_NAME = RFC1, RFCPING, RFC_METADATA, SYST, SDTX, BBPB
** RFC_TYPE = FGUR, SDIFRUNTIME (this is the function group)
* Object: S_TABU_DIS (defines what tables we can read)
** ACTVT = 03 (Display)
** DICBERCLS = * (this means all tables), or individually named tables that QPR ProcessAnalyzer needs 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 enough for QPR ProcessAnalyzer to successfully extract data.
 
== 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:


{| class="wikitable"
{| class="wikitable"
! '''SAP data type'''
! '''SAP data type'''
! '''Expression language:<br>ReadSap'''
! '''Expression language:<br>ReadSap function'''
! '''SQL scripting:<br>--#ImportSapQuery'''
! '''SQL scripting:<br>--#ImportSapQuery'''
|-
|-
||I
||'''I''' (integer)
||long
||long
||INT
||INT
|-
|-
||F
||'''F''' (floating point number)
||double
||double
||FLOAT
||FLOAT
|-
|-
||P
||'''P''' (fixed point number, packed number)
||double
||double
||FLOAT
||FLOAT
|-
|-
||C
||'''D''' (date)
||string
||NVARCHAR(length) or<br>NVARCHAR(max) (if length is greater than 8000)
|-
||D
||datetime
||datetime
||DATETIME2
||DATETIME2
|-
|-
||N
||'''T''' (time)
||timespan
||TIME
|-
||'''C''' (text)
||string
||string
||NVARCHAR(length) or<br>NVARCHAR(max) (if length is greater than 8000)
||NVARCHAR(length) or<br>NVARCHAR(max) if length is greater than 8000
|-
|-
||T
||'''N''' (numeric text)
||timespan
||string
||TIME
||NVARCHAR(length) or<br>NVARCHAR(max) if length is greater than 8000
|-
|-
||X
||'''X''' (binary data)
||byte[]
||byte[]
||VARBINARY(length) or<br>VARBINARY(max) (if length is greater than 8000)
||VARBINARY(length) or<br>VARBINARY(max) if length is greater than 8000
|}
|}


Notes:
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|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.
* If no conversion is defined, data is converted to strings. By default --#ImportSapQuery (in SQL scripting) does not make any conversions, and by default ReadSap (in expression language) makes all conversion.
 
* In the C datatype, spaces from both side are trimmed away.
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.
* If T type of column appears directly after the the D type of column, the time part in the T column is taken into account in the D column, and no new column in the result is created for the the T column. This occurs 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.
More information about SAP data types: https://help.sap.com/saphelp_nwpi71/helpdata/en/fc/eb2fd9358411d1829f0000e829fbfe/content.htm?no_cache=true.


In addition to the RFC interface, data can be fetched from SAP HANA with ODBC using the [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function and [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportOdbcQuery|ImportOdbcQuery]].
== SNC encrypted connection ==
To improve security, connection to SAP can be encrypted using SNC, a technique provided by SAP. To take the SNC into use, you need a dll file from SAP that contains the encryption functionality (e.g., sapcrypto.dll). Copy the file to the same location as the other SAP dll's (i.e., icudt50.dll, icuin50, icuuc50.dll, libsapucum.dll, sapnwrfc.dll). In the QPR ProcessAnalyzer data extraction command, SNC is set in the individual extractions commands (ExtractSap function and --#ImportSapQuery command) by providing the needed [[SQL_Scripting_Commands#--.23ImportSapQuery|parameters]]. To enable SNC, set the '''SncMode''' parameter to '''1''', and set the '''SncLibraryPath''' parameter to contain path to the encryption dll file (e.g., sapcrypto.dll). You may also need to configure '''SncMyName''', '''SncPartnerName''' and '''SncQop'''.
 
[[Category: QPR ProcessAnalyzer]]

Latest revision as of 09:43, 12 February 2024

Introduction to connecting SAP

Data can be extracted from SAP R/3 using its RFC interface. In QPR ProcessAnalyzer, the extraction is done using the expression language ExtractSap function or SQL scripting --#ImportSapQuery command. In addition to the RFC interface, data can be fetched from SAP HANA through its ODBC connection using the expression language ImportODBCSecure function or SQL scripting ImportOdbcQuery command. Note that the RFC interface also available in SAP HANA.

For the RFC interface, QPR ProcessAnalyzer is using SAP NetWeaver RFC Library 7.50 provided by SAP allowing developers to connect to remote-enabled function modules in SAP. In SAP, QPR ProcessAnalyzer is calling the 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.

In QPR ProcessAnalyzer, scripts can be run in the server side mode which will make SAP connection in the server machine, or using QPR ScriptLaucher which will make SAP connection in the QPR ScriptLaucher itself. Connection to SAP can be made from QPR ProcessAnalyzer server as long as all firewall rules are set up correctly to access SAP. It that's not possible, use QPR ScriptLaucher which need to be installed into an on-premise machine inside company firewall to reach SAP. In addition, QPR ScriptLauncher needs have a connection QPR ProcessAnalyzer server. Please find instructions how to install QPR ScriptLauncher.

ETL script examples

Follow the links to find example scripts:

Authorizations / Requirements in SAP

If the BBP_RFC_READ_TABLE is not available in SAP, the older RFC_READ_TABLE can be used as well. If you have custom versions of the BBP_RFC_READ_TABLE, those can be used as well if 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 (defines what functions we can call)
    • ACTVT = 16 (execute)
    • RFC_NAME = RFC1, RFCPING, RFC_METADATA, SYST, SDTX, BBPB
    • RFC_TYPE = FGUR, SDIFRUNTIME (this is the function group)
  • Object: S_TABU_DIS (defines what tables we can read)
    • ACTVT = 03 (Display)
    • DICBERCLS = * (this means all tables), or individually named tables that QPR ProcessAnalyzer needs 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 enough for QPR ProcessAnalyzer to successfully extract data.

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.

SNC encrypted connection

To improve security, connection to SAP can be encrypted using SNC, a technique provided by SAP. To take the SNC into use, you need a dll file from SAP that contains the encryption functionality (e.g., sapcrypto.dll). Copy the file to the same location as the other SAP dll's (i.e., icudt50.dll, icuin50, icuuc50.dll, libsapucum.dll, sapnwrfc.dll). In the QPR ProcessAnalyzer data extraction command, SNC is set in the individual extractions commands (ExtractSap function and --#ImportSapQuery command) by providing the needed parameters. To enable SNC, set the SncMode parameter to 1, and set the SncLibraryPath parameter to contain path to the encryption dll file (e.g., sapcrypto.dll). You may also need to configure SncMyName, SncPartnerName and SncQop.