ExtractSap Function

From QPR ProcessAnalyzer Wiki
Revision as of 11:33, 14 February 2023 by MarHink (talk | contribs) (Added some more detils to example description)
Jump to navigation Jump to search

ExtractSap function extracts data from an SAP R/3 system and returns it as a DataFlow. The function is in the generic context and also in the project context. When using the secure strings, the function need to be called in the project context as the secure strings are project specific.

Using the ExtractSap function requires installing SAP NetWeaver RFC Library.

Parameters:

  • UseGateway: Boolean value indicating whether data extraction should be performed through the gateway (true) or by QPR ProcessAnalyzer Server (false, default value). The gateway may be needed to access on-premise systems that are not available in the public network.
  • User: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant in SAP.
  • Password: Password for the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant in SAP.
  • PasswordKey: Secure string key for the stored SAP password. Alternative for the Password property.
  • Client: The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant in SAP.
  • AppServerHost: Hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if MessageServerHost is not defined. Corresponds to the "ASHOST" constant in SAP.
  • MessageServerHost: Hostname or IP of the SAP system’s message server (central instance). Mandatory if AppServerHost is not defined. Corresponds to the "MSHOST" constant in SAP.
  • SystemNumber: SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" constant in SAP.
  • SystemID: SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
  • Options: Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
  • OptionString: String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 characters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
  • FieldNames: Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
  • Function: SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
  • Language: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
  • Rowcount: Maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab 'Import' or function module 'rfc_read_table' in SAP. Optional.
  • Rowskips: Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
  • PoolSize: Maximum number of RFC connections that this destination will keep in its pool. Default value is "5". Optional. Corresponds to the "POOL_SIZE" constant in SAP.
  • IdleTimeout: If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
  • Router: List of host names and service names or port numbers for the SAPRouter in the following format: /H/hostname/S/portnumber. Optional. Corresponds to the "SAPROUTER" constant in SAP.
  • LogonGroup: The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant in SAP.
  • Mode: If this number is set to "1", then the query result will have the SAP Table field names as data table column names and actual data rows as rows. If this is set to "3", the query result will get the field descriptions from the SAP query using NO_DATA parameter, i.e. the returned columns are the following (in this order): Field, Type, Description, Length, Offset. Default value is "1". Optional.
  • QueryTable: Name of the SAP table to be extracted. Specifies the value for the parameter QUERY_TABLE in tab 'Import' or function module 'rfc_read_table' in SAP. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.
  • ConvertDataTypes: List of SAP data types that are converted into respective expression language data types. Defined by listing the data type identifier characters in any order. Available data type identifying characters are IFPCDTNX. If not defined, all data types are converted. Example: IFP (convert only numeric data types: Integer, Float, Packed number).
  • Ping: If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
  • TraceLevel: Sets SAP trace level, which can be 0, 1, 2, 3 or 4. Setting higher trace level helps to troubleshoot SAP connection issues. sapnco.dll writes log file to the current working folder with for example name'nco_rfc_5484_2.trc'. Optional.

Examples:

An example of performing a SAP extraction and persisting the extracted data to a table having id 1. Note: Data table must exist already when using this approach. 

let sapConnection = #{ 
  "AppServerHost": "sap01",
  "SystemNumber": "00",
  "User": "user1",
  "PasswordKey": "SapPW1",
  "Router": "",
  "SystemID": "QPR",
  "Client": "800",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600 
 };
let queryParameters = sapConnection.Extend({
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
  "QueryTable": "VBAK",
  "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"]
});
let resultsFlow = ProjectById(1)
  .ExtractSap(queryParameters);
DatatableById(1)
  .Import(resultsFlow);

An example of performing a SAP extraction and persisting the extracted data to a table named "SAPData" in project having id 1. If the data table does not yet exist, a new one is created into Snowflake.

let sapConnection = #{ 
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
 };
let queryParameters = sapConnection.Extend({
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
  "QueryTable": "VBAK",
  "Options": ["VBELN EQ '0060000039'", "OR VBELN EQ '0060000040'"]
});
ProjectById(1)
  .ExtractSap(queryParameters)
  .Persist("SAPData", #{"ProjectId": 1, "Connection": CreateSnowflakeConnection(#{"ProjectId": 1})})

An example of performing a SAP extract (using ScriptLauncher as a gateway), a simple transformation for extracted data and import to a table named "TransformedSAPData" in project having id 1.

function ExtractTransformAndLoad(extractFunc, transformFunc, loadFunc)
{
  let rawDataFlow = extractFunc();
  let transformedDataFlow = ToDataFlow();

  _system.Parallel.Run([
    () => Catch({
      let df;
      while (!IsNullTop(df = rawDataFlow.Collect(#{"CollectChunk": true}))) {
        transformedDataFlow.Append(transformFunc(df));
        WriteLog(`A chunk having ${df.NRows} rows has been transformed.`);
      }
      if (rawDataFlow.HasError) {
        transformedDataFlow.Fail("Error occurred during data extraction.");
      }
      else {
        transformedDataFlow.Complete();
      }
    }, {
      transformedDataFlow.Fail("Error occurred during transformation calculation.");
    }),
    () => {
      loadFunc(transformedDataFlow);
    }
  ]);
}

let sapConnection = #{ 
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
};

ExtractTransformAndLoad(
  () => ExtractSap(
    sapConnection.Extend([
      "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
      "QueryTable": "VBAK",
      "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0060000042'"],
      "UseGateway": true
    ])
  ),
  df => df.SetColumns(["Test": () => `${Column("NETWR")} ${Column("WAERK")}`]),
  dataFlow => {
    dataFlow.Persist("TransformedSAPData", ["ProjectName": "TestData", "Append": 0]);
  }
);
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=ExtractSap_Function&oldid=22525"