hi greig,
SAP Note 865192 - How to optimize the performance of the DTW
Summary
Symptom
There are always a lot of data to import or update through DTW. The performance of DTW is more and more important to the customers' business.
Other terms
DTW, B1, SAP Business One 2004A, SAP Business One 2004B, SAP Business One 2004C
Reason and Prerequisites
Functionality Description
Solution
The following methods will help improve the performance.
1. It is best to import/update smaller files. If the data file is very large, separating the whole file into several smaller files is recommended.
2. Also separating every item file according to the separate type of the header file. For example, for "BillOfMaterials" object, if the files include 10000 records, the header file which include 10000 records is as follows.
Linetype,RecordKey,TreeCode,TreeType,Quantity
Linetype,RecordKey,Product No.,Product Type,Quantity
H,1,B00001,3,20
H,2,B00002,3,50
H,3,B00003,3 ,100
... ...
H,10000,B10000,3 ,100
The item file includes 10000 records, that are 30000 rows is as follows.
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
I1,1,0,A0000,10,1,20,USD
I1,1 ,1,A0001 ,20,1,10,USD
I1,1 ,2,A0002 ,50,1,10,USD
I1,2 ,0,A0003 ,20,1,12,USD
I1,2 ,1,A0004 ,10 ,1,32 ,USD
I1,2 ,2,A0005 ,10,1,21,USD
I1,3 ,0,A0006 ,12,1,12,USD
I1,3 ,1,A0007 ,10 ,1,11 ,USD
I1,3 ,2,A0008 ,10 ,1,10 ,USD
... ...
I1,10000 ,0,A9997 ,12 ,1,12,USD
I1,10000 ,1,A9998 ,10,1,11 ,USD
I1,10000,2,A9999,10,1,10 ,USD
If user is prepared to separate the files into 10 files, each file includes 1000 records. The resulting files are as follows.
The header file which includes 1000 records of the first set is as follows.
Linetype,RecordKey,TreeCode,TreeType,Quantity
Linetype,RecordKey,Product No.,Product Type,Quantity
H,1,B00001,3,20
H,2,B00002,3,50
H,3,B00003,3 ,100
... ...
H,1000,B01000,3,100
The item file which includes 1000 records, that are 3000 rows is as follows.
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
I1,1,0,A0000,10,1,20,USD
I1,1 ,1,A0001 ,20,1,10,USD
I1,1 ,2,A0002 ,50,1,10,USD
I1,2 ,0,A0003 ,20,1,12,USD
I1,2 ,1,A0004 ,10 ,1,32 ,USD
I1,2 ,2,A0005 ,10,1,21,USD
I1,3 ,0,A0006 ,12,1,12,USD
I1,3 ,1,A0007 ,10 ,1,11 ,USD
I1,3 ,2,A0008 ,10 ,1,10 ,USD
... ...
I1,1000,0,A0997,12,1,12,USD
I1,1000,1,A0998,10,1,11,USD
I1,1000,2,A0999,10,1,10,USD
The header file which includes 1000 records of the second set is as follows.
Linetype,RecordKey,TreeCode,TreeType,Quantity
Linetype,RecordKey,Product No.,Product Type,Quantity
H,1001,B01001,3,20
H,1002,B01002,3,50
H,1003,B01003,3 ,100
... ...
H,2000,B02000,3,100
The item file which includes 1000 records, that are 3000 rows is as follows.
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
Linetype,RecordKey,LineNum,ItemCode,Quantity,Warehouse,Price,Currency
I1,1001,0,A1000,10,1,20,USD
I1,1 001,1,A1001,20,1,10,USD
I1,1 001,2,A1002,50,1,10,USD
I1,1002,0,A1003,20,1,12,USD
I1,1002,1,A1004,10,1,32,USD
I1,1002,2,A1005,10,1,21,USD
I1,1003,0,A1006,12,1,12,USD
I1,1003,1,A1007,10,1,11,USD
I1,1003,2,A1008,10,1,10,USD
... ...
I1,2000,0,A1997,12,1,12,USD
I1,2000,1,A1998,10,1,11,USD
I1,2000,2,A1999,10,1,10,USD
The other 8 sets should be refered to the two sets above.
User may imports all of the 10000 records by executing it 10 times.
3. Deleting the rows in which the null data exist may imporve the efficiency. For example, if user prepares the data file of the "Bank" object as follows
AbsoluteEntry,BankCode,BankName,CountryCode,DefaultBankAccountKey,IBAN,PostOffice,SwiftNo
AbsoluteEntry,BankCode,BankName,CountryCode,DefaultBankAccountKey,IBAN,PostOffice,SwiftNo
1,1,bank1
2,2,bank2
3,3,bank3
This data file have 8 fields but only 3 fields have valid values. user can transform this table to the following table.
AbsoluteEntry,BankCode,BankName
AbsoluteEntry,BankCode,BankName
1,1,bank1
2,2,bank2
3,3,bank3
In the step 4 of the DTW, that is check the input data, user must make every field of the source data correlate the corresponding target field.
Header Data
Release Status: Released for Customer
Released on: 28.09.2006 14:09:09
Master Language: English
Priority: Correction with medium priority
Category: Consulting
Primary Component: SBO-DTW Data Transfer Workbench
Secondary Components: SBO-SH-DTW-TS Troubleshooting
Affected Releases
Software
Component Release From
Release To
Release And
subsequent
SBO-DTW 2004 2004 2004
SBO-DTW 2005 2005 2005 X
SP Patch Level
Software Component Version Support Package SP Patch Level Download
SAP BUSINESS ONE 2005 A SP01 SP001 000000
Jeyakanthan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.