SLT ODP : Real-Time Data Replication from Non-SAP(MSSQL) to BW (ADSO)
In this blog we are going to discuss how to replicate real-time data from Non-SAP Source( MSSQL) –> BW ( ADSO ) via SAP ODP Framework.
Below are my system configuration:
ECC system: ERP 6.0
SLT Replication server: SLT 2.0 (DMIS 2011_1_731 SP13)
BW system : BW 7.5 SP 5
What is ODP Framework?
ODP supports extraction and replication of data to several Target systems and support delta queue mechanism in real-time mode. The Target system receives the data from delta queue and continue the data processing.
In my scenario, I have implemented replication on MSSQLserver table from SAGE DB to BW (ADSO) via SLT-ODP replication server.
Step-by-step guide to replicate real-time data from NonSAP (MSSQL) –> SLT Replication Server –> BW ( ADSO ):
1) In SLT server, goto LTRC tcode and make sure Basis team configure the Non-SAP (MSSQL) connection in SLT server.
2) Also make sure the Source and Target systems are correct in the configuration.
Source: MSSQL DataBase
For non-SAP, the supported databases are, In my case I am using MSSQL:
- Sybase ASE
3) Make Sure that the database user has the respective privileges:
- Establishing a connection (create session) to the database.
- Connecting to a specific schema from the database.
- Within the schema, the DB user will have below privileges like db_owner.
- select from the specific table
- create a table in the given schema (in order to successfully create the logging table)
- select from the logging table
- delete the logging table
- create database triggers for the specific table
- delete the database triggers
- create synonyms and views for the specific table
- delete the synonyms and the views
4. Additionally make sure that the user is able to read the data from table sys.dm_tran_database_transaction which is needed to ensure that the trigger creation works correctly and data replication is consistent.
4) Make sure Basis team created Sourcesystem in BW.
Goto RSA1 -> Sourcesystems-> ODP –SLT Queue.
5) Goto DataSource in RSA1 -> Select the SourceSystem -> Click on “Replicate Metadata”
6) Click on ‘Continue’ button
7) By default it will select all the Tables in MSSQL database.
So Make sure you deselect all and select only the ones you required to create DataSource.
8) In my case I selected Datasource “BGTEXT”.
9) Now you can see Datasource created for Table BGTEXT in BW.
10) Activate the Datasource “BGTEXT”
11) Developing ADSO in Hana Studio.
Goto HanaStudio -> Windows -> Perspective -> other Perspective-> Others -> select BW Modeling
Create a New BW project by giving the BW server details as per SAP GUI.
12) Expand the BW system -> give the userid/password -> expand the BW repository to see all the Info areas.
13) Goto Infoarea ZSLT -> Rightclick on DataStoreObject (Advance) and create New ADSO.
14) Give all the Information to create ADSO and Click on Finish.
15) Activate the ADSO.
16) Goto –> RSA1 -> InfoProviders -> RightClick on ADSO and select to create Transformation.
17) Give all the source/Target information to create a Tranformation
18) Make sure all the fields are mapped correctly.
Click on Activate.
19) Create a DTP.
20) Click on Continue
21) Activate the DTP and make sure the Extraction mode is Delta.
22) Execute the DTP for initial load
23) Now goto BW and check the DTP load is completed sucessfully with “DeltaInt”checked and you can see total no. of initial records loaded.
Note: the next dataloads will load only the delta records from source syustem.
24) Now Activate the Dataload request in ADSO.
25) After you start dataload, you can goto SLT server ( LTRC ) and click on the connection to check if the replication job is running for BGTEXT table.
26) After starting the DTP to load data into ADSO, you can see the replication job just started.
27) After the Dataload completes, you can see the logging table & Triggers will be active to capture the Delta changes on BGTEXT table.
28) Also In Tcode – ODQMON, you can see how many records are replicated into SLT server from SAGE(MSSQL) table BGTEXT
Process chain ( Real-Time Mode )
Create ProcessChain in RSPC.
1) Start Variant
2) DTP Load
3) Activation of ADSO
Activate the Processchain.
In order to start real-Time data loads via Processchains.
Need to do the change below settings:
Start Variant -> immediate schedule and SAVE.
Click on the “Streaming” and check “Process Chain Run in Streaming Mode”.
Activate the Processchain & execute.
Now you can see the Streaming is “Green” and it will automatically run the processchain whenever there is a change in BGTEXT table
Now the Processchain is ready to run in Real-time mode.
After the streaming mode is Active in BW Processchain.
Make sure the below Job is Actively running in SM37 for every 15 mins in SLT server.
This Job will send an event to trigger the Processchain automatically when there is change in the SLT delta queue for particular table.
After all the above steps are done, the data will be loaded to ADSO automatically by processchain in real-time.
Great blog, thank you for your contribution Ravi.
As I understood, realtime in terms of this scenario means, that the data is posted every 15 min. right?
This is a Real-time replication of Data from Source table to BW, when there is a change in Source table, the processchain will trigger instantly in BW side and loads the data into ADSO.
I think the job which I mentioned in the blog to run every 15 mins is for breaking the long running ODQ job. If you see in blog, the screenshot shows there is always a active job running for ODQ. when there is change in source table, the SLT Delta queue triggers an event to start the processchain immediately.
Hope I answered your question.
Thanks Ravi for the blog. Its informative.
"Breaking" sounds correct to me.
If I look at code of the program on high level, prima facie looks like this is the job Actually sending the event to process chains. Job waits for 15 mins and keeps streaming to BW for 15 mins. After 15 mins breaks off as new job has started to do the same task. And the loop goes on.
Yes, thank you.
Thanks, This thread is very useful.
But we have one question on Process chain streaming mode. We have tested in our system by scheduling streaming process chain in immediate mode but we are not automatically pulling delta records from ODP queue.
According to this thread https://blogs.sap.com/2017/02/28/streaming-process-chains/ to automatically get delta records we need to set streaming process chain to API mode.
Can you please suggest right way to follow for streaming process chain scheduling?
Wonderful Blog. Great information. Thanks Ravi.
This is very informative. How can we use ODP to integrate the SAP ECC and external cloud system like snowflake. Does ODP support this scenario?