1 2 Previous Next 16 Replies Latest reply on Mar 15, 2018 2:59 AM by Rajeev Rai

    Importing Foundation data into Remedy

      Share This:

      Hi,

       

      I have to import 1.5 lakhs users into people form in remedy version 7.6.04 SP2.

      What should be the best way to implement it. I have done it earlier thorugh following methods but not for more than 500 users.

       

      1. Through DMT tool.

      2. Through Data Import tool.

      3. With workflows by integarting client AD with remedy and then creatng vendor forms and escalations.

       

      We have both .csv file (with foundation data) and access to client AD.

      My biggest concern is 1 lakh plus users and the fact that client will provide a .csv file every week and we have import new users and modify existing users as well. Please suggest if anyone has done it before.

        • 1. Re: Importing Foundation data into Remedy
          Laurent Matheo

          lakh means thousand (1000?) right?

           

          DMT will work with that amount, I've done more actually... Though you should do that off peak hours.

          But if it's on a regular basis I guess pumping data from the ldap would be a better idea.

          • 3. Re: Importing Foundation data into Remedy
            Laurent Matheo

            Lol. Ok that's indeed not exactly the same

             

            To import new people you should have to chunk the 150 000 people file, for the initial import. Like 10 000 or something, you'll need to do some tests to see how many users your system can handle.

             

            As for modification, you wouldn't be able to use DMT. So LDAP would be the best on this, but you can't sync 100 000 people every day to check for modifications. I hope there is a "last modification" tag on the ldap, this way on only fetch ldap records newer than yesterday...

             

            We had to do this for a customer actually that didn't give us access to their ldap so they gave us a csv file, each day, with 100 000+ records everyday.

            We coded a script (vbs) that generated a "difference" file between the records imported the day before and the today's file.

            Then this "difference" file was imported using Data Import tool into a staging form we created with the information (last name, first name, login ID...). Then some workflow was triggered to update/create the people.

             

            But if you have LDAP access, it'll be easier...

            • 4. Re: Importing Foundation data into Remedy

              Could we do it through AIE or Atrium integrator more efficiently ?

              • 5. Re: Importing Foundation data into Remedy

                Hi Vivek,

                 

                Below TIP may help you in some extent:

                 

                Microsoft Excel has a limit of 65,536 rows in a spreadsheet, which limits the number of records that you can include in a single spreadsheet. To deal with this issue, copy the contents from the installation directory (the default is C:\Program Files\BMC Software\DataManagementClient) to multiple new directories. If you have installed the client files in a directory that is not in the default installation path, copy them to multiple new directories. The number of required directories depends on the number of rows of data (as a multiple of 65,536). For example, if you need three directories, you might set them up as:

                C:\Program Files\BMC Software\DataManagementClient1

                C:\Program Files\BMC Software\DataManagementClient2

                C:\Program Files\BMC Software\DataManagementClient3

                 

                For forms that have too many records for a single spreadsheet, enter the data in multiple spreadsheets and in multiple directories. If you are not loading records in all spreadsheets, you can delete the spreadsheets from the extra directories. You can run batch files from the separate directories concurrently, which enhances

                performance for loading data from the CSV files into the staging forms.

                 

                 

                BMC's test for performance benchmark of Data Loading consisted more than 1.5 lac records.

                 

                 

                 

                -Shantanu Ballal

                • 6. Re: Importing Foundation data into Remedy

                  DMT will load the data in all the staging forms which will keep your data in sync with all the related ITSM forms. I do not think that AIE or Atrium Integrator is also able to keep this sync.

                   

                  Message was edited by: Shantanu Ballal

                  • 7. Re: Importing Foundation data into Remedy
                    anupam wagh

                    Yes DMT would be the best option to keep data integrity.

                    • 8. Re: Importing Foundation data into Remedy
                      Ganesh Gore

                      for such huge number of records and weekly create/update repetitive activity, personally I'll suggest AIE.

                      1. Create one interface which will have set of workflows and staging forms.

                      2. Create AR-Csv file mapping in AIE:

                      3. Create AIE Exchange and schedule it weekly basis(e.g: Sat/Sun mid night)

                       

                      So it will fetch records from csv file and create/update records in staging form. workflows will validate and create/update people records accordingly.

                       

                      logically this interface will need updated csv file to be placed at particular location. You can implement error handling mechanism on staging form itself by using workflows (e.g invalid support group/company, login id missing, location data missing blah blah blah..)

                       

                      hope this will help you

                      2 of 2 people found this helpful
                      • 9. Re: Importing Foundation data into Remedy
                        Sylvain YVON

                        For a client we had to import 250,000+ users weekly (with a daily delta import). We first did as Ganesh explained. People are fast to import, but AR System users aren't, and we had a big delay issue. The file took really too long to import.

                        So we wrote a script to insert/update users directly into the DB (into user_x) then use arreload to for AR System to rewrite its users cache.

                        I know it's terrible, you should avoid this if possible ... That was in version 7.5, maybe you don't have to do that in 7.6.04.

                        • 10. Re: Importing Foundation data into Remedy
                          Ganesh Gore

                          I developed similar thing in 7.6.04 SP2 recently. I did not face any such issue. In fact, that was very complex as it had data of users+CI data+relationships too.

                          • 11. Re: Importing Foundation data into Remedy

                            Ganesh,

                             

                            We are also working on version 7.6.04 SP2.

                            Could I use ctm:load people as a staging form as it has all the validations required to check for correct data.

                            Then Just like with DMT we can use validate and promote and it will import the correct data to the CTM:people form.

                             

                            I am totally new to AIE and will be using it for the first time so need a little more information to work on it.

                            Do we have to create an AIE exchange to put data from CSV file to ctm:load people or to any staging form.

                            Or we should use an external database as an intermediate layer to gather data from csv into that DB and then schedule transfer betwwen external db and remedy forms.?

                            Do you have kind of document which could explain it in more detail?

                            If you have , Please forward it to : vivek_uss93@yahoo.com

                             

                            All,

                            I don't know the issue with my DMT but it always stuck in "In Progress" state whenever i try to import more than 1000 users. In the validate stage only it never goes to completed state. I have to manually kill records from thread manager form to go to promote stage.

                             

                            How long it should take normally to import 1000 records at a time ?

                            Mostly I import people records only.

                            • 12. Re: Importing Foundation data into Remedy

                              Ganesh,

                               

                              With the AIE guides and your help, I have been able to design the below successfully:

                               

                              1. Created a New AR Mapping and mapped form fields to DB columns.

                              2. Created New Data Exchange and added above mapping in it.

                              3. With above steps in place , I have been able to transfer data from a flat file into a staging form.

                               

                              I tried by modifying the data present in flat file and updates are going to staging form successfully without creating new records!

                              Now should I write a filter to transfer data from this staging form to CTM:LoadPeople  and then do validate and promote process as we do with DMT?

                               

                              Or could I use any other appraoch for loading user records to People form??

                              Please suggest simpler and better solution.

                              I feel I am just 1-2 steps away from achieving my target .

                               

                              thanks a ton !!

                              • 13. Re: Importing Foundation data into Remedy
                                Srinivas Merugu

                                Once you push the data into AR System you can easily play with it.

                                Rather than using/disturbing DMT forms and workflow, I suggest to

                                implement your own custom forms and workflows.

                                 

                                1. Create another stagging form called PeopleValidate and push the

                                validated data from your stagging form to this form

                                2. You need to perform validations on your stagging forms data like

                                mandatory values provided or not, validate company name, site, site

                                group etc. Once validation is done push it to PeopleValidate.

                                3. Create one more form called peopleInvalid to hold invalid records

                                which you will be pushing to this form f you find any invalid records

                                - not really required but helps to find invalid entries easily.

                                4. Write an escalation to push data from PeopleValidate to CTM:People form.

                                5. Write another escalation to delete data from all the stagging forms.

                                1 of 1 people found this helpful
                                • 14. Re: Importing Foundation data into Remedy
                                  Ganesh Gore

                                  Vivek wrote:

                                   

                                  Ganesh,

                                   

                                  With the AIE guides and your help, I have been able to design the below successfully:

                                  1. Created a New AR Mapping and mapped form fields to DB columns.

                                  2. Created New Data Exchange and added above mapping in it.

                                  3. With above steps in place , I have been able to transfer data from a flat file into a staging form.

                                  I tried by modifying the data present in flat file and updates are going to staging form successfully without creating new records!

                                  Now should I write a filter to transfer data from this staging form to CTM:LoadPeople  and then do validate and promote process as we do with DMT?

                                  I feel I am just 1-2 steps away from achieving my target .

                                  thanks a ton !!

                                  Cool !

                                  Now write filters on this staging form to:

                                  1. Validate data:All mendatory fields are present or not, if not set error/warning flag

                                  2. Fetch location related data from foundation forms: if invalid location set error/warning flag

                                  3. Push data to CTM:People form to create new records: On SUBMIT action and return PPL id on staging form

                                  4. Push data to CTM:People form to modify records: On MODIFY action where PPL id already exists

                                  5. Push Support groups and permission related data to respctive forms

                                  and list goes on.....!

                                   

                                  hope this will help you.

                                  1 2 Previous Next