Everything You Need to Know about BDC: Part 6 of 8

Greetings and welcome to Part 6 of our Business Data Catalog (BDC) Series.  In this article, the focus will be on configuring User Profiles to import properties from external systems via the BDC.  I’ll start off by giving you a good grounding in the overview section.  I’ll then jump into creating the two different types of import connections, 1:1 and 1:many, and then show you in detail how to map your profile properties.   Finally, I’ll wrap up with some troubleshooting tips.  By the end, you’ll have a solid understanding of how to configure your ADFs and User Profiles to import and map your properties.

In case you’re just joining us in the series, here are our previous five articles:

Part 1.  BDC Purpose and technical architecture

Part 2.  Detailed overview of the Application Definition File (ADF) and how to develop one to connect to a back-end database

Part 3.  Developing an ADF to connect to Web Services

Part 4.  Consuming Business Data through Web Parts and SharePoint lists

Part 5.  Implementing Enterprise Search with Business Data

Overview

User Profiles within MOSS are a great way to store key information about SharePoint users.  This information, stored within individual profile properties, contains useful content such as first name, last name, date of hire, the user’s manager, and numerous personal details such as interests and skills.  This user metadata can be used when executing workflow, searching for people, utilizing social networking aspects such as my sites, defining audiences, any many other SharePoint capabilities.  SharePoint contains dozens of built-in properties, and you can add any custom properties as needed.

While all of this a great benefit to enhancing SharePoint’s knowledge of these people, there may be a drawback to this.  Here is yet another database of information that needs to be populated!  In most organizations some of this information lives within Active Directory (AD) and perhaps other details can be found in repositories such as HR systems, so you can say that SharePoint is really just duplicating what already exists elsewhere.  The primary goal of this article is to show you how you can eliminate this redundant data entry by pulling this authoritative information from your external systems through the BDC.

User Profiles and the profile properties are all maintained within your Shared Services Provider (SSP).  User profiles can be created manually, or you can create import connections which allows you to automatically synchronize these values from external systems.  Supported external systems are AD, LDAP (such as Lotus Notes), and BDC.

An important detail you must understand is that only AD or LDAP can be set up as a primary (or master) connection.  A BDC connection cannot be.  A primary connection means that a SharePoint profile with an account name (e.g. domain\username) can be created from this source.  BDC can only be a secondary connection, meaning that it is only able to fill in additional properties for an existing profile—in other words, it cannot create a profile.

To better explain, see Figure 1 below which shows the profile being created (based on the Account name) from AD and additional properties coming from an HR System (click image to enlarge):

Figure 1: Mapping profile properties between SharePoint and external systems

The arrows reflect the mapping between SharePoint profile properties and columns in external systems.  You can see that some information (such as first and last name) may be duplicated in external systems.  When defining mapping for a profile property, you can only specify a single source for the data.  Based on the arrows, the decision was made to pull the first and last name from AD.

For many organizations, this is a common scenario.  AD, maintained by the IT group, typically contains only a minimal amount of accurate user meta data.  Other systems, often maintained by the HR group, contains additional values.  SharePoint allows you to set up multiple primary and secondary connections to ensure all your profiles can be created and populated as needed.

Now that you have a basic understanding, the rest of the article will focus on how to create and configure these secondary BDC import connections.

Creating BDC Import Connections

Prior to creating a BDC Import Connection, I recommend you configure and ensure that your AD or LDAP primary connection is functioning.  Once this is working, you can then focus on your secondary BDC connection.  For my upcoming demos that I’ll walk you through, I am pulling accounts from a single domain inside AD.

To create a BDC Import connection, first access your User Profile and Properties screen within your SSP (Central Administration –> SSP –> User profiles and properties).  On this screen, click on the View Import Connections link.  Then click on the Create New Connection toolbar button.  Finally, choose Business Data Catalog type in the drop down list.  This will yield the following screen:

Figure 2: Creating a BDC Import Connection

In Figure 2 above, you’ll notice that you have two mapping options (depicted by the radio buttons),  1:1 and 1:many.  Let’s look at each option in detail:

1:1 Import Connection Type

A 1:1 connection is the most common type of connection that is used.  This means that a single profile in SharePoint will map to a single entity record that is returned from BDC.  For example, if we have a user profile for synergy\afuller as shown in Figure 1 above, this user will also exist in an external system.  For a 1:1 mapping, a Specific Finder method will be called based on the selected entity.  This shouldn’t surprise you as a Specific Finder method returns a single record from the external system.  A good question you might be thinking is how does it know which record to pull?  I’ll answer this question as I show you how to configure the connection.

To configure the 1:1 mapping as shown in Figure 2, we need to first start off by creating or identifying an existing profile property that can be used to uniquely identify the user in the external system.  This property must also match the identifier that is defined for your entity in your ADF.  In my example, I will be using EmployeeID which is the primary key for an Employees table in a SQL Server database.  Employees represents a table usually found in HR or ERP systems.  Here is the Identifier as reflected in the ADF:

Figure 3: EmployeeID as the Identifier for Employees Entity

And here is the Specific Finder:

Figure 4: Specific Finder for Employees Entity

As you can see, I have selected EmployeeID to be the unique identifier.  Since I don’t have a built-in profile property in SharePoint that stores EmployeeID, I’ll create one.  This is necessary for the 1:1 mapping to work.  Here is how it looks after I’ve created it:

Figure 5: Newly Created EmployeeID Profile Property

I chose the integer type because the property’s type must be compatible with the type defined in the Identifier (which was defined as System.Int32 as shown in Figure 3).

Now that this is done, I can create my BDC Import connection.  (Note: Even though you are only creating a 1:1 mapping that will use a Specific Finder, this screen also expects a Finder method.  Even though the Finder method is only used when creating 1:Many connections as discussed below, you must still have one or you will get an error when you select the entity from the catalog in this screen.)

Going back from Figure 2 above, after selecting Business Data Catalog as the type, you must enter a connection name.  I recommend a friendly name that you’ll easily recognize. For my demo, since I’m pulling employee information from an HR system, I’ll call the connection HREmployee.  Here is how it looks just prior to saving:

Figure 6: Creating a 1:1 Import Connection

You’ll notice that I selected the EmployeeID profile property that was just created.  For this to work, I now need to go into my current SharePoint profiles and manually enter in a values for EmployeeID.  This is necessary in my example since SharePoint must know what value to pass to the Specific Finder.  If you’re using a built-in profile property that is already populated (such as the Account name), you don’t need to do this.

Now that the 1:1 relationship between the User Profile and the external system is set up, it’s time to figure out which profile properties you want to import.  If you refer back to Figure 1 above, you’ll see that I illustrate the importing of Title and About me properties from the external system.

For the demo here, I’ll map two existing properties, Hire Date and About me.  You do this by editing the profile property (SSP –> User profile and properties –> View profile properties –> Choose a property and edit).  There’s no reason why I can’t create new properties and map these; the process is the same.  Here is how to map the Hire date profile property to the HireDate column from the HREmployees import connection:

Figure 7: Mapping Hire Date

Figure 7 above shows the upper and lower portion of the same screen.  When choosing a column in the Data source field to map drop down list, you’ll be presented with a list of all the columns returned from the Specific Finder.  So, the rule of thumb is the ensure that you are returning all columns you want to map from your Specific Finder.  As with creating the import connection, when mapping properties this way, you must ensure the data types are compatible.  If they are not, you’ll be given an error message like this: Cannot map to this data source field. This property is of type ‘date’ and the data source field you are mapping to is of type ‘System.String’.

I have also mapped the About me property to the Notes column defined in the Specific Finder.  At this point, the mapping configuration is in place.  We’re not quite done yet, however.

Another important step is to ensure that your Default Content Access Account has permissions to update these profile properties through the BDC.  The Default Content Access Account is configured when setting up Enterprise Search (SSP –> Search settings –> Default content access account).  If you prefer, you can also set a specific account when configuring the User Profile Import.  Just go to SSP –> User profile and properties –> Default access account.  Now that you know what your access account is, you must grant this account two sets of permissions:  1) Permissions to update your profile properties and 2) permissions to call into these BDC methods.

To grant permissions to update profile properties, go to SSP –> Personalization services permissions.  On this screen, grant your access account the Manage User Profiles permission as shown here:

Figure 8: Granting Content Access Account Manage User Profiles Permissions

If you’ve worked much with profile properties, you’ve probably noticed that each property can be individually secured by adjusting its privacy setting.  The Manage User Profiles permission grants my access account (SYNERGY\MOSS.Search) write permissions to all profile properties.  Without this, you’ll need to set the privacy level of each mapped property to Everyone, which may not be the best security level.

The second permission is to grant this access account permissions to the BDC entity.  If you’ve been following our series, we covered BDC permissions in Part 2.  The minimum permission you need to grant is Execute as shown below.  You access this screen from SSP –> View Applications –> <Select ADF> –> <Select entity> –> Manage Permissions.  In this screen shot, I grant MOSS.Search execute permissions to the Employees entity:

Figure 9: Granting Access Account Entity Permissions in BDC

Finally, we are now done and ready to import.  Let’s quickly recap to make sure we have these steps down.  Again, we’re talking about a 1:1 mapping.

1.  Ensure ADF is prepared with a Specific Finder returning the columns you want to map.

2.  Create a new or choose an existing profile property that contains values to match the entity identifier.  In my example above, I used EmployeeID.

3.  Create a 1:1 import connection.

4.  Map your desired profile properties to columns returned from Specific Finder.

5.  Grant your content access account permissions

To start the import, return to the main User Profiles screen and click Start full import.  This will first do a full import from your primary import connection.  The status here is reflected next to Profile import status shown below.  Once that is done (and sometimes with a brief minute or two delay after it finishes), the secondary imports from BDC will begin.  This is reflected next to Membership & BDC import status.  Both are shown highlighted here:

Figure 10: User Profile import status

Once the profile imports complete (and it may take from several minutes to possibly an hour for thousands of users), you’ll want to check the import log to get a sense on whether it succeeded.  To do so, click the View import log link.  When reading the log, you should know that a PEOPLE_IMPORT content source refers to individual person crawls through your primary import connection, whereas PEOPLE_DL_IMPORT refers to group (i.e. distribution list) crawls.  PEOPLE_DL_IMPORT is also where your secondary import connections through BDC are logged, so it’s relevant to us in the scope of this article.  Here is a part of the log showing both content sources for one user, Andrew Fuller.

Figure 11: User Profile Import Log

Even though the logs may look fine, you’ll certainly want to verify some of the mapped values come over.  To view one, simply click on View user profiles and select a user.  In Figure 11 below, here are the two mapped properties for synergy\afuller:

Figure 12: About me and Hire date values pulled in by profile import

Since I mapped the About me property, you can also see this within the user’s My Profile found in their My site.  Here is Andrew Fuller’s:

Figure 13: Andrew Fuller’s My Profile page

Now that you have a solid overview of 1:1 mappings, let’s turn our attention to 1:many.

1:Many Import Connection Type

While less common than 1:1 connections, a 1:many is handy in a few situations.  Let me give you one example.  As mentioned in the overview section above, the IT group typically manages basic user attributes in AD.  In all my years of consulting, I’ve encountered very few organizations that actually keep these attributes up to date.  The problem is that IT is usually busy handing a myriad of critical problems, and they don’t usually bother updating less important attributes such as someone’s manager.  As long as the permissions are right, that’s what is important to them.

This can cause problems, however.  One of the useful SharePoint profile properties is Manager which gives SharePoint knowledge of the organizational hierarchy.  This was shown in Figure 13 above when looking at Andrew Fuller’s My Profile page.  By default, this property is mapped to the manager setting for the user in AD.  However, if this value is not set or out of date, SharePoint won’t have an accurate picture of the organization.

To correct this problem, we will pull the manager from a Departments table that is stored in our external HR system.  Here is where the 1:many comes in.  Recognize that many users will have the same department.  The idea is that we want to use the department profile property in SharePoint, which is correctly set, and use that to look up into the Departments table (in the external system) to pull the manager for that department.  So, you can think of the 1:many to be for each department there will be many user profiles with that value.

Let me show you how I configure the mapping to pull the Manager property from our HR System.  I start off by adding another Import connection.  Yes, you can have multiple import connections coming from BDC.  I call this one HREmployeesGetManager and here is how it is configured:

Figure 14: Creating a 1:many import connection

When choosing the 1:many mapping radio button, you have two drop downs to fill in.  You first need to select the filter.  The choices you have will be the filter descriptors that have been made available for the entity’s Finder method.  Hence, to use 1:many mapping, you must have a finder method and at least one filter descriptor.  (Note: if you cannot select 1:many mapping, it is probably because you don’t have filter descriptors defined in your ADF.)

For my demo, here is the Finder method and filter descriptor for the Departments entity:

Figure 15: Finder method for Departments entity

In the SQL select query, you can see that I am selecting the DepartmentID, DepartmentName and Manager columns based on a certain department name.  The next step is to change the default mapping for the Managers property.  Here is how I have changed it:

Figure 16: New mapping for Managers profile property

For the Data source field to map, I have pointed this to the Manager column.  Other column choices are the other columns returned from the Finder method.  For the last text box (Manager field string format in AD), you can leave this unchanged.

That’s it.  Run another import, and you should now have your manager property coming from your external HR system.  For this to work, you will need to ensure your Department property is populated for your user profiles.  In my case, this value was still coming from AD.  That’s right, you can use a value that is imported from AD and pass that as a parameter to your BDC connection.  Pretty cool.

Let me make one last point on 1:Many import connections:  Be careful if your Finder method returns more than one record, and your mapped property does not allow multi-values.  For example, perhaps the HR system had two department records with the same name.  And for each record, a different manager is set.  When the import runs and it tries to pull the manager based on the name, two records will be returned. When this happens, the import process will take the first record it finds, and this may not be the one you want.  If you are using a multi-valued property, however, it works nicely and will store the values for all records that are found.

Troubleshooting Tips

When troubleshooting import problems, you’ll definitely want to go to the SharePoint logs (by default C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\LOGS).

When mapping, a profile property can be mapped to only one source (primary or secondary).  Similarly, a column from the Specific Finder (1:1 connection) or Finder (1:many connection), can only be mapped once.  For example, if you map the HireDate column to one profile property, you cannot also map it to another.

Remember that you can only map compatible data types.  Fortunately, you will get this error when you try to save your profile property.

When mapping strings, be careful about the column length.  If your profile property only supports 25 characters, and you try to store 50 characters that is the returned value from BDC, you will get an import error for that profile. You would only know this is a problem by seeing this error in the SharePoint logs.  (Note: you cannot change the length of any of the out-of-the-box profile properties.  In this case, you would need to create a new property.)

When creating BDC import connections using the UI, the entity selected from the catalog must expose both Finder and Specific Finder methods.  This is necessary, even if you plan on using only 1:1 or 1:Many connection types.

Conclusion

In this article, we covered everything you need to know about importing User Profiles from BDC.  And you now know as much about this as I do.

Coming up in Article 7, we will cover the BDC API.  This will allow you to call into the BDC programmatically from your custom applications, web parts, or any other .NET code you need to write.