Note: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services Reference: Article

User Profile Application is a service application that can be consumed across multiple sites and farms. We can configure the service based on our requirement and it stores information about users in a central location. Social computing features use this information to facilitate productive interactions which enable users to collaborate efficiently. In order to provision My Sites, enable social computing features such as social tagging and newsfeeds, you must enable the User Profile service.

• User profiles – A user profile organizes and displays all of the properties related to each user together with social tags, documents and other items related to that user.
• Organization profiles – Contain detailed information about an organization such as teams, divisions, and so on.
• Profile synchronization – Provides a reliable way to synchronize user, group, and organization profile information that is stored in the SharePoint Server 2010 profile store with profile information that is stored in directory services across the enterprise.
• Audiences – Enables organizations to target content to users based on their job or task, as defined by their membership in a SharePoint Server group or distribution list, by the organizational reporting structure, or by the public properties in their user profiles.
• My Site Host – A dedicated site for hosting My Site Web sites. A My Site Host is needed in order to deploy the social features of SharePoint Server.
• My Site Web site – A personal site that gives users in your organization a central location to manage and store documents, links, and colleagues.
• Social tags and notes – Enables users to add social tags to documents, to other SharePoint Server items, and to other items, such as external Web pages and blog posts. Users can also leave impromptu notes on profile pages of a My Site Web site or any SharePoint Server page.

We get more information about configuring User Profile Service and User Profile Application from

MSDN Blogs > SharePoint Escalation Team http://blogs.msdn.com/b/spses/

Data Source:

Data source is the place where SharePoint can connect thru user profile service (connection strings) and import user profile information. It could be a business applications or directory services.

SharePoint supports many types of connection like …

Active Directory, Active Directory Logon Data, Active Directory Resource, Business Data Connectivity, IBM Tivoli Directory Server (ITDS), Novell eDirectory, Sun Java System Directory Server

We can connect to either of the above data source and import user profile information by passing all the required data like authentication type and credentials.

Data Import:

Importing data from data source thru configured connections

Configure Synchronization Connections: Create connections to connect data sources with authentication type and credentials.

Configure Synchronization Timer Job: This timer job will run at the specified interval to synchronize user, group and group membership changes between the user profile application and specified data source.

Configure Synchronization Settings: To manage the settings for profile synchronization of users and groups.

Start Profile Synchronization: Either you can start Full or Incremental synchronization

Manage User Profiles:

Once user profiles imported from AD, it get stored in [Profile DB]; the following query will help you to get the list of property value of a particular user.

USE [Profile DB]

SELECT upf.NTName, pl.PropertyName, upv.PropertyVal FROM UserProfile_Full upf WITH(NOLOCK)

JOIN userProfileValue upv ON upv.RecordID = upf.RecordID

JOIN PropertyList pl ON pl.PropertyID = upv.PropertyID

WHERE upf.NTName = 'Domain\alias'

select * from UserProfile_Full

select * from UserProfileValue

select * from PropertyList

We can create new property thru “Manage user properties”

We can get more information about user profile service application, connection string and user profile import process from MSDN Blogs > SharePoint Escalation Team http://blogs.msdn.com/b/spses/

Timer Jobs:

User Profile Change Job:

Whenever there is a change in user profile, either thru Central Administration->User Profile Application->Manage User Profiles or AD import there will be a changelog entry added into UserProfileEventLog table. Newly added entries will be processed thru this timer job. The “User Profile Change Job” has been scheduled for every hour.

User Profile Change Cleanup Job:

This job has been scheduled for Daily; it will clean up 14 days old data from UserProfileEventLog table.

User Profile Incremental Synchronization:

This job is to synchronize the new and updated Users, groups and group membership from AD, incremental sync will synchronize only the newly created or updated data.

User Profile to SharePoint Full Synchronization:

This job is to synchronize the user information from UPA to SharePoint sites, scheduled hourly by default.

User Profile to SharePoint Quick Synchronization:

This job is to synchronize the user information from UPA to SharePoint sites, it scheduled for every 5 minutes, it updates ProfileSweep table change token.

Social Structure in Profile DB:

The Profile database has been expanded with few Social tables. The majority of functionality around ‘Social Feedback’ – a pillar of Social Computing, along with User Content, and Social Networking – is stored and processed in the Profile database.

Social Feedback in SharePoint is accomplished through:

· Tagging

· Note Board

· Rating

· Bookmarking

Of these, only the Ratings are synchronized with the Content Database via timer job. The User Profile Service Application is heavily linked to feedback mechanisms in SharePoint Server 2010.

For more details and an explanation with a diagram please refer to BLOG

Sub-types (User profile)

The user profile properties are used to display user information and the relationships of each user to other users in an organization.

Profile subtypes can be used to create a different set of properties for a different set of users. For example, you can create a subtype that categorizes a user as either an intern or a full-time employee.

 

Property Name:

We can create new property name to store additional values of user profile information, for example we can create a new property name ‘Age’ and store all users age.

System Property: (We cannot delete, edit and change mapping to sub-types, by default mapped to all sub-types)

SID

Account Name

First Name

Last Name

Custom Property: (We can edit and delete, we can change mapping to different sub-types)

Home phone

Mobile Phone

Fax

Below query will return all the property names and it will show whether it is System Property or Custom Property.

SELECT pl.propertyname, pa.IsSystem as SystemProperty from PropertyList pl

join ProfiletypePropertyAttributes pa ON pa.PropertyID=pl.PropertyID

order by pa.issystem

Section Name:

Sections are to categorize property names; it helps to manage property names.

· We can create new sections to organize property names.

· There are 6 sections by default available.

Basic Information Contact Information

Details Delegation

Newsfeed Settings Custom Properties

· We can map sections also with sub-types.

Sub-Types:

· By default there is a Sub-type named “Default User Profile Subtype”

· Every user profile must map with a sub-type, by-default it will be mapped with ‘Default User Profile Subtype’, we can create new sub-type and map user profiles.

· Same user profile cannot map to multiple sub-type.

Create sub-types:

UPA -> Manage User Properties -> Manage Sub-types -> Create (Intern)

Map User profile to sub-types:

CA -> Manage User Profiles -> Edit My profile -> Sub-type

Change sub-type from drop down and fill new fields appear from the sub-type

Save and close.

Tables and Structure

select * from dbo.ProfileSubtypeList

select * from dbo.PropertyList

select * from dbo.ProfileSubtypePropertyAttributes

select * from dbo.ProfileTypePropertyAttributes

 

Here is the query which will help you to get a particular user’s profile Values mapped to Property Names and sub-types.

USE [Profile DB]

SELECT upf.NTName, pl.PropertyName, upv.PropertyVal, pst.ProfileDisplayName, pa.IsSystem FROM UserProfile_Full upf WITH(NOLOCK)

JOIN userProfileValue upv ON upv.RecordID = upf.RecordID

JOIN PropertyList pl ON pl.PropertyID = upv.PropertyID

JOIN ProfileSubtypeList pst ON upf.ProfileSubtypeID=pst.ProfileSubtypeID

JOIN ProfiletypePropertyAttributes pa ON pa.PropertyID=pl.PropertyID

WHERE upf.NTName = 'four\eight' or upf.NTName='four\selvagan'

order by upf.NTName