How about 1) Get list from external system (1,000,000) import each of them - get the ID back for each. Store SSN/ID for each in separate table (not in your system) -- will that pass security?
2) Next week - compare old list to new list for 1) Mods 2) Delete 3) News 1) Find the old ID from new SSN -- then update it 2) Find the old ID from new SSN -- then delete it 3) Since no old SSN exists -- create it To me -- that is the only realistic approach you have. Certainly you could do your orig approach -- but then you would need more ARS licenses -- bigger hardware etc... (Approx cost $500,000) Tell that to security -- $5 USD says they will let you hold a SSN/ID table somewhere else (not in the ARSystem) -John On 10/15/07, strauss <[EMAIL PROTECTED]> wrote: > > ** If you do what you are talking about, you are going to have a different > problem - no way to search for requests put in for someone last week based > on who they are this week - the PPL ID will be different. And don't tell me > your users can search on name accurately - 12,000 of the 128,000 names in my > CTM:People are not unique, and Middle name only helps with 2,000 of those. > You will no doubt have over 90,000 names in a million that are not unique. I > also doubt that the Customer's Incidents tablefield in the Incident form > will reflect any previous incidents for any one individual, and you > certainly won't be able to connect them to CIs in the CMDB. You have to > have a common, stable unique key. > > My CTM:People is being updated from PeopleSoft via LDAP using AIE 7.1 and > a staging form like you say you have. The key field is workforce id from > People Soft, but it could just as easily have been our Login ID (also unique > on both systems like workforce id) so maybe you can have them populate the > source data with login, or another unique value. Is the email address in > the source data unique, for example. Personally, I would do anything to > avoid having to reconstitute the entire CTM:People table weekly, because the > filter processing time you are seeing is unavoidable if you want all of the > appropriate customer data set in the ITSM 7 tables, with them related to the > correct customer company(ies) and having whatever access permissions they > need. I would even settle for the sequential record id from the source > system - in a previous LDAP to Remedy implementation we used that as a > linking value for lack of anything more reliable. > > Christopher Strauss, Ph.D. > Remedy Database Administrator > University of North Texas Computing Center > http://remedy.unt.edu/ > ------------------------------ > *From:* Action Request System discussion list(ARSList) [mailto: > [EMAIL PROTECTED] *On Behalf Of *William Rentfrow > *Sent:* Monday, October 15, 2007 12:35 PM > *To:* arslist@ARSLIST.ORG > *Subject:* CTM:People large data imports > > ** On my current project we will have to import about 1,000,000 CTM:People > records every week (give or take). These will be "new" records every time > because the only unique identifier we have from the external systems is an > identifier like SSN that we can't put into our system (that's a policy > decision). We're checking on ways to do updates but it's still messy due to > the item below. > > The actual import is going to be automatic and should follow this process: > > 1.) Escalation deletes all relevant CTM:People records > 2.) File is automatically put on the Remedy server with people information > 3.) Escalation fires a Run Process that imports the data into a staging > form. > 4.) Escalation fires and sets a flag field to "Process Now" on all records > in the staging form > 5.) Filter on "modify" on the staging form pushes to CTM:People > > Here's the problem - we're using decent hardware running on Solaris 9, etc > - yet the push to CTM:People takes 1.5 seconds per record due to all of > the validation that takes place. > > I have to do 1,000,000 records a week - and there are only 604,800 seconds > in a week....also, it appears using direct SQL loads won't work due to all > of the back end set fields (I can get around all of those except the GUID - > which could be set by another escalation.....and then we are back at this > same problem). > > If you've done large data imports to CTM:People I'd really like to know > how you went about loading the data efficiently. > > William Rentfrow, Principal Consultant > [EMAIL PROTECTED] > C > 701-306-6157<http://pbx/taci.cgi?exten=91701-306-6157&account=5130&context=from-internal&phonetype=SIP&callerid=6515560930> > O > 952-432-0227<http://pbx/taci.cgi?exten=91952-432-0227&account=5130&context=from-internal&phonetype=SIP&callerid=6515560930> > > __20060125_______________________This posting was submitted with HTML in > it___ > __20060125_______________________This posting was submitted with HTML in > it___ > -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax [EMAIL PROTECTED] _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
