Lotus Connections Profiles Population Wizard, What is Really Going On Under the Covers

I have been doing quite a bit of data integration lately to bring together data from four different sources in to Lotus Connections Profiles.  Along the way I have learned quite a bit about Tivoli Directory Integrator (TDI).  TDI while a little complicated at first is a pretty flexible tool capable of carrying out all the data integration both initially, and maintaining the data across systems. If you are installing Lotus Connections your first interaction with TDI will be via the Database Population Wizard, a guided interface which executes TDI scripts in the background.  I thought it would be a good idea to put together some information on what is actually going on in the background, as a way of explaining TDI a little, and gain a better understanding of the process. I am using the following software TDI 6.1.1 (Part Number C9666ML) TDI 6.1.1 Fix Pack 3 TDI 6.1.1 IF0008 Connections Database Configuration and Profiles Population Wizard (Part Number C1L11ML) My database is DB2 though Oracle and MS SQL are also supported ( I have worked extensively with Oracle and DB2) When you launch the population wizard the first screen is the Database Configuration 1 As you fill in the fields, the values are actually being stored in a file named profiles_tdi.properties (this file is located under the Wizards directory in “TDIPopulationTDISOL” this is the TDI Solutions Directory that will be used when executing TDI for Profiles.  We will come back to this file a little later but here are the relevant sections dbrepos_jdbc_url=jdbc:db2://db2.the80s.com:50000/PEOPLEDB dbrepos_password={encr}k2bdc4849865965597ULZ7kTRrzm7q/4pAxPtLgfQXT1TVdC8l7cYVotd+Tryy8287TpX5HM8wMAnfo= dbrepos_username=lcuser dbrepos_jdbc_driver=com.ibm.db2.jcc.DB2Driver The next screen is where you configure your LDAP Connection 2 and the relevant lines being populated in the profiles_tdi.properties source_ldap_url=ldap://sitcom.the80s.com:389 source_ldap_use_ssl=false Next up is the credentials to connect to LDAP 3 and the relevant lines being populated in the profiles_tdi.properties source_ldap_user_login=Sitcom Administrator source_ldap_user_password={encr}Xs9eJLne8LBtcYq5Yo+91Hrd1jnS6RG4sd9699655VHZYmBeEbSOpvkds7+9IXvlDjgwu7cGcfAJvzCu/X Next is defining the search base, and the LDAP search filter in this example my O is ‘the80s’, and I am filtering for Person Documents 4 and the relevant lines being populated in the profiles_tdi.properties source_ldap_search_base=o=the80s source_ldap_search_filter=(&(uid=*)(objectclass=inetOrgPerson)) In the next screen you map the attributes from your LDAP to the fields in Profiles, these values will be saved in map_dbrepos_from_source.properties which is also under the Wizards directory in “TDIPopulationTDISOL” 5 These values should be set as they apply to your environment.  While not completely relevant to this topic the key field is the PROF_GUID field which needs to be set specifically for your LDAP.  For Domino LDAP is is ‘dominoUNID’  This table will display the specifics for the various LDAP configurations supported in Connections.   Any field you don’t want populated should be left blank, and will contain the value ‘null’ in the propertied file. PROF_GUID=dominoUNID PROF_FLOOR=null Finally when you populate the database you have the option to run other jobs at the same time, these values are stored in the profiles_tdi.properties 6 country_table_csv_file=isocc.csv – this is to populate country code information in to the COUNTRY Table (isocc.csv is provided in the solutions directory if you are using standard ISO country codes) The remaining options are more company specific so you have to find the source for the data in your organization department_table_csv_file=deptinfo.csv populates the DEPARTMENT table with your organizations department scheme organization_table_csv_file=orginfo.csv populates the ORAGNIZATION table with your organizations organizational units emp_type_table_csv_file=emptype.csv populates the EMP_TYPE table with employee type information workloc_table_csv_file=workloc.csv populates the WORKLOC  table with your organizations work location In addition for each job there is a line defining the separator, they are all defaulted to ‘;’ workloc_table_csv_separator=; To change them you need to edit the file, there is no option in the UI The final option is to run the Mark Managers task, run this to mark managers so that Reporting Structure will display properly in Connections (only run this task if you are providing the org structure in the data load) This is all the configuration the wizard requires and if you simply let it run, it will populate the database with the parameters you selected.  What you will see next is how you can leverage the setup information provided in the wizard to run these jobs individually,  schedule them to run on an interval to maintain the data, or leverage the configuration already completed to run your own custom jobs. TDI should be launched from the Solutions Directory  in this case WizardsTDIPopulationTDISOL.  Edit tdienv.bat for your environment, run it, and then execute ibmditk.bat in from where you installed TDI (Program FilesIBMTDIV6.1.1 is the default) Once launched open the profiles_tdi.xml, and expand the Assemblylines sections.   tdi2 Each assembly line represents one set of data to be managed, you will notice the various jobs that correspond to the steps we looked at earlier for example “populate_country” is the job to populate the COUNTRY table. Opening up the populate_country assembly line we can see the various components of the assembly line. “http://www.flickr.com/photos/curiousmitch/3119673012/” title=”tdi3 by CuriousMitch, on Flickr”>tdi3 The first thing you should note is that there are multiple config tabs, this is a recurring them throughout TDI and takes a little getting used to.  The top Config Tab is the configuration for the entire assembly line.  The one below it is the config for a specific flow in this case “update_db”.  An assembly line can contain one or multiple Feeds and Flows. Though they don’t appear that way each of the items in the red box is a clickable item.  For example JDBC URL which as you can see contains the value we specified earlier in the properties file.   We could simply type it in here, but look how many assembly lines and how many configurations we have that might require the same value.   If we click on “JDBC URL” we see that the Expression is {properties.profiles:dbrepos_jdbc_url} tdi4 If we navigate away from the Assembly line and over to the Properties section we can see the various “Properties” files attached to this configuration tdi5 On the Connector Configuration tab we see that this is where the assembly line is connected to the “profiles_tdi.properties” file we set up earlier tdi6 On the Editor tab we can see the values assigned tdi7 By defining the properties here, and configuring the assembly lines to use references instead of hard coding them, the TDI solution becomes much more manageable.  For example if you have a production and a test environment you can  maintain your assembly lines to be identical and simply use different property files this will save you lots of time when you make changes to your solutions. As you develop your own assembly lines you can define your own property files and properties with in them for ease of use. Lastly you can execute an assembly line without launching TDI to do that you need to run ibmdisrv.bat which is in the directory where TDI is installed (again run it from the solution directory) followed by the xml file which contains the assembly  line with a -c parameter followed by the assembly line to run with the -r parameter.  To run the populate_country assebly line we looked at above the command would be “ibmdisrv -c profiles_tdi.xml -r populate_country Additional Resources Lotus Connections Infocenter: Populating the Profiles database Tivoli Directory Integrator documentation IBM Tivoli Directory Integrator Users Group You Tube has a few videos with examples of TDI, the search results are here. .

8 Responses to Lotus Connections Profiles Population Wizard, What is Really Going On Under the Covers
  1. Stuart McIntyre
    December 19, 2008 | 12:06 am

    Excellent post, Mitch, and one that really needed to be written. Well done.

  2. Torsten Hoffmann
    December 19, 2008 | 2:11 am

    Thanks Mitch, and another link dogear’ed Emoticon

  3. Duane Fitzpatrick
    December 19, 2008 | 7:55 am

    Yes, Thank you, Mitch for the article. Question: what if the manager guid is populated from a different data source, like a feed from HR. Do I need to do anything special to get the reporting hierarchy to work in Profiles? I usually have the “run task that marks profiles of managers” set to “No” since we don’t pull that info from Domino LDAP. Any clue how that works? Thank you in advance. Duane

  4. Chris Whisonant
    December 19, 2008 | 9:09 am

    Another great post Mitch – thanks!

    btw, I like the domain you’re using here – sounds pretty fun… Emoticon

    And it’s hilarious that your username on the first screenshot is root on a Windows system… Emoticon

  5. Mitch Cohen
    December 19, 2008 | 10:06 am

    @3 Duane – There are 2 fields that make up marking a manager in the database

    PROF_IS_MANAGER – this is set to Y or N
    PROF_MANAGER_UID – this is set to the value stored in the managers PROF_UID field (and it is case sensitive)

    What we did was simply get this data out of our HR system and use TDI to mark the managers fields, and compute and fill in the PROF_MANAGER_UID field. Feel free to e-mail me or get my contact info from Tim if you want to discuss further.

    @4 Chris – thought you would like the domain! I suspect that domain will make an on stage appearance @ LS09

  6. Paul Smith
    December 22, 2008 | 6:52 am

    Great post Mitch – any chance of adding it to the Connections Wiki on developer works?

  7. Anonymous
    December 30, 2009 | 2:15 pm

  8. Thomas wistrom
    February 17, 2010 | 7:50 am

    great article but can I use TDI v7.x instead?