Skip to main content

Set Up Data Services Objects

This chapter provides information about setting up SAP Data Services objects for Transaction, AVL, Drilldown, SyncBack, and Reconciliation.

Prerequisite Tasks

On the computer on which SAP Data Services is installed, make sure that the following tasks are complete:

  • In the DSConfig.txt file at \\ProgramData\SAP BusinessObjects\Data Services\conf, update the value of ANSI_Varchar_Behavior as False.
  • Ensure that the data store configurations point to the staging database.
  • Copy the SourceConnect folder at <SetupFolder>\Sample_folder_structure into the computer on which SAP Data Services is installed.
  • Ensure that the setup files are available at the following locations:
    • The .atl files for your source system are available at <SetupFolder>\<SourceSystem>\<SourceDatabase>\Mappings.
    • The common .atl files are available at <SetupFolder>\Common_Mappings.

      • Note: While importing multiple .atl files or reimporting .atl files, right-click the data store and select Exclude Tree to avoid overwriting the data store connection details.

  • For 1C:Enterprise, ensure that the folder containing the .csv files is accessible to the SAP Data Services server.

Set Up SAP Data Services Objects for Transaction and AVL

You can import the .atl file pertaining to your source system into your SAP Data Services repository through SAP Data Services Designer. For each source system, the project, data stores, and jobs are created in the repository. Then, you can update the connection details in the data stores to point to your source system objects.

Note: For information about the password that you must use for importing the .atl file, contact insightsoftware Support.

To set up SAP Data Services Objects for Transaction and AVL

  1. Open SAP Data Services Designer as an administrator, and connect to your repository.
  2. For the transaction data, perform the following:
    1. Import the <SourceSystem>_Journals%.atl file into your SAP Data Services repository. The <SourceSystem>_Journals project and the corresponding data stores and jobs are created.
    2. Additionally, for NetSuite, import the NetSuite_DataLoad.atl file. The NetSuite_DataLoad project and the corresponding data stores and jobs are created.

    Note: If the following message appears while importing the .atl file, select Yes.
  3. For the balances data, perform the following:
    1. Create balance sheet accounts in SAP S/4HANA.
    2. Create the Accounts Receivable, Accounts Payable, and General Ledger clearing accounts that you need to specify in the $%Clearing_account variable while running the <SourceSystem>%_Balances job. For information about the $%Clearing_account variable for your source system, see Global Variables in Appendix B.
    3. Optionally, clear the Accounts Receivable and Accounts Payable clearing accounts manually. The General Ledger clearing account will be cleared automatically.
    4. Import the <SourceSystem>_Balances.atl file into the SAP Data Services repository. The <SourceSystem>_Balances project and the corresponding data stores and jobs are created.

      5. For JD Edwards EnterpriseOne, the out-of-the-box view for balances extraction must be updated according to the JD Edwards EnterpriseOne account setup. The account ranges should be modified in the view definition to indicate the account types. For example, if the account type is Assets, modify the value for the GBOBJ field to be between 100000 and 199999.

    4. Note: For information about the list of projects and the corresponding jobs pertaining to your source system, see SAP Data Services Jobs in Appendix B.

  4. For Reconciliation data, perform the following:
    1. Import the <SourceSystem>_RECON_L1_L5.atl file into your SAP Data Services repository. The <SourceSystem>_RECON_L1_L5 project and the corresponding data stores and jobs are created.
    2. Import the RECON_STG.atl file into your SAP Data Services repository. The RECON_STG project and the corresponding data stores and jobs are created.
  5. For AVL data, import the AVL_<SourceSystem>.atl file into your SAP Data Services repository. The AVL_<SourceSystem> project and the corresponding data stores and jobs are created.
  6. Update the connection details in the following data stores to point to your source system objects:
    • Oracle E-Business Suite, IFS Applications, Oracle PeopleSoft, and Infor Baan
      Data store name Description

      DS_<SourceSystem>_CDC

      The CDC schema that you created during the installation of Transaction.

      DS_<SourceSystem>_MASTER

      The schema that contains the extraction views.

      DS_<SourceSystem>_STG

      The staging schema or database that contains the staging tables.

      DS_<SourceSystem>_SLT

      The SLT Replication Server database in which you created the /1LT/CF_E% tables.
    • JD Edwards EnterpriseOne
      • Data store name Description

      RTD_DS_JDE_APPL

      The JD Edwards EnterpriseOne database in which the JD Edwards EnterpriseOne tables exist.

      RTD_DS_JDE_BAL_APPL

      The JD Edwards EnterpriseOne database in which the JD Edwards EnterpriseOne tables exist.

      DS_RD_JDE_CDC

      The CDC schema.

      RTD_DS_JDE_MASTER

      The schema that contains the extraction views.

      RTD_DS_JDE_BAL_MASTER

      The schema that contains the extraction views.

      RTD_DS_JDE_STG

      The schema that contains the staging tables.

      HANA_RECON_REP

      The staging schema in the staging database.

      RTD_DS_JDE_SLT

      Specify the details of the SLT Replication server database in which the /1LT/CF_E% tables are present.
    • Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics SL, Microsoft Dynamics GP, Dynamics 365 Finance and Operations, Dynamics 365 Business Central (on-premises and cloud), Epicor iScala, Infor CloudSuite Industrial (SyteLine), Deltek Costpoint, and NetSuite
      • Data store name Description

      DS_<SourceSystem>_STG

      The staging schema or database that contains the staging tables.

      DS_<SourceSystem>_SLT

      The SLT Replication Server database in which the /1LT/CF_E% tables are present.

      DS_<SourceSystem>_VIEWS

      The database that contains the extraction views.

      DS_MSFTAX_CDC (For Microsoft Dynamics AX only)

      The database in which the SQL Server CDC table for the GENERALJOURNALENTRY table is created.

      DS_NetSuite_NS_System (For NetSuite only)

      The system that contains NetSuite application table.
    • Workday, QAD, 1C:Enterprise
      • Data store name Description

      DS_<SourceSystem>_MASTER

      The staging database that contains the staging tables.

      DS_<SourceSystem>_SLT

      The SLT Replication Server database in which the /1LT/CF_E% tables are present.
  7. For the RECON_SOURCE data store, update the connection details to point to the schema containing the extraction views.
  8. For the DS_S4_APPL data store, update the connection details to point to SAP S/4HANA.
  9. For the %DS_<SourceSystem>_PSCACHE data store, update the Cache directory field to point to the Cache folder at \\SourceConnect\MasterMapping in which the cache file needs to be created.
  10. Additionally, update the connection details in the following data stores to point to your staging objects:
    Data store name Description

    HANA_RECON_REP

    The staging schema in the staging database.

    RTD_DS_<SourceSystem>_STG

    The schema that contains the staging tables.

    RTD_DS_<SourceSystem>_SLT

    The SLT Replication Server database in which the /1LT/CF_E% tables are present.
  11. For all the jobs in the staging database-related data stores, make the following changes:
    1. Depending on your source system, in the jobs pertaining to one or more of the following data stores, remove the target table in each data flow:
      • DS_<SourceSystem>_STG
      • RTD_DS_JDE_STG
      • DS_<SourceSystem>_MASTER
      • HANA_RECON_REP
      • RECON_SOURCE
    2. Add the target table in the data store to the data flow.
    3. Save the changes.
  12. For the <SourceSystem>_ERROR_LOG and <SourceSystem>_REPROCESS_FILE flat files in SAP Data Services Designer, edit the Root directory field to point to the Log and Reprocess folders respectively in the SourceConnect folder.
  13. For the MSC_LOOKUP_DATA Excel workbook in SAP Data Services Designer, edit the Directory field to point to the MasterMapping folder in the SourceConnect folder.

After you set up the objects, you can load the transaction and balances data from your source systems into SLT Replication Server. For information about loading the data, see Load Data into SLT Replication Server in Loading Data, Reprocessing Documents.

Before loading the data from NetSuite, you must perform some prerequisite tasks. For information, see Prerequisite Tasks for Loading NetSuite Data into SLT Replication Server in Loading Data, Reprocessing Documents.

Set Up SAP Data Services Objects for Flat File Data Load

You can import the .atl file pertaining to your source system into your SAP Data Services repository through SAP Data Services Designer. The project, data stores, and jobs are created in the repository. Then, you can update the connection details in the data stores to point to your source system objects.

To set up SAP Data Services objects for flat file data load

  1. Open SAP Data Services Designer as an administrator, and connect to your repository.
  2. Import the UFF_Journals.atl file located at <SetupFolder>\UFF\Mappings into your SAP Data Services repository. The following projects and jobs are created:
    • UFF_Journals project:UFF_Journals and UFF_Reprocess_Journals
    • UFF_MasterData_Mapping project:Master_Lookup_Load

    Note: The Reconciliation-related objects for Transaction are applicable to flat file data as well. For information about these objects, see Set Up SAP Data Services Objects for Transaction and AVL.

  3. 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 will be sent to the specified email address.
  4. Import the UFF_AVL.atl file located at <SetupFolder>\UFF\Mappings into your SAP Data Services repository. The UFF_AVL project, and the following jobs are created.
    • UFF_AVL_CI: Loads the Customer Invoice data from Flat files to SLT tables.
    • UFF_AVL_CO: Loads the Sales Order data from Flat files to SLT tables.
    • UFF_AVL_PO: Loads the Purchase Order and Goods Receipt Note data from Flat files to SLT tables.
    • UFF_AVL_SI: Loads the Supplier Invoice data from Flat files to SLT tables.
    • Master_Lookup_Load: Loads the master data from MSC_Lookup_Data.xlsx to the MSC_MASTER_LOOKUP table and creates a persistent cache on the MSC_ MASTER_LOOKUP table for lookup data.
  5. Update the $FILE_PATH variable with the folder location where you have the UFF files available.
  6. Update the $SOURCE_SYSTEM variable with the source system name.
  7. Set up the $G_MATERIAL variable with DUMMY as the value in places where the source transaction is not defined with a material value. It will then be mapped to a material value in SAP S/4HANA.
  8. For the DS_UFF_MASTER data store, provide the connection details of the staging database that contains the configuration tables.
  9. For the DS_UFF_AVL_STG data store, provide the connection details of the staging database that contains the configuration tables.
  10. 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.
  11. For the DS_UFF_AVL_SLT data store, specify the details of the SLT Replication Server database in which the /1LT/CF_E% tables are present.
  12. For the DS_UFF_PSCACHE data store, update the Cache directory field to point to the folder where you want to create the cache file.

Set Up SAP Data Services Objects for Drilldown

You can import the .atl file pertaining to your source system into your SAP Data Services repository through SAP Data Services Designer. The project, data stores, and jobs are created in the repository. Then, you can update the connection details in the data stores to point to your source system objects.


Important: On the computer on which SAP Data Services is installed, make sure that the following tasks are complete:
  • In the DSConfig.txt file at \\ProgramData\SAP BusinessObjects\Data Services\conf, update the value of ANSI_Varchar_Behavior as False.
  • The data store configurations point to the staging database.

The setup files are available at the following locations:

  • <SetupFolder>\<SourceSystem>\<SourceDatabase>\Mappings
  • <SetupFolder>\<SourceSystem>\<SourceDatabase>\Scripts
  • <SetupFolder>\Stage

To set up SAP Data Services Objects for Drilldown

  1. Open the SAP Data Services Designer as an administrator, and connect to your repository.
  2. For transaction data, import the .atl file for your source system into your SAP Data Services repository.

    Note: For information about the password that you must use for importing the .atl file, contact insightsoftware Support.

    3. The <SourceSystem>_SCD project, the following jobs, and the corresponding data stores are created:

    • <SourceSystem>_CDC_TRX: Performs the incremental data load for transaction data.
    • <SourceSystem>CDC_Set_Status_On: Enables the incremental data load for the <SourceSystem>_CDC_TRX job.
    • <SourceSystem>CDC_Set_Status_Off: Stops the incremental data load for the <SourceSystem>_CDC_TRX job.
    • <SourceSystem>_OT_<ModuleName>_TRX: Performs the initial data load for transaction data. The <ModuleName> refers to AP, AR, GL, PO or SO.
    • <SourceSystem>_OT_MASTER_DATA: Performs the initial data load for master data.
  3. Edit the settings of the data stores to update the connection details to point to your source system objects:

    • DS_<SourceSystem>_APPL: Update the connection details to point to your source database that contains the source tables.

    • DS_HANA_SCD: Update the connection details to point to the Drilldown user that you created in the SAP HANA database during the Drilldown installation.

    • DS_<SourceSystem>_CDC: Update the connection details to point to the CDC schema that you created during the Transaction installation.

  4. For all the jobs in the DS_HANA_SCD data store, make the following changes:
    1. Remove the target table in each data flow.
    2. Add the target table in the data store to the data flow.
    3. Save the changes.

After you set up the objects, you can load the transaction data into SAP S/4HANA for Drilldown reporting. For information, see "Loading Data, Reprocessing Documents" in SAP Central Finance Transaction Replication by insightsoftware Administrator Guide.

Set Up SAP Data Services Objects for SyncBack

You can import the .atl file pertaining to your source system into your SAP Data Services repository through SAP Data Services Designer. The project, data stores, and jobs are created in the repository. Then, you can update the connection details in the data stores to point to your source system objects.


Important: On the computer on which SAP Data Services is installed, make sure that the following tasks are complete:
  • In the DSConfig.txt file at \\ProgramData\SAP BusinessObjects\Data Services\conf, update the value of ANSI_Varchar_Behavior as False.
  • The data store configurations point to the staging database.

To set up SAP Data Services Objects for SyncBack

  1. Open the SAP Data Services Designer as an administrator, and connect to your repository.
  2. Import the <SourceSystem>_Syncback.atl file located at <SetupFolder>\<SourceSystem>\Mappings into your SAP Data Services repository. The <SourceSystem>_Syncback project, the corresponding data stores, and jobs are created:
    Job name Description

    S4_<SourceSystem>_AP_Payment_OT

    Performs an initial load of payments data from SAP S/4HANA into a vendor payment-specific table in the source database or a file.

    S4_<SourceSystem>_AR_Payment_OT

    Performs an initial load of receipts data from SAP S/4HANA into a customer receipt-specific table in the source database or a file.

    S4_<SourceSystem>_AP_Payment_CDC

    Performs an incremental load of payments data from SAP S/4HANA into a vendor payment-specific table in the source database or a file.

    S4_<SourceSystem>_AR_Payment_CDC

    Performs an incremental load of receipts data from SAP S/4HANA into a customer receipt-specific table in the source database or a file.

    S4_<SourceSystem>CDC_Set_Status_On

    Enables the incremental data load for the S4_<SourceSystem>_AP|AR_Payment_CDC job.

    S4_<SourceSystem>CDC_Set_Status_Off

    Stops the incremental data load for the S4_<SourceSystem>_AP|AR_Payment_CDC job.
  3. Additionally, for all staging databases except SAP HANA, the following jobs are created:
    Job name Description

    S4_<SourceSystem>_AP_Payment_Trx

    Performs an initial load of the payments data from SAP S/4HANA into the AP_FIN_PYMT_TRX table in the staging database.

    S4_<SourceSystem>_AR_Payment_Trx

    Performs an initial load of the receipts data from SAP S/4HANA into the
    AR_FIN_PYMT_TRX table in the staging database.

    S4_<SourceSystem>_AP_Payment_Trx_CDC

    Performs an incremental load of payments data from SAP S/4HANA into the AP_FIN_PYMT_TRX table in the staging database.

    S4_<SourceSystem>_AR_Payment_Trx_CDC

    Performs an incremental load of receipts data from SAP S/4HANA into the AR_FIN_PYMT_TRX table in the staging database.

    S4_<SourceSystem>STG_Set_Status_On

    Enables the incremental data load for the S4_<SourceSystem>_AP|AR_Payment_Trx_CDC job.

    S4_<SourceSystem>STG_Set_Status_Off

    Stops the incremental data load for the S4_<SourceSystem>_AP|AR_Payment_Trx_CDC job.
  4. Edit the settings of the data stores to update the connection details to point to your source system objects:
    • DS_S4_DB: Update the connection details to point to the database in SAP S/4HANA in which the extraction views were created during the installation of SyncBack.
    • DS_<SourceSystem>_MASTER: Update the connection details to point to the schema in which the synonyms were created during the installation of SyncBack.
    • RTD_DS_<SourceSystem>_STG/DS_MAG_STG: Update the connection details to point to the schema that contains the staging tables in your source instance.
  5. For all the jobs in the RTD_DS_<SourceSystem>_STG/DS_MAG_STG data store, make the following changes:
    1. Remove the target table in each data flow.
    2. Add the target table in the data store to the data flow.
    3. Save the changes.

After you set up the objects, you can load the data from SAP S/4HANA into your source system using SyncBack. For information, see " Loading Data, Reprocessing Documents" in SAP Central Finance Transaction Replication by insightsoftware Administrator Guide.

Was this article helpful?

We're sorry to hear that.