Reading Data from Multiple SAP Tables

The Read from SAP stage can be configured to read data from a single table in the SAP database or multiple tables. This procedure describes how to configure Read from SAP to read data from a multiple tables. To read data from multiple tables, you define a JOIN statement to combine data into a single stream.

  1. In Spectrum Enterprise Designer, drag Read from SAP onto the canvas.
  2. Double-click the Read from SAP stage on the canvas.
  3. In the Connection field, select the SAP server that contains the data you want to read into the dataflow. If there is no connection defined for the SAP server you need to create the connection by clicking Manage.
  4. In the Source type field, choose Multiple.
  5. Click Add.
  6. Select the tables you want to read into the dataflow then click OK.
    Note: Only the first 200 tables are listed. Use the search feature to find tables not listed in the first 200. The search field only searches the values in the Name and Label columns.
  7. Select the first table in the list and click Create Relationship. This is the source table.
  8. In the Source key field, select the column from the source table whose value will be used match records to records from the other table.
  9. In the Join type field, select one of the following:
    INNER JOIN
    Returns only those records that have a match between the source and target tables.
    LEFT JOIN
    Returns all records from the source table even if there are no matches between the source and target tables. This option returns all records from the source table plus any records that match in the target table.
  10. In the Table field, select the target table.
  11. In the Table key field, select the column in the target table containing the data you want to compare to the data from the Source key field to determine if the record meets the join condition.
  12. Click OK.
  13. Click Select Schema.
  14. Choose the fields that you want to read into the dataflow. To view the field names that will be used in the dataflow, check the Display technical name box.

    Fields in SAP have a user-friendly name used for display purposes and a unique name that may be less readable. For example, a field may have a user-friendly name of "Distribution Channel" and a technical name of "DIS_CHANNEL". In order to ensure that the field name is valid in the dataflow, the technical name is used as the field name.

  15. Click OK.
  16. If you want to read only certain records, you can specify filter conditions on the Filter tab. In order for a record to be read into the dataflow it must meet all the conditions you define.
  17. You can improve performance by specifying an appropriate fetch size on the Runtime tab.

    Select this option to specify the number of records to read from the database table at a time. For example, if the Fetch size value is 100 and total number of records to be read is 1000, then it would take 10 trips to the database to read all the records.

    Setting an optimum fetch size can improve performance significantly.
    Note: You can calculate an optimum fetch size for your environment by testing the execution times between a Read from DB stage and a Write to Null stage. For more information, see Determining an Optimimum Fetch Size.

    The default fetch size for Read from SAP is 10,000.

The Read from SAP stage is now configured to read data from a multiple tables in the SAP database into the dataflow.