SAP, Accessing DSO tables Through BAPI

Goal

This document describes how to access DSO tables using the BAPI connector.  It also describes how to transform data in the data_array field of a RFC_READ_TABLE base view to a simple table.  Finally, the document gives tips for using SAP supported filters.

Content

To create a base view for a DSO table follow these steps:

1. Configure the BAPI data source.
2. Click “Create base view”.  Supply the RFC_READ_TABLE call as the BAPI name.  This RFC allows reading tables in the underlying SAP database.

The base view will always have the following fields: delimiter, no_data, query_table, rowcount, rowskips, data_array, fields_array, and options_array.

To query the base view, set the query_table field to the physical table name. For instance: ‘query_table’ = ‘/BIO/EXAMPLE_TABLE’.

The images below show a query example and the result obtained 

One thing to keep in mind is that the size of the output of the RFC call is limited. If the DSO table has too many columns  the error  “BUFFER_EXCEED” will appear.  The solution is to project only the required columns.  The columns to project can be specified as a condition.  For instance: “SELECT * FROM bv_rfc_read_table WHERE query_table = ‘/BIO/EXAMPLE_TABLE’and (fields_array).fieldname = 'MANDT' “

The actual data from the DSO table is in the data_array field. The DSO table field names are in the fields_array field.   The image below displays data from the data_array field.

To transform the data in the data_array field of an RFC_READ_TABLE base view to a simple view follow these steps:

1. Create a Selection view over the RFC_READ_TABLE base view.  Configure it as follows:

1. In the Where Conditions tab define two conditions.

1. The first condition just selects the physical table name using the query_table field.  For instance:

 ‘bv_rfc_read_table.query_table’ = ‘/BIO/EXAMPLE_TABLE’

2. The second condition sets the delimiter to a semicolon.  For instance:

 ‘bv_rfc_read_table.delimiter’ = ‘;’

Note: This semicolon will separate each piece of data in the data_array field. Specifying the delimiter aids in the join between the data_array field and a delimited view.  The join is the final step of transforming the data into a simple table.

2. In the Output tab all the fields except for data_array and fields_array can be removed.

The image below displays an example configuration for the selection view:

2. Create a Flatten view over the selection view.  Flatten the data_array field by clicking on the settings icon of the data_array field. In the output tab remove the fields_array, leaving only the data field, named wa.

The images below display the configuration for the flatten view.

3. Create a Delimited file data source. The base view from this data source will be used to parse the data in the data_array field from the flatten view created in the previous step. Configure it as follows:

1. Choose From Variable as Data Route and set the variable name to something like input_data.
2. Use ; as Column delimiter. The column delimiter will depend on how the data will be returned from the data source in the data_array field, in this example, a semicolon.
3. This same data source can be used to create base views for other RFC base views.
4. Save the data source.

4. Execute the flatten view created in the previous step.  Copy the data from the first row of the results.
5. Open the delimited file data source and create a new base view.  Paste the copied data into the input_data field and click OK.

1. This base view can also be used to query other RFC views with the same number of columns.

6. Create a Join view over the delimited file base view:

1. Drag the flattened BAPI view into the Join workspace.
2. Join between the input_data field of the delimited file base view and the wa data field of the flattened view.
3. In the Output tab remove the unnecessary fields (input_data and wa).
4. At this point, it is possible to also change the column names from the delimited file base view to match the field names of the RFC table.  The field names can be acquired by executing the selection BAPI view created in step two and looking into the field_array field.

The images below are an example of the configuration for the join view.

7. Execute the Join view to see the data stored in the original columns.

SAP supports filters. To include filters for the Virtual DataPort views it is possible to use the (options_array).text field in a where condition. The input for the(options_array).text will be the same as in an SQL query.  

For instance, to select data that has the value “MAT003” in the column “/BIC/IO_MAT” the condition would look like this:

(options_array).text = '/BIC/IO_MAT = ''MAT003'''

Note: Denodo provides a RFC_READ_TABLE custom wrapper which can be used to query tables from the SAP system. The custom wrapper can be found in the DenodoConnects under the Downloads section of the support site. For more details, refer to Denodo RFCReadTable Custom Wrapper - User Manual. This wrapper simplifies the steps to create the base view by automatically creating view schema without the need to create data combinations manually.