SLT ODP : Real-Time Data Replication from Non-SAP(MSSQL) to BW (ADSO)

[former_member364613]

Hi All,

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

Target: ODP



For non-SAP, the supported databases are, In my case I am using MSSQL:

• MaxDB


• DB2


• DB6


• MSSQL


• Oracle


• Sybase ASE


• HANA

3) Make Sure that the database user has the respective privileges:

1. Establishing a connection (create session) to the database.


2. Connecting to a specific schema from the database.


3. 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”.

Click ‘Continue’.



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.

JobName: ODQ_DAEMON_CLIENT_100

 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.

 

The-End.