How to call an external API to update data in NextTables for SAP BW

You will learn

How to call an external API from NextTables the moment a row is saved, and write the response back into the row. The pattern has five moving parts that only work as a whole: result columns locked in configuration, an Update BAdI that calls the service, one small ABAP class that talks to the outside world, a custom button with a process chain for the mass case, and the same class reused in a BW transformation so your ETL derives identical values.

This article is for ABAP developers, and for the admins who configure the table beside them. The worked example calls the Google Geocoding API to turn addresses into coordinates; the pattern is the same for any HTTP service.

The pattern, and when to reach for it

Some values belong in a row but should never be typed into it, because a system outside SAP BW knows them better: coordinates for an address, a validated VAT or bank identifier, a normalized company name, a current exchange rate. The pattern in this article derives such values at the moment a user saves, so the person maintains only the input field and the system fills the rest.

These are the moving parts, and every one of them has a job:

The worked example

A user types or pastes an address into a NextTables table. On save, NextTables writes the address through to the ADSO, and alongside it the latitude, longitude and the normalized address that the geocoding service resolved. The user never types a coordinate and cannot overwrite one.

Geocoding shows the pattern well because it exercises every part: single rows and spreadsheet pastes, a billable third-party call worth guarding, and the same addresses arriving again through a data flow. Once the coordinates sit in the ADSO they behave like any other characteristic: you can build map visualizations on them, cluster records by location, or join them to anything else in the warehouse.

Prerequisites

The columns in the example

Define result columns to match what your service returns. For geocoding, these six have proven themselves; the last three are worth copying into any variant of the pattern, whatever the service does:

Your API class: the contract

NextTables does not ship a client for any particular service, and this article deliberately shows only the contract: which provider you use, how you authenticate and what you are contractually allowed to send are your decisions. Whatever runs behind the method, the NextTables side of the pattern stays identical. The BAdI needs one class from you, with one static method and a predictable result structure. For the geocoding example:

CLASS zcl_google_geo_api DEFINITION PUBLIC FINAL CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES: BEGIN OF ts_geo_result,
             formatted_address TYPE string,
             lat               TYPE p LENGTH 9 DECIMALS 6,
             lng               TYPE p LENGTH 9 DECIMALS 6,
             status            TYPE string,
           END OF ts_geo_result.

    CLASS-METHODS get_geodata
      IMPORTING i_address TYPE string
                i_api_key TYPE string OPTIONAL
      EXPORTING es_result TYPE ts_geo_result.

ENDCLASS.

The implementation is short. It URL-encodes the input into the provider's endpoint, issues the request with cl_http_client=>create_by_url, parses the JSON response, and returns the first result together with the provider's status. Roughly forty lines, most of which is error handling. For a different service, only this class changes; every step below stays as it is.

Step-by-Step Instructions

1) Lock the derived columns (configuration, no code)

The result columns are written by the system and must never be typed into. This is a pure configuration setting.

Go to Settings → Configurations, open your table, and in the Column Configuration set Edit options to Locked: keep target value for LATITUDE, LONGITUDE, ADDRESS_FORMATTED, GEO_MSG, DATE_PROCESSED and TIME_PROCESSED. All column settings are documented in Table and column configuration in NextTables for SAP BW.

"Keep target value" is the important half of that setting: the field is not editable in the front end, and whatever your BAdI writes in step 3 survives the save. Leave the input field itself editable. That is the one thing the user is supposed to change.

2) Set the BAdI filter

The derivation happens in the Update BAdI, in the step just before the database write. See How to implement a BAdI for NextTables if you have not created a BAdI implementation before.

In the Filter Values of your BAdI implementation, enter the table name and table type of your ADSO, so the implementation only runs for this table. A BAdI without a filter runs for everything and will call your service for tables you never intended to touch.

3) Implement SET_UPDATE_EXIT

METHOD /nly/if_editor~set_update_exit.

  FIELD-SYMBOLS:
    <lt_data> TYPE ANY TABLE,
    <ls_row>  TYPE /bic/azdrgeodt2.   " active table of the ADSO

  CHECK co_table IS BOUND.
  ASSIGN co_table->* TO <lt_data>.

  " Only derive on the way in, and only once, just before the write
  CHECK i_type = /nly/cl_table_rest_v3=>co_type_update
     OR i_type = /nly/cl_table_rest_v3=>co_type_insert.
  CHECK i_step = /nly/cl_table_rest_v3=>co_step_before_update.

  DESCRIBE TABLE <lt_data> LINES DATA(lv_lines).

  IF lv_lines > 500.
    " Too many rows to call the service for synchronously - see step 5
    ct_messages = VALUE #( BASE ct_messages
      ( type      = /nly/cl_table_rest_v3=>co_msg_type_info
        visu_type = /nly/cl_table_rest_v3=>co_visu_type_modal
        hdr       = 'More than 500 records'
        msg       = 'Ad-hoc geocoding is disabled above 500 records. ' &&
                    'Save the rows and start the geocoding process chain from the toolbar.' ) ).
    RETURN.
  ENDIF.

  LOOP AT <lt_data> ASSIGNING <ls_row>.

    CHECK <ls_row>-address IS NOT INITIAL.

    zcl_google_geo_api=>get_geodata(
      EXPORTING i_address = CONV string( <ls_row>-address )
      IMPORTING es_result = DATA(ls_geo) ).

    <ls_row>-address_formatted = ls_geo-formatted_address.
    <ls_row>-latitude          = ls_geo-lat.
    <ls_row>-longitude         = ls_geo-lng.
    <ls_row>-geo_msg           = ls_geo-status.
    <ls_row>-date_processed    = sy-datum.
    <ls_row>-time_processed    = sy-timlo.

  ENDLOOP.

ENDMETHOD.

Three things in this code matter:

• co_table holds only the rows being written. Looping over it calls the service for exactly the records the user just touched, without any "has this changed?" comparison.
• The CHECK i_step line prevents duplicate calls. The Update exit runs several times per save; without the check you would call, and pay for, the service more than once per row. When each event fires and what every parameter carries is documented in The Update BAdI (SET_UPDATE_EXIT) in NextTables for SAP BW.
• Writing sy-datum and sy-timlo into the processed fields gives you a cheap way to find stale rows later, and to prove when a value was derived.

4) Activate everything

Activating the implementing class alone is not enough. Activate all three:

• the implementing class together with its methods
• the BAdI implementation
• the enhancement implementation

5) Put the mass case behind a button and a process chain

An external call is an HTTP round trip per row. At a handful of rows nobody notices; at several thousand the user is staring at a spinner and the dialog work process is at risk of timing out. That is what the 500-row guard in step 3 is for: above the threshold it refuses to call the service inline and tells the user what to do instead.

The instruction it gives them is to start a process chain that processes the not-yet-derived rows in the background. Put that chain behind a custom button in the NextTables toolbar, and the whole pattern stays inside the application: paste the rows, save, press the button, come back to a completed table. How to start a process chain with NextTables covers the button and the RSPC calls. The chain itself selects the rows where DATE_PROCESSED is initial and runs the same zcl_google_geo_api class over them.

6) Reuse the same logic in a BW transformation

Everything above derives values for rows a person maintained. The same inputs usually also arrive through a data flow, and if the ETL route derives its values differently, you end up with two versions of the truth in the same warehouse.

Because the service call lives in a class rather than in the BAdI, there is nothing to duplicate. Call it from an end routine in the transformation:

* Transformation, end routine
LOOP AT RESULT_PACKAGE ASSIGNING FIELD-SYMBOL(<ls_result>).

  CHECK <ls_result>-address IS NOT INITIAL.

  zcl_google_geo_api=>get_geodata(
    EXPORTING i_address = CONV string( <ls_result>-address )
    IMPORTING es_result = DATA(ls_geo) ).

  <ls_result>-latitude          = ls_geo-lat.
  <ls_result>-longitude         = ls_geo-lng.
  <ls_result>-address_formatted = ls_geo-formatted_address.
  <ls_result>-geo_msg           = ls_geo-status.

ENDLOOP.

In a transformation you should also cache: if the same input appears in ten thousand records, look it up once. A sorted table keyed on the input, checked before the call, typically removes the large majority of the requests in a real load.

Best practices

• Keep the service call in one class. It is the only thing that makes the interactive path and the ETL path agree, and the only place you have to change when you switch provider.
• Never let a user type a derived value. Lock the result columns in configuration (step 1). A hand-typed value that disagrees with the input is almost impossible to spot later.
• Store the provider's status alongside the result. A row with ZERO_RESULTS and empty values is a data-quality finding; a row with values and no status is a mystery.
• Fail soft. If the service is unreachable, write the status and leave the result columns empty rather than raising an exception. Otherwise an outage at the provider blocks users from saving their data at all.
• Re-derive only when the input changes. The Update exit already gives you only the changed rows; if you later add a scheduled refresh, drive it from DATE_PROCESSED.

Troubleshooting / FAQs