SAP Data Services & SAP Replication Server

SAP Data Services had the ability in previous versions to use SAP Replication Server for Change Data Capture capabilities but this involved the use of PowerDesigner and a staging area using Sybase ASE. As of Data Services 4.2 SP3 you no longer need PowerDesigner or the staging area as the integration is now much simpler and SAP Data Services can now connect directly to SAP Replication Server.Architecture.png

SAP Data Services connects to the source systems to collect metadata and then to SAP Replication Server / Agent for configuration and change data retrieval.

You create 2 datastores, one with No CDC that will be used for the initial load

No CDC.png

and one using the CDC option which will be used for the delta loads.

CDC datastore.png

Under the advanced section you now add the Replication Server and Replication Agent details.

(This example is using Sybase ASE as the source system, as of Data Services 4.2 SP3 Sybase ASE is not supported as a CDC source. Please refer to the Product Availability Matrix for supported systems)

Import the same tables from both datastores.

/wp-content/uploads/2014/12/datastores_608320.pngYou can now start creating your job. You may want to start with a conditional workflow to check if the job is needs to do an initial or delta load. The initial load is just a standard dataflow using the table from the Initial datastore.

The dataflow for the delta (Rep Server CDC) part of the job has to be inside a Continuous Workflow.

cont_WF.png

Once the job has been started it will continue to run and fetch changed data from the Replication Server. The Continuous Workflow has options (e.g. number of runs or custom function) to control how long it should run for.

The dataflow inside the continuous workflow now reads the table from the Delta datastore.

/wp-content/uploads/2014/12/dataflow_608322.pngWhen viewing the data from the Delta datastore you will notice 2 additional columns, DI_SEQUENCE_NUMBER and DI_OPERATION_TYPE. Operation type is used to determine if the record is an insert, update or delete record.

/wp-content/uploads/2014/12/data_608323.png

In the example dataflow above the Operation type is then used in a Map_CDC_Operation transform to tell SAP Data Services to generate update, insert or delete statements. If you don’t want to delete data from the target then a Map_Operation transform can be used to turn a delete into an update for example.

With SAP Data Services 4.2 SP3 this new simplified architecture makes it even easier to perform real time transformations as part of a data integration process.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Alexander Chepanov

    David, thank you for useful article.

    Do I understand correctly, SAP Replication Server is required for CDC only in previous versions SAP DS? In SAP DS 4.2 Replication Server no more need for CDC because of there are a new simplified architecture in DS 4.2?

    (0) 
    1. David Pugh Post author

      Alexander,

      Previous versions didn’t support SAP Replication Server. Support was added in DS 4.2 and has been simplified in 4.2 SP3 as it no longer required a staging table or the use of PowerDesigner.

      SAP Replication Server is one option for CDC there are other alternatives which are documented in the DS Designer guide.

      David

      (0) 
      1. Alexander Chepanov

        David,

        thank you for answer.

        I’d like to summarize a requirements for use CDC option in SAP DS depending on Source.

        There are following requirements for CDC documented in DS Designer Guide

        1. Oracle’s CDC packages is required for Oracle;
        2. Attunity Streams is required for Mainframe;
        3. Microsoft SQL Replication Server is required for SQL DB.

        And what about SAP Replication Server and SAP LT Replication Server?

        In which cases I need to use them?

        Is it possible to use source-based CDC for HANA as a source? And what are the requirements for this case?

        Best regards,

        Alexander

        (0) 
        1. Mugur Onita

          Alexander,

          That’s an interesting question and I’m sure many customers we’ll need an answer for the same.

          1. Oracle CDC – SAP DS Designer Guide is mentioning you can use SAP DS only for Oracle CDC, provided Oracle vers. is 9i or higher, however what the book doesn’t mention is the fact Oracle has deprecated such functionality starting with Oracle 12c. They want everyone in need of CDC to use only their own tool – Oracle GoldenGate via Oracle Data Integrator (Oracle’s ETL tool).

          See link for details: https://docs.oracle.com/database/121/UPGRD/deprecated.htm#UPGRD60084

          That’s why, if your Oracle DB is vers. 12c or higher, for CDC you need to use a combination of SAP Sybase Replication Server and SAP Data Service. The good news is as David has pointed out, starting with SAP DS 4.2 SP3 the integration has been simplified.

          2. SAP LT Replication Server is meant for Real Time data replication from SAP core systems (transactional) such as SAP CRM, ECC, etc. into SAP HANA. In this scenario you don’t need SAP Data Services (ETL).

          3. HANA as a source CDC – I don’t think that exists.

          Hope this helps.

          (0) 
  2. Alexander Chepanov

    David and Mugur,

    thank you for answers.

    Thus there are the following requirements for using CDC with SAP DODS:

    Data-Source:

    1. For Oracle:

         a. Oracle vers. is 9i to 12c: SAP DS only;

         b. Oracle vers. 12c or higher: SAP DS + SAP RS;

    2. For Sybase: SAP DS + SAP RS;

    3. For MS SQL: SAP DS + MS SQL RS;

    4. For Mainframe: SAP DS + Attunity Streams;

    5. For HANA: SAP LT RS only (CDC trigger-based);

    6. For SAP Business Suite Apps.: SAP LT RS only (CDC trigger-based).

    Please, add something else or correct me if I had a mistake.

    Best regards,

    Alexander.

    (0) 
    1. Jose Torres

      Hi Alexander,

      We have a MS SQL Server as source that we would like replicate to SAP data services.

      Currently, as of September 2106, do you know if:

      -Microsoft SQL is supported as a source Database for replicating to SAP Data Services using SAP Replications server and which would be the  MS SQL version supported?

      thank you

      regards

      Jose

      (0) 
  3. Rakhi Sankar

    Hi,

    I have created the delta data store but i am not able to view any data in the delta table.

    My database is oracle 12C. How to trouble shoot further. I have added and updated records in the DB.Please help

    (0) 
  4. Ian Osborne

    I have followed this blueprint and have successfully implemented RepServer CDC over one source table with Data Services.  I then replicated the data flow, deleted and replaced the source/target tables to create anothert data flow for replicating a second table.  This data flow was then placed in the same workflow as the original replication workflow.  Now on execution, the original dataflow is no longer replicating data.  If this architecture does not work for replicating multiple tables, then how can this be achieved?  Should we have a different continuous workflow for each source table. or otherwise?

    (0) 

Leave a Reply