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