Importing Data from SAP: Difference between revisions
No edit summary |
No edit summary |
||
| (16 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Data can be | Data can be extracted from SAP R/3 using the RFC interface with the expression language [[ExtractSap_Function|ExtractSap function]] and SQL scripting [[SQL_Scripting_Commands#--.23ImportSapQuery|--#ImportSapQuery]] command. 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 [[SQL_Scripting_Commands#--.23ImportOdbcQuery|ImportOdbcQuery]]. | ||
== 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 | ||
|- | |- | ||
|| | ||'''D''' (date) | ||
||datetime | ||datetime | ||
||DATETIME2 | ||DATETIME2 | ||
|- | |- | ||
|| | ||'''T''' (time) | ||
||timespan | |||
||TIME | |||
|- | |||
||'''C''' (text) | |||
||string | ||string | ||
||NVARCHAR(length) or<br>NVARCHAR(max) | ||NVARCHAR(length) or<br>NVARCHAR(max) if length is greater than 8000 | ||
|- | |- | ||
|| | ||'''N''' (numeric text) | ||
|| | ||string | ||
|| | ||NVARCHAR(length) or<br>NVARCHAR(max) if length is greater than 8000 | ||
|- | |- | ||
||X | ||'''X''' (binary data) | ||
||byte[] | ||byte[] | ||
||VARBINARY(length) or<br>VARBINARY(max) | ||VARBINARY(length) or<br>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|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. | More information about SAP data types: https://help.sap.com/saphelp_nwpi71/helpdata/en/fc/eb2fd9358411d1829f0000e829fbfe/content.htm?no_cache=true. | ||
[[Category: QPR ProcessAnalyzer]] | [[Category: QPR ProcessAnalyzer]] | ||
Revision as of 12:20, 2 December 2022
Data can be extracted from SAP R/3 using the RFC interface with the expression language ExtractSap function and SQL scripting --#ImportSapQuery command. In addition to the RFC interface, data can be fetched from SAP HANA with ODBC using the ImportODBCSecure function and ImportOdbcQuery.
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.