Filename determination in SAP BW4 Transformation - Flatfile Datasource

It is often required with flat file interfaces that files should not be processed twice. Ideally, the files are processed once after writing to the application server of SAP BW. In the following I explain a method how this can be done.

The procedure for dynamic determination of files to be loaded, can be achieved by routines. These routines use some logic to change the filenames in the adapter setting in the DTP so that different files can be loaded in the application server by the DTP.

Usually a logic is used, in which the routine recognizes whether a file was already loaded. For this we store via a transformation routine the already loaded filenames in the target aDSO. By means of these entries we can recognize whether a file was already processed and accordingly load the unprocessed file.

Saving the filenames in aDSO

To store the filenames of the already loaded files in the target ADSO, we use the SAP table RSFILENAMEDONE. In this table the filenames are stored with their corresponding request IDs.

This allows us to fill a variable with the filename of the loaded file in the start routine of the transformation. This is possible by selecting the 'filename' from the table RSFILENAMEDONE via the 'request'. Now this variable has only to be passed to the field via a field routine.

if sy-batch is initial.
      break-point.
endif.

if g_filename is initial.
      Select Single filename from RSFILENAMEDONE
      where request = @request into @g_filename.
endif.

A comparison of SAP BW, HANA Native and SAP DW-Cloud -  Download the Whitepaper here! 

Dynamic fileupload

If the filenames have been stored in the aDSO, we can use them to check during DTP execution whether a file has already been processed. To do this we compare the filenames from the directory of the application server with the filenames from the aDSO and only load files that are not yet listed in the aDSO.

In the second step we check if the found file is still uploaded to the application server by comparing the file size every five seconds. If the file size is identical it means that the file has been completely stored on the application server and we can load it.

Do 2 times.
      IF sy-index = 1.
        LOOP AT it_files ASSIGNING FIELD-SYMBOL(<ls_files>).
          p_filename = |{ lv_dir_name }{ <ls_files>-name }|.
          SELECT SINGLE filename
            FROM /bic/afilnatest2
            WHERE filename EQ @p_filename
            INTO @DATA(l_filename).
          IF sy-subrc NE 0. " File has not been transferred yet
            ls_files = <ls_files>.
            EXIT. " consider first filename, that is not found in target yet.

          ENDIF.
        ENDLOOP.

        WAIT UP TO 5 SECONDS.
      ELSE. " Second part of logic
        LOOP AT it_files ASSIGNING <ls_files>
          WHERE name = ls_files-name.
          IF ls_files-size = <ls_files>-size. " file size has not changed in last 5 seconds, therefore it is considered not being transferred anymore
            
            p_filename = |{ lv_dir_name }{ ls_files-name }|.
            p_subrc = 0.

          ELSE.
            CLEAR p_filename.
          ENDIF.

        ENDLOOP.
      ENDIF.
    ENDIF.
enddo.

Do you have questions about how to get the best use out of your BW? Are you trying to build up the necessary know-how in your department or do you need support with a specific question? We are happy to help you. NextLytics is always at your side as an experienced project partner.