Hello All, this is my first ever document on SDN so pardon me if I miss anything and your valuable suggestions are welcome.
I would like to share some knowledge about the various datasources that can be used to extract data into multiple systems let’s say SAP BW system and other be Informatica System. I will try to shed some light on the how the delta mechanism works in this case of all datasources. Also I will try to be as simple as I can.
Scenario: Two systems ‘SAP BW’ and ‘Informatica’ are connected to a single source system SAP ECC.
While working on this kind of scenario I could classify the datasources in some broad categories depending on their Delta Mechanism or if they are FULL loads.
- Change Pointer Based Datasources- These are mostly master datasources like 0CUSTOMER_ATTR, 0MATERIAL_ATTR, 0MATERIAL_TEXT and follow the change pointer based delta mechanism (i.e. tables BDCP2 or BDCPV in older version). The delta mechanism these follow is of type ‘E or NEWE’ (You can find it in table ROOSOURCE)
- Setup Table Based Datasources- Mainly LO extractors which deal with setup tables, ideally application area datasources in LBWE and follow delta mechanism of ABR etc. For example, 2LIS_18_I0NOTIF is one such kind and almost all datasources starting with 2LIS
- Time Stamp Based Datasources- These are the datasources which follow the time stamp table logic i.e. with delta mechanism of AIE, ADD etc. A few examples of these datasources are 0FI_GL_4, 0CO_OM_OPA_6. These datasources have special timestamp table BWOM2_TIMEST which mainly manages the delta mechanism.
- Custom Datasources or Generic Datasources- These datasources which we create of our own and we provide our generic delta fields in RSO2.
- FULL load datasources- Most of the master datasources are full loads and do not follow delta mechanism.
Good News: All of the above type of datasource work very well when these are extracted in multiple target system even when the delta extraction is running in parallel or in sequence.
So how does this work basically, answer to this is simple Delta Queues.
However, this is little tricky when it comes to extract data into multiple systems when running the delta in parallel as datasources have specific characteristics that they follow based on their kind.
In this section I will discuss only change pointer based datasources and in coming sections I will try to explain functioning of others too.
We will go through system configuration and necessary steps that need to perform before we could even start the extractions. Briefly we will go through following:
- System Configuration understanding in case of two target system
- Understanding of Change pointer based datasources functioning
- Initialization of datasource from two systems and its effect on source system
- Delta Extraction from two Systems
System Configuration: Ideally this is the basis activity and I will only provide some tcode information. To extract the data into multiple systems we will have to establish communication between the two systems which would involve following:
a) Logical system creation (tcode – SALE)
b) RFC destination creation (tcode – SM59)
c) saprfc.ini file settings (in case of Informatica this is done on Informatica app server side) – we faced a lot of issues with saprfc.ini file entries so make sure these are correct
d) ALE idoc configuration (tcode – WE31, WE30, WE21, WE20, BD64)
When the two systems are connected to one source system there will be two logical systems, one for each. For example, let’s say we are connecting SAP BW and Informatica system to SAP ECC. Then in SAP ECC there will be two logical systems, let’s say logical system names are BQ1CLNT100 and INFACONTNT (we will use these names in rest of the document below).
Change Pointer Based Datasources: These datasources work based on change pointers which are stored in table BDCP2 (BDCPV in older versions). In table BDCP2, all the change pointers are stored based on Message Types and Change Pointer ID (the key fields of this table). The Message Types are associated with each logical system. For example, if we have SAP BW system connected to SAP ECC then we will have exactly one message type between two systems. The message type linking with the logical systems can be found in table ROOSGEN.
So whenever we create/change any customer the change pointer is stored in table BDCP2 for this particular message type. Like if I create a new customer then in BDCP2 the entry would be like this:
Important field in BDCP2 table:
a) MESTYPE – This field holds the message type related to the target system. So do we manually have to create this message type? Well, answer to that is No. For any datasource working on message type and change pointer mechanism the message type is system generated. It is generated as soon as we create the logical system and replicate the datasource from target system for the first time. In above case, RS0059 was created when we replicated the datasource from SAP BW system for the first time.
b) ‘PROCESS’ – This field has indicator if the data is already extracted to target system or not. Basically if the value in this field is blank then it is not extracted in SAP BW system and will be considered for next extraction and as soon as this field is set to ‘X’ then that particular record will not be considered for next extraction.
Initialization of datasource from two systems and its effect on source system: Let us assume that we have all the communications established between all 3 systems. And we have replicated the datasource from both systems: BQ1CLNT100 and INFACONTNT from source system SAP ECC.
As we all know for delta extraction the pre-requisite is INITIALIZATION. When we initialize the datasource will have separate delta queues for both the target systems in RSA7.
Delta Extraction from two Systems: Remember, we saw that message type gets created after we replicate the datasource in source system. So if the change pointers are activated for this datasource in source system then right after replication the new/changed data gets stored in table BDCP2 for both the message types of both target system.
In below screen, we have two message types created for respective systems. Here note that we have only replicated 0CUSTOMER_ATTR in both system but 0CUSTOMER_TEXT is only present in SAP BW system. So for 0CUSTOMER_TEXT we have only one message type.
Now whenever we create new data we will have 3 records in BDCP2 table for respective message types.
Let’s create a new customer:
Check BDCP2 Table:
In BDCP2 table we have 3 records for respective message types. Here since we have 0CUSTOMER_ATTR for two different target systems so we have individual records.
For, BQ1CLNT100 –> RS0130 and INFACONTNT –> RS0147
And PROCESS is blank.
For 0CUSTOMRER_TEXT we don’t have multiple message types so we have only one change record for it’s message type.
Now, when we extract the delta data into any system they will relate to their own delta queues and message types. And data will be extracted in both systems equally without missing any data.
Summarizing – It will work in following cases:
1. Extract data from BW system regularly and not from Informatica – In this case BW will only extract data from its own message type and delta queue without disturbing data for Informatica. And when Informatica extract data after a period of time it will not miss any data that is already extracted to BW.
2. Data Recovery: For this type of datasource we generally do the data recovery by resetting the PROCESS field back to ‘ ‘. So if we need to recover data for only BW system then it will be independent from Informatica extraction as BW system data recovery will deal with only its own message type.
3. Delta in Parallel: Since message type is different so if extractions from both target system access the source system for same datasource then SAP handles the locking mechanism of base tables and it do not fail for any system.
Take away from above discussion:
· Change Pointer datasources work perfectly for multiple system extraction even in parallel delta extractions.
· Multiple target systems have separate logical systems and hence separate delta queues.
· Data recovery is safe for one system without affecting other target system
I will discuss more datasources in subsequent documents as I believe this document has grown very long. I don’t really know where to stop
Please share your thoughts and let me know if you need more information on this.