Extract ERP Table - Data360_Analyze - Latest

Standard configuration

The Extract ERP Table node uses the (unsupported) SAP function RFC_READ_TABLE to extract table data.

The RFC_READ_TABLE function takes an OPTIONS parameter which can be used effectively like a Where clause in a SQL statement. You can add an optional input on the node where each record in the input contains a different OPTIONS parameter to be executed. In this case, the function will be executed once per input record, with a different OPTIONS parameter per execution.

To configure the node with this behavior:

1. Connect the input to the node, and select the from Field variant of the Options property.
2. Enter the field name containing the OPTIONS. For each record in the input, the specified field must contain a valid OPTIONS string to be provided to the SAP RFC_READ_TABLE function.

Advanced configuration

When the size of the combined fields does not exceed 512 bytes, the default behavior is for the node to extract all records matching any conditions in the provided Options. If you do not want to extract all data, you can specify a maximum number of rows to extract from the table in the RowCount property.

If the size of the combined fields exceeds 512 bytes, the node will need to make multiple calls to extract the data. In this case, you can specify the number of records to extract at a time in the RowBatchSize property.

If the node cannot extract all data at once, it will extract key fields for all records that match the values specified in the Options and RowCount properties. An OPTIONS clause is used to make calls to extract the key fields. You can restrict the size of the query and number of records that are returned per extract by specifying a value in the MaxOptionsSize property.

Using an alternative function

As a workaround to the byte limit of the RFC_READ_TABLE function, you can specify an alternative function that does not have this limitation by configuring the following properties:

• RFC_READ_TABLE_Alternative - Specify the name of an alternative function if you do not want to use RFC_READ_TABLE. Note that the interface to the function must be the same as RFC_READ_TABLE.
• RowByteLimit - If you have specified an alternative function in the RFC_READ_TABLE_Alternative property, specify the byte limit of that function if it is different to the default RFC_READ_TABLE limit of 512 bytes.

If you are using an alternative function, there are also a set of optional properties that you can set to modify how the function behaves:

• MaxOptionsLineSize - The maximum number of characters for any given line of the OPTIONS parameter.
• ExtractedDateFormat - The format of the date fields in the response from the function call.
• ExtractedTimeFormat - The format of the date fields in the response from the function call.

If you want to set these properties at a system-wide level, you can add the relevant property string to your cust.prop file:

1. Navigate to the "cust.prop" file located at:

   <Data360Analyze site configuration directory>/conf/cust.prop

2. Open the cust.prop file and add the property on a new line, as per the text in the table above, for example:

   ls.brain.node.erp.sapconnector.extractTable.alternativeFunctionName=NEW_FUNCTION_NAME

3. Restart Data360 Analyze for the changes to take effect.

Outputs

The Extract ERP Table node has four outputs:

• Fields
• Data
• Error Records
• Error Details

Fields

The Fields output pin contains the metadata for the table that is being extracted, as follows:

Data

The Data output pin contains the extracted records on which no error was noticed. The specific fields that are defined on the Data output depend on the table that is extracted and the value that is specified in the Fields property. The type of these fields depends on the type of the corresponding field in the SAP table, and the value of the OutputDataConversion property.

Error Records

The Error Records output pin contains a record for each row in the SAP table that could not be extracted and converted correctly.

The output contains a long field __RecordNumber which identifies the record on which the error was noticed. The __RecordNumber field can be matched against the RecordNumber field on the Error Details output to locate the specific error(s) that were noticed on the record.

Additionally, the node will contain all of the same fields as that on the Data output. However, these will always be output as Unicode types and contain the raw, unconverted data for each of the fields prior to any attempt to convert them to the Data360 Analyze type that corresponds with the SAP ABAP type of the field.

Error Details

For each error that occurs when a SAP record is extracted, a record will be written to the Error Details output. This means that for each record in the Error Records output, there may be multiple records in the Error Details output.

The Error Details output defines the following fields:

Handling errors

You can configure the following properties to handle errors:

• UnmappableOutputBehavior
• ErrorThreshold

Properties

TableName

Specify the name of the SAP table to extract. A value is required for this property.

Fields

Optionally specify a comma separated list of fields which are to be extracted from the table. By default, all fields will be extracted.

Options

Optionally specify the OPTIONS string to be provided to the SAP RFC_READ_TABLE function. This property is like the Where clause in a SQL statement. Restrictions to what the node will extract should be set in this property.

Choose the (from Field) variant of this property to look up the value from an input field with the name specified.

If a field name is specified, each record on the input must contain the string that would be used in the TEXT field in the SAPOPTIONS import parameter and the RFC_READ_TABLE query will be executed once per input record.

The format of this property is the same as that which is required by the TEXT field in the SAP RFC_READ_TABLE import parameter OPTIONS.

RowCount

Optionally specify the maximum number of rows to extract from the table. By default, all records will be extracted.

RowCountPerOption

Optionally set this property if an input is provided to the node specifying the OPTIONS used to run the RFC_READ_TABLE function.

• If set to True, the value specified in the RowCount property is the maximum number of records to extract per function execution.
• If set to False, the value specified in the RowCount property is the maximum number of records to extract overall.

The default value is False.

ConnectionType

Specify the type of connection you are using. If you are not using a load balanced login, then you can connect to a Custom Application Server. Otherwise, you should select Group/Server Selection.

Choose from:

• Custom Application Server - Forms a connection to the specified SAP Application Server without using group load balancing for the logins.
• Group/Server Selection - Load balanced login using the SAP Group/Server mechanism.

A value is required for this property. For more information, see your SAP documentation.

When Custom Application Server is specified, the following properties are required:

• ApplicationServer
• SystemNumber

When Group/Server Selection is specified, the following properties are required:

• MessageServerHost
• Group/Server
• R3Name

In all cases, the following properties are required:

• User
• Password
• ClientNumber

ApplicationServer

Specify the name of the SAP Application server.

A value is required if ConnectionType is set to Custom Application Server. Should not be set if ConnectionType is set to Group/Server Selection.

Group/Server

Specify the group of SAP application servers.

A value is required if ConnectionType is set to Group/Server Selection. Should not be set if ConnectionType is set to Custom Application Server.

MessageServerHost

Specify the SAP message server host.

A value is required if ConnectionType is set to Group/Server Selection. Should not be set if ConnectionType is set to Custom Application Server.

MessageServerPort

Specify the SAP message server port.

Optional if ConnectionType is set to Group/Server Selection. Should not be set if ConnectionType is set to Custom Application Server.

SystemNumber

Specify the two-digit system number of the SAP system. Called the "Instance Number" in the SAP Logon GUI.

A value is required if the ConnectionType is set to Custom Application Server.

R3Name

Specify the system ID of the SAP system.

A value is required if the ConnectionType is set to Group/Server Selection.

ClientNumber

Specify the three digit client number for the SAP system.

Called the "SystemID" in the SAP Logon GUI. A value is required for this property.

RouterString

Optionally specify the SAP Router String in the following format:

/H/<IP address where your SAP router is located>/S/<port for your SAP router>/H/<SAP server host ip address>/H/

User

Specify the name of the SAP user.

A value is required for this property.

Password

Specify the password for the specified user on the SAP system.

A value is required for this property.

ConnectionMethod

Optionally specify the method used to connect to the SAP system. Choose from:

• Direct Connection - Connection will be held for the duration of the node run.
• Pooled Connection - Connection will be taken and returned as needed during node execution.

The default value is Pooled Connection.

PoolCapacity

Optionally specify an integer value specifying the pool capacity for the SAP system when using pooled connections.

The default value is 3.

See http://docs.oracle.com/cd/E14571_01/doc.1111/e17656/jco_updates.htm

PeakLimit

Optionally specify the peak limit when using pooled connections to the SAP system (specify an integer value).

The default value is 10.

PassThroughFields

Optionally specify which input fields will "pass through" the node unchanged from the input to the output, assuming that the input exists. The input fields specified will appear on those output records which were produced as a result of the input fields. Choose from:

• All - Passes through all the input data fields to the output.
• Used - Passes through all the fields that the node used to create the output.
• Unused - Passes through all the fields that the node did not use to create the output.
• None - Passes none of the input data fields to the output; as such, only the fields created by the node appear on the output.

If a naming conflict exists between a pass-through field and an explicitly named output field, an error will occur.

The default value is None.

RowBatchSize

Optionally specify the number of records to extract at a time from the SAP instance.

For extraction of large tables, attempting to extract all records at once can result in the call using large amounts of memory.

Therefore, you can use this property to ensure that the node will only query the SAP instance to obtain a maximum of the specified number of rows per call.

Note that the node must initially extract the key fields for all records (up to any specified RowCount) matching the provided query.

This property does not affect the number of key field records extracted.

Also, after extracting the key fields, if the node determines that there are more than RowBatchSize records to extract, it will not use this property. Instead it will use the values from the key fields records to construct queries to extract the data. The number of records it will attempt to retrieve during this operation is instead controlled by the MaxOptionsSize property.

The default value is 100,000.

MaxOptionsSize

Optionally specify the maximum number of lines in an OPTIONS clause to process at a time when the node must perform multiple queries and extract data based on key fields.

The node must initially extract the key fields for all records (up to any specified RowCount) matching the provided query.

After extracting the key fields, if the node determines that there are more than RowBatchSize records to extract, it will then make repeated calls to retrieve the remainder of the fields to output.

The subsequent calls are made by querying the table using an OPTIONS clause configured to extract records matching the key fields obtained in the initial extract.

Too many OPTIONS lines can result in a failure processing the SQL used by the RFC_READ_TABLE.

This property determines how many OPTIONS lines will be generated for each of the subsequent extracts, thereby restricting both the size of the query and the number of records returned per extract.

The default value is 3000.

ByteArrayAsHexString

Optionally specify whether byte array data should be returned as a hex string.

• If set to True, the data will be returned as a hex string.
• If set to False, the data will be returned in a base-64 encoded string.

The default value is True.

OutputDataConversion

Optionally specify how the data from SAP should be output to the Data360 Analyze fields. Choose from:

• Use Field Metadata - The metadata specified on the field within the table is used to determine the output type of the field in Data360 Analyze. Any errors that occur during this conversion will be logged to the Error output pin and the ErrorThreshold property will be used to determine node behavior.
• To String - All fields will be output as string.
• To Unicode - All fields will be output as unicode.

The default value is Use Field Metadata.

OutputRecordNumbers

Optionally specify whether or not the record number should be written on the Data, Records with Errors, and Error Details outputs.

If set to True, the record number is included in the outputs. This is useful for linking the error information back to the actual record where the error occurred.

The default value is True.

LargeDecimalAsDouble

Optionally specify whether to output SAP numeric types in string or numeric format.

Some of the SAP numeric types have a range or precision which cannot be fully handled by the double type in Data360 Analyze.

• If set to False, outputs SAP numeric types as a string to ensure that there is no loss of precision or accuracy.
• If set to True, outputs SAP numeric types in numeric format.

This affects the following SAP types: BCD (P) DECF16 (DECFLOAT16) DECF34 (DECFLOAT34).

The default value is False.

RowSkips

Optionally specify the number of rows in the table to ignore prior to extracting the first record.

If multiple executions are to be performed via providing an input which contains the OPTIONS to be executed, then these records will be skipped per function execution.

The default value is 0.

UniqueKeyFields

Optionally specify the fields in the table which form a unique key for records to extract.

The fields must be specified in a comma separated list.

If no value is specified in this property, the node will use the value set in the server property ls.brain.node.erp.sapconnector.extractTable.<tableName>-Keys. This server property can be set in the cust.prop file of your server instance and applies to all Extract ERP Table nodes that extract from the specified table.

If no such property exists or cannot be correctly parsed as a comma separated fields list, the node will use any pre-configured defaults it knows for the specified table.

Only a small subset of tables have pre-configured default values for the unique keys set, so if it cannot locate a pre-configured list of fields for the given table it will resort to using all fields specified as key fields in the table DD03L for the table to extract.

RFC_READ_TABLE_Alternative

This node uses the (unsupported) RFC_READ_TABLE function to extract the SAP table. Where there is a custom implementation of the RFC_READ_TABLE function, and the interface to the function is the same as RFC_READ_TABLE, specify the alternative function name in this property.

The RFC_READ_TABLE function has a number of limitations. The function does not work where any individual row is defined to contain more than 512 bytes. While this node is able to first determine the size of all of the fields to extract from the table and split the call appropriately such that this limit is not exceeded, it still requires that no individual field within the table is defined to be 512 bytes or larger. Due to these limitations, many SAP instances have a custom implementation of RFC_READ_TABLE, with a different function name.

If a different function is in use, provided that the interface to the function is the same as RFC_READ_TABLE, the alternative function name can be specified in this property. If the alternative function has a different byte limit per row, that should be set in the RowByteLimit property.

The default can be changed on a server wide basis via setting the ls.brain.node.erp.sapconnector.extractTable.alternativeFunctionName server property in the cust.prop file of your server instance.

RowByteLimit

Optionally specify the number of bytes per row that are allowed for the function to execute.

For custom RFC_READ_TABLE implementations, you can modify this property to match the byte limit on the alternative version of the RFC_READ_TABLE function.

Note that while the function itself will have a byte limit per row, this node will split up the table extraction, such that so long as no single field exceeds the byte limit in the table being extracted, the node will still operate correctly.

The default value is 512 (the limit of RFC_READ_TABLE).

The default can be changed on a server wide basis via setting the ls.brain.node.erp.sapconnector.extractTable.rowByteLimit server property in the cust.prop file of your server instance.

MaxOptionsLineSize

Optionally specify the number of characters per line in the Options that are allowed for the function to execute.

For custom RFC_READ_TABLE implementations, you can modify this property to match the character limit on the alternative version of the RFC_READ_TABLE function.

Note that while no single line will be sent to the function with more than the specified limit, the node will split up the Options property into multiple lines to send to SAP so long as the query can be split in such a manner.

Therefore even if the Options property is greater than this limit, the node will be able to execute the query by splitting it into multiple lines while adhering to the limits.

The default value is 72 (the limit of RFC_READ_TABLE).

The default can be changed on a server wide basis via setting the ls.brain.node.erp.sapconnector.extractTable.maxOptionsSize server property in the cust.prop file of your server instance.

ExtractedDateFormat

Optionally specify the format with which date fields will be returned by the function.

For custom RFC_READ_TABLE implementations, you can modify this property to match the date format returned by the alternative version of the RFC_READ_TABLE function.

The default value is yyyyMMdd (used by RFC_READ_TABLE).

The default can be changed on a server wide basis via setting the ls.brain.node.erp.sapconnector.extractTable.extractedDateFormat server property in the cust.prop file of your server instance.

ExtractedTimeFormat

Optionally specify the format with which time fields will be returned by the function.

For custom RFC_READ_TABLE implementations, you can modify this property to match the time format returned by the alternative version of the RFC_READ_TABLE function.

The default value is HHmmss (used by RFC_READ_TABLE).

The default can be changed on a server wide basis via setting the ls.brain.node.erp.sapconnector.extractTable.extractedTimeFormat server property in the cust.prop file of your server instance.

JCoProperties

Optionally specify any custom JCo properties to be used by the node. For example, setting trace properties.

Each line in this property represents an individual JCo property to use when interfacing with the SAP system. Each line must be of the format <key>=<value>.

If any of the provided properties conflict with the corresponding JCo properties for any other node property, the values in this property will take precedence.

MaxHeapSize

Optionally specify the JVM maximum heap size to be used by the node.

For large extracts, the node can require a significant amount of memory. Change this property in order to allow for the node to be allocated more memory.

The default value is 2048m. The default can be changed on a server wide basis via setting the ls.brain.node.java.erp.sapconnector.extractTable.maxHeapSize server property in the cust.prop file of your server instance.

ErrorThreshold

Optionally specify how many errors are allowed to occur prior to the node failing.

When an error occurs with extracting a field from the return SAP table data, the error will be written to the Error output pin. In such cases, processing can continue.

A value of 0 means that the node will fail on the first occurrence of such an error. A value of -1 means that the node will never fail due to such errors.

The default value is 0.

UnmappableOutputBehavior

Optionally specify the action to take when SAP data cannot be mapped to a Data360 Analyze data type (e.g. ABAPObject types). Choose from:

• Error
• Log
• Ignore

The default value is Error.

UnexpectedExtractKeysBehavior

Optionally specify the action to take when additional, unexpected data is retrieved for a table extract.

If the node needs to retrieve data in batches and data changes on the SAP system between the calls for the different batches, the retrieved results may be different across the calls.

This property determines what the node should do if it receives additional records on one of the extracts which were not returned from the original extract call.

Choose from:

• Error - The node will error with the appropriate error message.
• Log - The node will continue, however the errors will be written to the node log.
• Ignore - Such errors are ignored.

The default value is Log.

MissingExtractKeysBehavior

Optionally specify the action to take when records that are returned from an initial extract are missing from subsequent extracts.

If the node needs to retrieve data in batches and data changes on the SAP system between the calls for the different batches, the retrieved results may be different across the calls.

This property determines what the node should do if it receives records on the first extract which is missing in subsequent extracts.

Choose from:

• Error - The node will error with the appropriate error message. Errors will also be written to the error pin.
• Log - The node will continue, however the errors will be written to the error output pin. If the ErrorThreshold is exceeded, the node will error.
• Ignore - Such errors are ignored.

The default value is Log.

Inputs and outputs

Inputs: Multiple optional.

Outputs: Fields, Data, Error Records, Error Details, multiple optional.