BDC Connection and Mappings for Profile Import
Recently I worked with a customer in which we have worked with them on a 1:MANY mapping for a BDC profile to map the columns from data base to a Multi value BDC profile property. I have given them a brief explanation about BDC profile import and their connections. It might be useful for your self-development on BDC profile import if you are interested.
Importing User Profiles : Master Connection and Secondary Connection
Microsoft Office SharePoint Server 2007 can import a list of domain users from the Active Directory directory service, LDAP server, or the Business Data Catalog. In addition, you can write code against the Office SharePoint Server 2007 object model to import information from other directory services or applications. You can schedule regular imports to the user profile store, and these can be incremental or full. Office SharePoint Server 2007 treats Active Directory and LDAP directories as master connections for importing user information; that is, it can use them as a source to create user profiles. This implies that if a user is missing from the master connection, Office SharePoint Server 2007 assumes the user is no longer in the organization and removes the user from the user profiles database.
On the other hand, Office SharePoint Server 2007 treats the Business Data Catalog data sources only as a supplementary data source, meaning that it uses the data only to provide additional user information not available in the master connection. It does not rely on the Business Data Catalog data sources for the master user list.
So whenever you start a Full/Incremental Import, MOSS first checks the MASTER CONNECTION and imports the user profile directory service to SSP. Then checks for any secondary connection (BDC Connection) and then imports the additional information from supplementary data source like external database or web service. Now with this introduction we can understand that BDC import connection can be only treated as secondary connection and will import the additional data from external data sources to SSP. So it’s clear from the description that BDC is just going to bring some data related to the user profile from external data source. So after the successful fetching of data from external data source BDC connection should be aware of mapping the data to a property of respective profiles. BDC connection uses two different mapping methods to import the additional information from external data source to SSP user profile. They are :
1. 1:1 Mapping
2. 1:Many Mapping.
1:1 Mapping
================
So far we understand that when Full/Incremental import start MOSS is going to execute the Master connection and will import the user profiles to SSP and then MOSS will execute BDC connection which will fetch the additional data from external data source and going to map the data with the user profile (Which has been already existing in SSP now as the master connection has been already executed)exists in the SSP. Now with this understanding, we can easily ask a question to ourselves that on what basis BDC connection is going to fetch the data from external data source and going to map it to the user profile property in SSP… and the answer is that when BDC connection is executed it will send a select query to the external data source to fetch a single Row based on a primary key and the primary key value will be one of the property of user profile which will be sent from SSP (This has been implemented by “Specific Finder” method instance of the respective entity in the application definition file. This is the reason behind having the “Specific Finder” method instance for 1:1 mapping). We need to map the required column of the row fetched from external data source to another user profile property. Thus the user profile property in SSP will be populated with additional data from external data source through BDC connection.
1. First step is to create a BDC import connection of type 1:1 mapping. So here, 1:1 mapping means ONE of the user profile property in SSP will be mapped with a column of external data source. While creating the BDC connection along with 1:1 mapping you need to select the “Property” which will be sending the unique identifier value to external data source from SSP to fetch the single record. When the BDC connection is executed, the user profile property of profiles in the SSP are going to get populated with the column coming from external data source. (In the example shown below “Emp Id” is the property value send to external data source from SSP)
2. As the BDC connection is going to fetch data from external data source the user profile in SSP should already have a property in which the data coming from external data source will be populated. So first, you need to create a user profile property of type to exactly match with the return type which BDC connection is going to fetch from external data source. While creating the user profile property you need to select the BDC connection which will provide you the detail of the returned columns. You need to select a column which you would like to map with the user profile property. (In the below sample, we have a custom property called “EmpName” created and it has been mapped to the column “EmpName” in the external data source through the BDC connection created in the step 1)
3. With all the above steps done, when the BDC connection is executed during the Full/Incremental Import SSP sends the profile property value to SSP (it’s done by “Specific Finder” method instance in the application definition file through a “Select” command with a “Where” clause) and fetches a single record from external data source and based on the mapping done for the custom profile property it populates the column. BDC connection executes this “Specific Finder” method instance for every user profile in SSP and thus populates ONE property of SSP with ONE column value fetched from external data source.
1: Many Mapping
===================
Now again with the understanding of BDC connection execution, If the requirement is to fetch MORE THAN ONE ROW from external data source and to map a specific column of those ROWS to a SINGLE user profile property in SSP then we need to stick with 1: MANY mapping. Where 1:MANY mapping means that fetching MANY (multiple) records from external data source and map it with ONE property of user profile in SSP. When I say multiple records it doesn’t mean that multiple column from external data source, It’s the same column of external data source from multiple records . For example, In your requirement, You have a multiple rows for a single user in external data source and you would like to map a column values of those rows to a single user profile property in SSP. So for these kind of multi value mapping requirements we need to implement 1:MANY mapping. 1:MANY mapping will be implemented through “Finder” method instance which just sends the “Select” query and fetches every records from the external datasource. So in 1:MANY mapping case, SSP is not going to send any value to external data source as we need to fetch every records from external data source. Once every record from external data source are fetched and reaches SSP, now it’s the turn of SSP to apply a filter a based on a column existing in the return records. This will be implemented through the “Filter Descriptor” of method instance which will filter the records based on the column value specified in its attributes. The “Filter Descriptor” cannot implement the filtering based on the column of type “INTEGER”, so if your column in the external data source which is referred for the filtering is of “Integer” type then an exception occurs and you can find a clear entry in ULS logs with verbose enabled. So the mandatory thing here is that if you are going to implement 1:MANY mapping for multi value mapping then you should your external data source such as the column based on which you are going to implement the filter descriptor should not be an “INTEGER” type. In the same way, The application definition file should also have the precise “Type Descriptor”. So if you design the external datasource as mentioned above then the BDC editor tool will take care of the precise “Type Descriptor” entry in the application definition file. (When you select the “Finder” method instance in BDC editor tool, the intelligence of BDC editor tool knows that “Finder” method instance is going to fetch more than one records (All the records from table) so the type descriptor should be “DataReader” )
1. First step is to create a BDC import connection of type 1:MANY mapping. So here, 1:MANY mapping means that fetching MANY (multiple) records from external data source and map it with ONE property of user profile in SSP. While creating the BDC connection along with 1:MANY mapping you need to select the “Filer Descriptor” based on which the filtering will be implemented in SSP. In 1:MANY mapping, SSP receives all the records and applies the filer descriptor which has been selected during the creation of 1:MANY mapping and maps the columns values of the filtered records to the custom user profile property.
2. As the BDC connection is going to fetch data from external data source the user profile in SSP should already have a property in which the data coming from external data source will be populated. So first, you need to create a MULTI VALUE user profile property of type to exactly match with the return type which BDC connection is going to fetch from external data source. While creating the user profile property you need to select the BDC connection which will provide you the detail of the returned columns. You need to select a column which you would like to map with the user profile property.
3. With all the above steps done, when the BDC connection is executed during the Full/Incremental Import SSP fetches every record from external data source using “Finder” method instance in the application definition file through a “Select” command. Once all the records are fetched then the “Filter Descriptor” is applied to the records based on the column specified (Gentle remainder again, The column shouldn’t be a type of Integer) in the filter descriptor definition. The mapped column value of filtered records will be populated to the custom MULTI value user profile property.