Todd Baginski's Blog - A SharePoint, Mobile, Office365, PowerApps and Azure Blog
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.
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.
The following components are used to supplement the SharePoint User Profile database with data coming from a back end database containing employee information.
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.
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.
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.
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.
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.
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.
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.
To verify the Full Import worked successfully follow these steps.
First, verify the properties were populated with values.
Next, verify the populated property values were mapped to the appropriate users.
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.
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.