SAP Data Services and SAP Replication Server
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.
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
and one using the CDC option which will be used for the delta loads.
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.
You 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.
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.
When 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.
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.
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?
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
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
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
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.
Mugur,
thank you for answer.
3. HANA as a source CDC - it is possible with SAP LT Replication Server (trigger-based).
See link for details: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70dd6e62-0113-3110-f58e-92aa5a784377?QuickLink=index&overridelayout=true&59511066929077
Best regards,
Alexander.
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.
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
Hello Jose / Alexander,
I want to replicate real time data from MS SQL 2019 to Sybase ASE 16 SP03.
As of today, I am using Sybase replication agent (Heterogeneous replication option) for data replication but unfortunately that is out of support and SAP does not have any alternative product which can provide similar services.
Can you share some insights if SAP Data services can be used in such a scenario to replace Sybase replication agent ?
Any insights might help.
Regards,
Dhaval.
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
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?
Is it possible to replicate pool/cluster tables using data services CDC with SAP Replication Server?