Gavin We have just developed an user bulk updating solution whereby the user attaches a csv file into a standard attachment field on a standard Remedy form and saves a new record. The attachment is saved to the server and the save triggers a command line (Run Process) Import using a shell script (Unix) and a previously created mapping file. The import then populates the target table in Remedy.
The benefit is that the mapping file can be set to require (or otherwise) Mandatory fields, update previous records (as is required in our case) or create new entries for each record. As it is a Remedy tool, the usual rules are applied to creating the new records in the target table, i.e. field lengths, types, etc. Hopefully the original CSV file will have been thoroughly checked anyway.... I have run a couple of thousand records through it, taking a few minutes but I haven't gone to tens of thousands, yet... We are ARS 6.3 patch 23, Oracle 9.2, AIX 5.2 but I am sure there will be a Windows alternative for our shell script... I can give you some details if it would help. Mark Rushton IBM Global Services Remedy Engineering & Development Team Email: [EMAIL PROTECTED] Tel: 01962 822863 Mob: 07930 336843 "Coleman, Gavin" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 29/07/2008 08:50 Please respond to [email protected] To [email protected] cc Subject Speed of Data Import ** Morning List. I wonder if anyone can give me some advice. We are trying to build a data import facility into Remedy. This will enable users to import .csv files with up to 50,000 records into a staging form. We would like to give the users the ability to validate these records (based on a set of user defined criteria) and then load them into the AST:Configuration Item (CI) form. Here is our current design: Web based tool pushes data in .csv file into Oracle table. Once this is done (50,000 records takes about 8 minutes), it calls a web service which starts up a Remedy API. The API loops round the records in the Oracle table and inserts them into a Remedy form. Validation occurs on this form (E.g. Is Company field Mandatory, Unique or a Primary Key) and using a console, the user is able to insert the records into the AST:Configuration Item (CI) form. This design came about because we are unwilling to provide Administrator Access to our server to allow users to use the Remedy Import Tool. This design works. However, there is a lot of validation going on on the Remedy staging form. This means that to process 10,000 records takes about 2 hours. The validation takes 1.38 seconds per record. If we have a customer wanting to validate 50,000 records daily we are looking at about 10 hours. This is not acceptable. I have a few questions: 1. Is this design the right way to go about this? 2. Has anyone else done anything like this for the amount of records we are talking about? 3. Has anyone got any suggestions to improve the speed (aside from the usual indexes etc)? We could potentially move the validation to the Web side of things (Biz Talk server), but would utilising the API to do a Merge operation significantly help matters? ARS Version 6.3 patch 24 Oracle 9.2 Windows 2003 servers Hope you can help! Thanks, Gavin Coleman Senior Analyst/Programmer Computacenter (UK) Ltd Services & Solutions Hatfield Avenue Hatfield, Hertfordshire, AL10 9TW, United Kingdom T: +44 (0) 1707 631662 E: [EMAIL PROTECTED] W: www.computacenter.com ********************************************************************** COMPUTACENTER PLC is registered in England and Wales with the registered number 03110569. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW COMPUTACENTER (UK) Limited is registered in England and Wales with the registered number 01584718. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW The contents of this email are intended for the named addressee only. It contains information which may be confidential and which may also be privileged. Unless you are the named addressee (or authorised to receive mail for the addressee) you may not copy or use it, or disclose it to anyone else. If you receive it in error please notify us immediately and then destroy it. Computacenter information is available from: http://www.computacenter.com ********************************************************************** __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
