If you look hard enough you can find several articles on the Internet which describe different ways to enhance SharePoint User Profiles with the Business Data Catalog. Each of the articles takes a different approach to implementing this functionality, however none of the articles provide a prescriptive step by step approach and end to end example. This article is geared toward someone who has limited or no experience with the BDC and provides a prescriptive step by step approach and sample code.

The other articles I found on the Internet about this topic include the following, they are well worth reading.

• Jimmie’s Sharings Importing Profile from BDC Blog Post – This article provides some excellent troubleshooting steps as well as a high level overview of the process.
• Toby Statham’s Blog Post – This article describes how to set up the MOSS Single Sign on Service and use it inside an Application Definition and also provides an overview of the process.
• Sahil Malik’s Blog Post – This article provides a high level overview of the process.

Business Context

Before we get started with the detailed technical documentation let’s take a moment to understand the business context associated with this topic. Imagine your company has implemented MOSS. My Sites and User Profiles have been set up and configured on your SharePoint server farm. The User Profiles are set up to import information from Active Directory using the out of the box functionality. Life is good and the My Site – My Profile pages display information like first name, last name, title, phone number, e-mail address and organizational hierarchy.

Your company also has an HR system which stores additional information about employees in a separate database, not in the Active Directory. You would like to enhance the User Profiles with additional information that is stored in the HR system’s database. Perhaps this information includes the city and state where the employees live.

You have a couple of options. One option is to write some code to populate the user accounts in the Active Directory with the information from the HR system and keep it in synch. Going with this option requires custom development and in the long run introduces another system you have to support in your organization. Another option is to use the Business Data Catalog and the SharePoint Search Service to query the information you want to add to User Profiles and keep it up to date.

Solution Components

The following components are used to supplement the SharePoint User Profile database with data coming from a back end database containing employee information.

• Back end database: In the example I am using in this article the sample database contains employee information which is not stored in the Active Directory.
• Business Data Catalog: An application definition is created to register the back end database with the Business Data Catalog.
• SharePoint Search Service: The SharePoint Search Service is configured to crawl the database registered with the Business Data Catalog and populate the profile properties in the User Profile Database with the information.
• User Profile Database: The User Profile Database stores the information retrieved by the Business Data Catalog and the SharePoint Search Service. The User Profile database is queried at runtime by the controls on the My Sites which display the information.

Active Directory account

The account used in this example is litwareinctoddbaginski. Here you can see the property pages which define the user account in Active Directory. Notice the City and State values are not populated in the Active Directory.

Back end database

In this example I’m using a SQL Server 2005 database named LitwareIncEmployeeInformation. The database has a table in it named dbo.EmployeeData which has the following schema.

The database contains sample data for my user account inside it. The sample data looks like this.

Implementing the solution

Application Definition

The first thing we need to do is create an application definition to register the dbo.EmployeeData table in the LitwareIncEmployeeInformation database with the Business Data Catalog. The application definition includes a Finder, a SpecificFinder, and IDEnumerator methods. The IDEnumerator method is used by the SharePoint Search Service to index the database and the SpecificFinder method is used to return information for a given record in the database. The Finder method may be used for testing purposes. I like to drop a Business Data List Web Part on a SharePoint Web Part Page after I import an application definition and configure it to display the data for an Entity I just registered with the BDC. Taking the time to do this after importing an application definition ensures the entities you have registered with the BDC are functioning properly and can save you a lot of time and headache in the event your application definition was not created properly.

You can use the Microsoft Business Data Catalog Definition Editor which is part of the SharePoint Server 2007 SDK: Software Development Kit, or the BDC Meta Man to generate the application definition. I used the BDC Meta Man to generate the application definition used in this example. Generating application definitions is a large topic, too big for this article. Please see the documentation in the SharePoint Server 2007 SDK: Software Development Kit or the step by step videos on the BDC Meta Man web site for more information.

Just remember, as long as you generate the Finder, SpecificFinder, and IDEnumerator methods you will be good to go. You don’t need any FilterDescriptors to make this work, it is very straightforward. You can also use the application definition which is part of this example as a reference to help you along the way.

After you create the application definition go to the Shared Services Administration web site and Import the application definition.

Once the application definition is imported you can view it in the Shared Services Administration web site.

Notice the dbo.EmployeeData table is registered with the Business Data Catalog as an entity with the friendly name EmployeeData. Also notice the Entity is Crawlable because it has a SpecificFinder method defined for it.

Import Connection

Now that an application definition has been created and imported into the Business Data Catalog we can create an Import Connection to connect to the EmployeeData entity defined in the Business Data Catalog. The Import Connection is used to populate the User Profile database with additional information. To create an Import Connection follow these steps.

1. Open the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. Click the View import connections link
4. Click Create New Connection
5. In the Type dropdown list select Business Data Catalog
6. In the Connection Name textbox enter LitwareIncEmployeeInformation
7. Click the browse button to the right of the Business Data Catalog Entity textbox
8. In the Business Data Type Picker select LitwareInc Employee Information – EmployeeData
9. Click OK
10. In the Connection section, select the Connect User Profile Store to Business Data Catalog Entity as a 1:1 mapping
11. In the Return items identified by this profile property textbox select WorkEmail
12. Click OK

When you are finished you will see the new import connection in the list.

These steps set up a connection to the dbo.EmployeeData table registered with the BDC. The connection is set up in a 1:1 fashion because there is exactly one row of data in the dbo.EmployeeData table in the LitwareIncEmployeeInformation database for each employee. The WorkEmail profile property is used to match the appropriate record in the dbo.EmployeeData table in the LitwareIncEmployeeInformation database with a given user in Active Directory. The WorkEmail property populated for the litwareinctoddbaginski Active Directory user account matches values in the EmailAddress column in the dbo.EmployeeData table in the LitwareIncEmployeeInformation database. It is possible to match on other columns as well. For example, if your back end database had a column which held the Active Directory user account name for each employee then you could do the lookup based on the AccountName profile property.

Note: If you are using a database that contains many rows of data for a single user you could use the 1:many mapping. If you take this approach you will need to adjust your application definition to include a FilterDescriptor which can then be used to return all the rows for a given user account.

User Profile Properties

At this point SharePoint knows how to get the data from the back end database and match it to a user defined in Active Directory by using the user’s email account as the key. The next step is creating new profile properties in the User Profile database to store the information returned from the back end database. To create the profile properties for the City and the State, follow these steps.

1. Return to the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. In the User Profile Properties section, click Add profile property
4. In the Property Settings section enter the following values
   • Name: EmployeeHomeCity
   • Display Name: City
   • Type: string
   • Length: 50
5. In the User Description section, in the Description text box enter This user profile property contains the city where the employee lives. This property is populated with information from the LitwareIncEmployeeInformation database.
6. In the Policy Settings section in the Default Privacy Setting drop down list select Everyone
7. In the Display Settings section uncheck Show on the Edit Details Page and check the Show changed in the Colleague Tracker web part
8. In the Property Import Mapping section, in the Source Data Connection drop down list select LitwareIncEmployeeInformation
9. In the Data source field to map drop down list select City
10. Click OK
11. In the User Profile Properties section, click Add profile property
12. In the Property Settings section enter the following values
    • Name: EmployeeHomeState
    • Display Name: State
    • Type: string
    • Length: 50
13. In the User Description section, in the Description text box enter This user profile property contains the city where the employee lives. This property is populated with information from the LitwareIncEmployeeInformation database.
14. In the Policy Settings section in the Default Privacy Setting drop down list select Everyone
15. In the Display Settings section uncheck Show on the Edit Details Page and check the Show changed in the Colleague Tracker web part
16. In the Property Import Mapping section, in the Source Data Connection drop down list select LitwareIncEmployeeInformation
17. In the Data source field to map drop down list select State
18. Click OK

Full Import

Finally, the easy part! The final step is kicking off the Full Import to query the back end database, return the information and add it to the new profile properties for the corresponding user accounts in the User Profile database. To begin the Full import follow these steps.

1. Return to the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. Click Start full import
4. In the Profile and Import Setting section the Profile import status will change to Enumerating
   
5. Click the Refresh link until the Full Import is complete. This can take several minutes or even hours if your database is very large.
   

Verification

To verify the Full Import worked successfully follow these steps.

First, verify the properties were populated with values.

1. Return to the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. Click View profile properties
4. Scroll to the bottom of the page and find the Custom Properties section
   
5. Click the State property and select the Edit option from the drop down menu
6. In the Usage section, verify the number of user profiles which are using the State property is accurate. In this example only one user will have the State profile property information populated because the same database contains only one row of information.
   
7. Perform the same verification for the City profile property.

Next, verify the populated property values were mapped to the appropriate users.

1. Return to the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. Click View user profiles
4. Find a user profile in the list which you wish to verify. In this case I will use the LITWAREINCtoddbaginski account.
5. Click on the user account name and select Edit in the drop down menu.
   
6. Scroll to the bottom of the list and verify the information which matches the user’s email address in the database is correct.
   

Enhancing the My Site – My Profile Page

Now you are ready to enhance the My Site – My Profile page to display the City and State information from the back end database. To do this, follow these steps.

1. Return to the Shared Services Administration web site.
2. In the User Profiles and My Sites section, click User profiles and properties
3. Click View profile properties
4. Scroll to the bottom of the page and find the Custom Properties section
   
5. Click the State profile property and select the Edit option from the drop down menu
6. In the Display Settings section check the Show in the profile properties section of the user’s profile page checkbox
7. Click OK
8. Repeat the process for the City profile property
9. Open a web browser and navigate to the My Site – My Profile page. The screen shot below shows the My Site – My Profile page displaying the City and State User Profile Properties.
   
10. If you wish to change the header for the properties return to the View Profile Properties page and edit the name of the Custom Properties section. For this example, I changed the section name to Employee details.
    

    Here you can see the updated category name in the My Site – My Profile page.

    

You can download the sample database, and application definition used in this article here.