Skip to main content

Parallel Loading and Reprocessing of Flat File Data

This chapter contains information about the parallel loading of transaction data in flat file format from multiple source systems into SLT Replication Server.

Parallel Loading of Data from Multiple Source Systems

For better performance, Transaction supports parallel loading of transaction data from multiple source systems into SLT Replication Server. By default, you can load the flat file data from two of your source systems in parallel into SLT Replication Server. For information about adding more source systems, see Add Source Systems for Parallel Loading.

Prerequisite Tasks

Before loading the data, perform the following:

  • Copy the setup files available in the following locations to your computer:
    • <SetupFolder>\<StagingDatabase>\Mappings
    • <SetupFolder>\<StagingDatabase>\Sample_folder_structure
    • <SetupFolder>\<StagingDatabase>\Scripts

      The supported staging databases are Microsoft SQL Server and SAP HANA.

  • Create a folder named SOURCE_FILES with the following subfolders in your computer:
    • Master Mapping
    • SRC1
      • INPUT
      • ARCHIVE
      • EXCEPTION
    • SRC2
      • INPUT
      • ARCHIVE
      • EXCEPTION
    • REPROCESS
      • INPUT
      • ARCHIVE
      • EXCEPTION

    Note: During the setup, you must specify the path of these folders in the global variables of the job for parallel loading of data.

  • Perform the following steps to check the documents posted in SAP S/4HANA by using document change functionality for Flat file data:
    1. Start transaction /AIF/CONTENT_EXTRACT in the customizing client of your SAP S/4HANA system.
    2. Enter the Deployment Scenario ID SAP_AIF_0026 and run the transaction by pressing F8.

Set Up Objects for Parallel Loading, Reprocessing of Data

You need to create staging tables and related objects in the staging database for parallel loading and reprocessing of data. Then, you can import the .atl file available in the setup files into your SAP Data Services repository to create data load and reprocessing-related objects in SAP Data Services Designer.

To set up objects for parallel data loading

  1. Connect to the staging database, and run the scripts located at <SetupFolder>\<StagingDatabase>\Scripts\Parallel_scripts in the following order:
    1. Tables_1.sql
    2. Decimal_Proc_2.sql
    3. Doc_Split_Proc_Src<Source_Number>_3.sql
    4. Fin_Validation_Chk_5.sql
    5. Summarize_Wht_6.sql
    6. UFF_Stg_Validation_Chk_src1_7.sql
    7. Update_Err_9.sql
    8. Validation_Chk_10.sql

    The staging tables, procedures, and other database objects related to the data load are created.

  2. In the staging database, run the scripts located at <SetupFolder>\<StagingDatabase>\Scripts\Reprocess_scripts in the following order:
    1. Tables_1.sql
    2. Decimal_Proc_Rep_2.sql
    3. Doc_Split_Proc_Rep_3.sql
    4. Fin_Validation_Chk_Rep_4.sql
    5. Summarize_Wht_Rep_5.sql
    6. UFF_Stg_Validation_Chk_Rep_6.sql
    7. Validation_Chk_Rep_7.sql
    8. Large_Amt_Split_Proc_Rep_8.sql

    3. The staging tables, procedures, and other database objects related to data reprocessing are created.

  3. In SAP Data Services Designer, import the UFF_Journals.atl file located at <SetupFolder>\<StagingDatabase>\Mappings into your SAP Data Services repository. The following projects and jobs are created:
    1. UFF_Parallel project:UFF_Journals and UFF_Reprocess_Journals jobs
    2. UFF_MasterData_Mapping project:Master_Lookup_Load job
    3. UFF_DOCCHG job
  4. Update the $v_uff_email variable of the UFF_Journals and UFF_Reprocess_Journals jobs with the email address of the point of contact. The error messages are sent to the specified email address.
  5. For the UFF_Journals job, update the following global variables with the path in which you created the source-specific folders. For information about the path, see Prerequisite Tasks.
    • $UFF_FILEPATH_SRC1: Indicates the path of the INPUT folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC1\INPUT that you created.
    • $UFF_FILEPATH_SRC2: Indicates the path of the INPUT folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC2\INPUT that you created.
    • $UFF_ARCHIVE_SRC1: Indicates the path of the ARCHIVE folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC1\ARCHIVE that you created.
    • $UFF_ARCHIVE_SRC2: Indicates the path of the ARCHIVE folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC2\ARCHIVE that you created.
    • $UFF_SRC1_EXCEPTION: Indicates the path of the EXCEPTION folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC1\EXCEPTION that you created.
    • $UFF_SRC2_EXCEPTION: Indicates the path of the EXCEPTION folder specific to the source system, such as C:\UFF\SOURCE_FILES\SRC2\EXCEPTION that you created.
  6. For the DS_UFF_MASTER data store, provide the connection details of the staging database that contains the configuration tables.
  7. For the DS_UFF_SLT data store, specify the details of the SLT Replication Server database in which the /1LT/CF_E% tables are present.
  8. For the DS_PSCACHE data store, update the Cache directory field to point to the folder where you want to create the cache file.

Load Data in Parallel, Reprocess Documents

Before loading the data, make sure that the previous data load is complete with no errors.

To load flat file data from two source systems in parallel, reprocess failed documents

  1. Place the MSC_Lookup_Data.xlsx file that you created during the configuration of SAP S/4HANA at the following location in your computer \\SOURCE_FILES\MasterMapping.

    Note: For information about the configuration, see Configuring SAP S/4HANA for Data Load in Loading Data, Reprocessing Documents.

  2. Place the .csv file containing your transaction data in the INPUT folder specific to your source system, such as \SRC1\INPUT.
  3. Run the UFF_Journals job. The transaction data is loaded into SLT Replication Server, and then the .csv file is moved to the ARCHIVE folder pertaining to the source system, such as \SRC1\ARCHIVE.
  4. Run the UFF_DOCCHG job if you want to make document changes in the posted transactions.The source data must contain transaction type as 'C' and match the data which is already present in FIN% or SLT tables except the data which must be changed.
  5. If any errors occur during the data load, a <SourceSystem>_EXCEPTION_<Date>.csv file containing the list of failed transactions is created and placed in the EXCEPTION folder, such as \SRC1\EXCEPTION\SRC1_EXCEPTION_18092019.csv.
  6. Review the errors, such as EFFECTIVE_DATE Cant Be Blank specified in the ERROR_DESC column of the .csv log file, fix the errors, and then place the corrected file in the \REPROCESS\INPUT folder.
  7. Run the UFF_Reprocess_Journals job. The updated transaction data is loaded into SLT Replication Server, and then the .csv file is moved to the ARCHIVE folder, such as \REPROCESS\ARCHIVE.
  8. If any of the documents still contain errors, a .csv file with the failed documents are created and placed in the \REPROCESS\EXCEPTION folder.
  9. Review the errors using the ERROR_DESC column in the log file, fix the errors, and then place the corrected file in the \REPROCESS\INPUT folder. For information on the validation of transaction data in FIN% tables, see ''Validation of Transactions'' in Appendix D: Troubleshooting.

    Note: We recommend that you periodically remove the files that are no longer required from the Exception folder.

Add Source Systems for Parallel Loading

By default, you can load flat file data from two source systems in parallel into SAP S/4HANA. For the parallel loading of data from more than two source systems, Transaction enables you to add source systems by updating the objects in setup files and SAP Data Services Designer.

To add source systems

  1. In SAP Data Services Designer, open the UFF_Parallel project > UFF_Journals job. In the UFF_INIT script, perform the following:
    1. Copy the lines that contain a reference to SRC1 or SRC2, and paste them in the script.
    2. In the copied lines, replace SRC1 or SRC2 with your source name.
  2. In the UFF_Journals job, create the following global variables for the new source, and then update them with the path in which you created the source-specific folders. For information about the path, see Prerequisite Tasks:
    • $UFF_FILEPATH_<NewSource>
    • $UFF_ARCHIVE_<NewSource>
    • $UFF_<NewSource>_EXCEPTION
  3. In the UFF_Journals job, add a new workflow, named WF_SCT_<NewSource>_STG, to the WF_UFF_CONTAINER workflow.
  4. In the <SetupFolder>\<StagingDatabase>\Scripts\Parallel_scripts folder, update Tables_1.sql with the code for creating tables, namely UFF_STG_<NewSource> and UFF_STG_TEMP<NewSourceNumber> by using the code for the UFF_STG_SRC1/UFF_STG_SRC2 and UFF_STG_TEMP1/UFF_STG_TEMP2 tables as a reference.
  5. In the Parallel_scripts folder, perform the following:
    1. Save a copy of the Doc_Split_Proc_Src1_3.sql or Doc_Split_Proc_Src2_4.sql script as “Doc_Split_Proc_<NewSource>_<SequenceNumber>.sql”.
    2. Save a copy of the Large_Amt_Split_Proc_Src1_11.sql or Large_Amt_Split_Proc_Src2_12.sql script as “Large_Amt_Split_Proc_<NewSource>_<SequenceNumber>.sql”.
    3. Save a copy of the UFF_Stg_Validation_Chk_src1_7.sql or UFF_Stg_Validation_Chk_src2_8.sql script as “UFF_Stg_Validation_Chk_<NewSource>_<SequenceNumber>.sql”.

      Where <SequenceNumber> indicates the order in which the script should be run.

    4. In the copied files, replace UFF_STG_SRC1/UFF_STG_SRC2 and UFF_STG_TEMP1/ UFF_STG_TEMP2 with UFF_STG_<NewSource> and UFF_STG_TEMP<NewSourceNumber>.
  6. In the Parallel_scripts folder, in the Fin_Validation_Chk_5.sql script, before the OPEN FIN_ERROR_DOCS line, add the following code snippet:

    Else if(@P_SOURCE_COLUMN='{new_source}')

    BEGIN

    DECLARE FIN_ERROR_DOCS CURSOR LOCAL FOR

    SELECT UFF_STG_{new_source}.*, FH.ERROR_DESC

    FROM UFF_STG_{new_source},FIN_HEADER FH

    WHERE FH.UNIQUE_ID in (select UNIQUE_ID from FIN_HEADER where OBJECT_LOGSYS=@P_SOURCE_COLUMN)

    AND 1=1 AND

    SUBSTRING(UFF_STG_{new_source}.HEADER_ID+''+RIGHT(UFF_STG_{new_source}.COM PANY_CODE,4),1,20) =FH.OBJECT_KEY

    AND UFF_STG_{new_source}.REFERENCE_DOCUMENT_NO=FH.REFERENCE_DOCUMENT_NO

    AND COALESCE(EFFECTIVE_DATE,0) =COALESCE(DOCUMENT_DATE,0)

    AND

    COALESCE(UFF_STG_{new_source}.POSTED_DATE,0)=COALESCE(FH.POSTING_DATE,0)

    AND FH.STATUS='E';

    END

  7. Finally, add the new source folder, such as SRC3, with the subfolders, INPUT, ARCHIVE, and EXCEPTION under the \SOURCE_FILES folder in your computer. For information about the path in which you created other source-specific folders, see Prerequisite Tasks.

Control Tables

You can use the tables, control_table and control_table_rep in the staging database to track the status of parallel loading and reprocessing of transaction data. The tables also contain the statistics of previous job runs.

control_table

After you run the job for loading of transaction data in the flat file format, the control_table table in the staging database is populated with the following information about the job run for each source:

  • REC_ID: Indicates the unique identifier of each record.
  • RUN_ID: Indicates the unique job run identifier used by different source systems for the same job.
  • SOURCE: Displays the name of the source system, such as SOURCE1 or SOURCE2.
  • STATUS: Displays the value Y to indicate that the job is successful and E to indicate that the job failed with errors.
  • PROC_RECORD_CNT: Shows the number of documents that are processed and moved from the staging table, UFF_STG_<SourceSystem> to the FIN% tables.
  • HEADER_RECORD_CNT: Shows the number of documents in the FIN_HEADER table that are ready to be moved to SLT Replication Server.
  • ITEM_RECORD_CNT: Shows the number of documents in the FIN_ACCT, FIN_CREDIT, FIN_DEBIT, FIN_PRDTAX, and FIN_WHTAX tables that correspond to the documents in HEADER_RECORD_CNT.
  • TRANS_CREDIT_STG: Displays the sum of credit amounts in the document currency in the UFF_STG_<SourceSystem> table.
  • TRANS_DEBIT_STG: Displays the sum of debit amounts in the document currency in the UFF_STG_<SourceSystem> table.
  • LOC_CREDIT_STG: Displays the sum of credit amounts in the company currency in the UFF_STG_<SourceSystem> table.
  • LOC_ DEBIT_STG: Displays the sum of debit amounts in the company currency in the UFF_STG_<SourceSystem> table.
  • TRANS_CREDIT_FIN: Displays the sum of credit amounts in the document currency in the FIN tables.
  • TRANS_DEBIT_FIN: Displays the sum of debit amounts in the document currency in the FIN tables.
  • LOC_CREDIT_FIN: Displays the sum of credit amounts in the company currency in the FIN tables.
  • LOC_DEBIT_FIN: Displays the sum of debit amounts in the company currency in the FIN tables.
  • ERR_CNT: Shows the error count in the UFF_STG_ERROR_LOG table.
  • TIME_STAMP: Displays the time when the job run is complete.

control_table_rep

After you run the job for reprocessing of transaction data in the flat file format, the control_table_rep table in the staging database is populated with the following statistics:

  • REC_ID: Indicates the unique identifier of each record.
  • STATUS: Displays the value Y to indicate that the job is successful and E to indicate that the job failed with errors.
  • PROC_RECORD_CNT: Shows the number of documents that are processed and moved from the staging table, UFF_STG_<SourceSystem> to the FIN% tables.
  • HEADER_RECORD_CNT: Shows the number of documents in the FIN_HEADER table that are ready to be moved to SLT Replication Server.
  • ITEM_RECORD_CNT: Shows the number of documents in the FIN_ACCT, FIN_CREDIT, FIN_DEBIT, FIN_PRDTAX, and FIN_WHTAX tables that correspond to the documents in HEADER_RECORD_CNT.
  • TRANS_CREDIT_STG: Displays the sum of credit amounts in the document currency in the UFF_STG_<SourceSystem> table.
  • TRANS_DEBIT_STG: Displays the sum of debit amounts in the document currency in the UFF_STG_<SourceSystem> table.
  • LOC_CREDIT_STG: Displays the sum of credit amounts in the company currency in the UFF_STG_<SourceSystem> table.
  • LOC_ DEBIT_STG: Displays the sum of debit amounts in the company currency in the UFF_STG_<SourceSystem> table.
  • TRANS_CREDIT_FIN: Displays the sum of credit amounts in the document currency in the FIN tables.
  • TRANS_DEBIT_FIN: Displays the sum of debit amounts in the document currency in the FIN tables.
  • LOC_CREDIT_FIN: Displays the sum of credit amounts in the company currency in the FIN tables.
  • LOC_DEBIT_FIN: Displays the sum of debit amounts in the company currency in the FIN tables.
  • ERR_CNT: Shows the error count in the UFF_STG_ERROR_LOG table.
  • TIME_STAMP: Displays the time when the job run is complete.

Was this article helpful?

We're sorry to hear that.