Question:
Hi,
I have a BW loading data from R/3 and from a external source system.
From R/3 performance of data loads is good. From the external tool, performance is very poor.
How can I analyze this problem? I cannot find any feasible cause. How can I improve performance?
Regards.
Answer:
From an external system? The data is coming in a file of some sort I assume.
A few suggestions...
(1) If you are processing a file fixed width files load faster than delimited files. On a fixed width file BW doesn't have to determine the record length for each record since it is fixed width. Sorry I am not doing a good job of explaining. I am actually up on a production issue but waiting for another group to call back.
(2) How current are the statistics on the data target you are loading? If under 3x make sure brconnect is being run regularly so you have up-to-date statistics
(3) Review the load in the monitor to see where most of the time is being spent - file extraction, transfer rules, update rules. This may show areas where transfer/update rules are not coded in an optimal fashion.
(4) Can you leverage start routines
(5) What else is running during your load? Do you have contention with other loads
(6) Is thedata being loaded to an ODS or Cube? If cube sometimes a poor design can cause you issues
(7) If you are loading to the cube you can drop indexes before you load and then rebuild after the load is complete
I got that call back I was waiting for. I hope this helps.
Answer:
Hi,
Thanks for your advices. I will try them. The external system is accessed through database access (DB-Connect).
Regards,
Answer:
Well then... I don't have an experience with DB-Connect so I can't speak to performance tuning specific to that "mode" of loading data.
Another thing I thought about was if your update rules are looking up Master Data you might want to do some analysis and see if there are Master Data tables that can be buffered. We did that and in some instances it helped greatly.
Also if you were looking up data from another ODS secondary indexes can also help dramatically if the primary index is not being used based on your selection criteria.
Again, probably not specific to DB-Connect but other areas I have learned to check on.
Good luck.
Answer:
Another thing I thought about was if your update rules are looking up Master Data you might want to do some analysis and see if there are Master Data tables that can be buffered.
Please can you explain how can we buffer Master data tables. We are having Master data look up in update rules. thats taking lot of time when loading data.
Thanks in Advance.
Ram
Answer:
Unfortunetly this is where my ability to speak intelligently breaks down. At my company our Basis group would handle these requests and I am not familiar with the technical steps to implement. It is more that I am aware of the possibility of buffering tables.
That being said...
I believe you can buffer at two different levels... either SAP or at the database level. I would recommend that you speak with your Basis or DBA groupt o understand what could be done to help you out.
I did try to do some research based on a performance tuning workshop I went to in Montreal.
At the SAP level you might want to look into Single Record Buffering or Generic Key buffering (table) and how those can be implemented to your particular situation.
Again, speak with your DBA group to understand if you can do anything on the database level and get an understanding of any risks associated with having tables buffered in memory.
If you are looking up Master Data and not using the primary key for the lookup you maybe able to define secondary indexes on the Master Data table in question to help speed up performance.
Also consider using a start routine to build an internal table in memory to store the master data for look up.
You could also search OSS for "buffer table" and see what comes up.
I called one of our Oracle DBAs to understand buffering on the Oracle side but he was not available.
Again I know of the possibility, not of the technical implementation but I think you should have enough to go on. If I come across anything else I will post an update.
I hope that helps.
Answer:
I followed up with one of our Oracle DBAs and it is possible to buffer a table in memory. The entire table is basically "pinned" in memory. You have to make sure you have enough memory to hold the tables you want to buffer this way. Risks are small because updates to the table are written to the physical table as well as being update in memory.
Answer:
Thanks BWWorkman for your useful analysis of buffering master data both on SAP side and database side. I was wondering after going through ur explanation as can we store master data in an ODS and then call them in a start routine, instead of buffering in database side? Well I havent tried this before but this is just a suggestion from me and if u pals find this ridiculous just ignore it.
_________________
Raj
Have a Nice Day:-)
Answer:
Yes you can "buffer" master data in the start routine. We do this quite a bit in my organization in fact I am working on just that.
The buffering in the start routine is not the same as the SAP or DB level buffering. Essentially you are creating and internal table in memory that is used as a lookup table by the local update rules.
You have to be careful though. In my case I have location master data used in a Transportation project and the location master data will be over 100K records. I am not going to create an internal table with all 100k just to look up a smaller set of location attributes.
What I am trying to do is determine all the location ID in the data package that I will have to look up and then buffer those IDs and associated attributes in the start routine internal table.
You can use this technique in by itself or in combination with the SAP/DB level buffering as well.