Walkthrough: Import Data From a Line Of Business System into SharePoint User Profiles


In this walkthrough, I’ll show you how to import data from a line of business (LOB) system into SharePoint user profiles and make that information searchable.  Using this approach, you can leverage information that is stored in other LOB systems and create a unified view of user information in SharePoint.


Create an Interface to the LOB System


Suppose your company has an HR system that contains information about employees.  Since it is the system of record for employee data, we don’t want users to re-enter this information in SharePoint.  In this example, the HR system is backed by Sql Server and the data we need is stored in a table named “Employee.”  Although we are accessing the database directly in this walkthrough, you should consider using a web service to provide a layer of abstraction and a well-defined interface. 


In our HR system, the Employee table contains a column that we can use to look up employees based on information we have in SharePoint.  In this case, we will use the “UserAccountName” column, but in a real-world implementation you need to find a key that is guaranteed to be unique such as the user’s security identifier (SID).  The following shows the design of the Employee table:


image 


When SharePoint to imports data from the HR system, it is essentially a crawl operation, so the SharePoint search account will need read access to the Employee table.  Because this database contains sensitive information, we will only allow the account access what it needs and nothing more:


image


Create Application Definition


Ceate an application definition XML file so SharePoint knows what entities and actions are available from the HR system.  We could do this by hand, but it is much easier to use a tool such as BDC Meta Man.  To start, select Connect to data source:


image


Enter database connection information and click Connect:


image 


Once connected, drag the Employee table onto the design surface:


image


The tool generates the Employee entity and three actions: Finder, Specific Finder and IDEnumerator.  If you are not familiar with these terms, here is a simple explanation.  A Finder returns a list of entities.  A Specfic Finders returns a single entity, and an IDEnumerator returns the identifiers of all entities.  By default, the tool uses the primary key as the parameter to the specific finder.  Since we don’t have the employee number in SharePoint, we need to modify the Specific Finder action to take an account name.  Select the action and click Modify:


image


Clear the condition from the EmployeeNumber column and add a condition to the UserAccountName.  Click Generate


image


You’ll see a preview of the query.  Click OK


image


If prompted to configure parameters, just put something in the fields (like a space) and click OK


image


Edit the HR System details:


image


You can use this screen to modify the system and instance name.  You can also set the authentication mode, which we’ll set to RevertToSelf.  This basically means, “whoever you are impersonating, revert to who you were.”  So if you were accessing an entity as contoso\johndoe it would revert back to the SharePoint application pool account.  If you used PassThrough, contoso\johndoe would be accessing the database as himself.  After configuring the system, click Save


image


Select Configuration > Settings and specify where the XML file should be saved.  Enter a path and click Save


image


Click the green “play” button to generate the application defintion


image


Import Application Definition


Go to your Shared Services web site and select Import application definition in the Business Data Catalog section.  Browse for the application definition file and click Import.


image


Configure BDC Permissions


Configure the SharePoint permissions for the application and entities.  Click Manage Permissions:


image


Select the search account, check the Execute permission and click Save:


image


The search account needs access to both the application and the employee entity, so from the manage permission screen for the application, click Copy all permissions to descendents:


image


This will copy the same permissions from the application to all of the entities in that application


image


Test the BDC Application


You can test the application by accessing the profile page for the Employee entity.  You can find the url of the profile page by viewing the entity:


image


Copy the url into a browser and enter a parameter for UserAccountName.  You will see a generic error message “Unable to connect to [Instance Name].”  This is because we used RevertToSelf and the account accessing the database is the application pool account (contoso\spfarm in this example).


image


To prove this, grant the application pool account rights to the Employee table and the entity details will display


 image


Give the Search Account Permission to Update User Profiles


Because the import is running as the search account, that user must have the permission to update user profiles.  To configure this, from Shared Services, select Personalization services permissions:


image


Click Add Users/Groups:


image


Enter the search account and check the Manage user profiles permission.  Click Save


image 


Add an Import Connection


Next, we will configure the user profile to import the Location column into the SharePoint user profile.  In the Shared Services web site, select User profiles and properties:


image


To connect to the HR System, you have to create an import connection.  To do this, click View import connections:


image


Click Create New Connection:


image


Select Business Data Catalog and choose the entity.  There should only be one employee for each SharePoint user profile, so we’ll select 1:1 mapping.  Next, we must specify the attribute in the UserProfile to pass to the Specific Finder method.  Select AccountName and click OK


image


Add a User Profile Property and Map to a BDC Entity Attribute


Add a property to the SharePoint user profile to contain the Location we are importing from the HR system.  Click Add profile property:


image


Enter a Name, Display Name and configure privacy and editing rights:


image


In the Source Data Connection drop down, select the import connection you created in the previous step.  Select the field to map from the HR System:


image


Perform a Full User Profile Import


Select Start full import:


image


The primary (AD) import will run and then there will be a short pause before the Membership and BDC import starts:


image


After the import completes, check the import log for errors:


image


If you encounter errors, you’ll have to comb through the ULS logs to find the specific issue:


image


Verify the Import Worked


On the user profile page, select View user profiles and select a profile.  The property should now contain data from the HR System:


image


Make the Imported Property Searchable


Now we’ll do something useful with the information we imported from the HR System by making it searchable.  We’ll create a search scope so you can search for people by location.  When we added the location property to the user profile the check box was selected to make the property indexed:


image 


Before the property can be used in search, we must perform a full crawl.  From the Shared Services website, access Search Settings.  Click on Content sources and crawl schedules.  Select Local Office SharePoint Server Sites > Start Full Crawl:


image


Create a Search Scope


Once the crawl completes, return to Search Settings and select Metadata property mappings.  Locate the property you added to the user profile and edit it.  Check the box “Allow this property to be used in scopes.”


image


Return to Search Settings and select View Scopes.  Click New Scope.  Enter a name for the scope such as “Hong Kong Employees,” and click OK:


image


Next, add a rule to the scope by clicking on Add rules:


image


Select Property Query and select the user profile location property we added.  Enter a value such as “Hong Kong.”  Select Require and click OK:


image


Return to Search Settings and in the Scopes section, click Start update now.


image


From the Shared Services website, access Search Settings.  Click on Content sources and crawl schedules.  Select Local Office SharePoint Server Sites > Start Full Crawl:


image


You can confirm the scope contains items by viewing the scopes:


image


Use the Search Scope in a Site Collection


Access the site collection settings and add the scope.  From Site Collection Administration, select Search scopes.


image


Notice the search scope you created is in the Unused Scopes section.  We want it to display in the search dropdown, so click on Search Dropdown:


image


Check the box to include the new scope:


image


The search dropdown will now display the new scope.


image


You can further customize the search scope so it takes you to the people search results page.  You could even add a tab to the Search center for each location (scope) such as “Seattle,” “New York” and “Hong Kong” to make a people directory.  Finally, you can add people search web parts in other parts of the site collection to enable users to quickly find people.


Conclusion


In this walkthrough, I showed you how to import data from a line of business (LOB) system into SharePoint user profiles and make that information searchable.  This approach enables you to expose data from LOB systems and display the information in the user’s profile and other areas in SharePoint.


References and Additional Reading


Comments (4)

  1. dougmcisaac says:

    Thanks for the walkthrough. I’m going to be helping a client with this on Monday.

    Doug

  2. seolinkmart says:

    I would like to say thank you very much for your step by step snap shot tutorial.

  3. dave_gordon says:

    Hi John, any idea how this would be impleneted on SP2010 but for users without AD accounts, becuase this became cumbersome to manage.  Understanding that BCS contacins components of FIM we also have FIM 2010 at our disposal but I can't find any examples of using this to synchroise data SP 2010 user profiles with LOB data.

    Many thanks in advance

    Dave Gordon