Social Computing: Part 3 : Activity Feeds, Social Ratings, Tags and Notes.

Article
02/05/2011

Activity Feeds

Activity Feeds are an ongoing record of a user’s activities through the day (or for whatever time period in which they choose to update the feed). There are two sections of Activity feed.

· My Activities - My Profile > Recent Activities

· Consolidated - My Newsfeed (Activities which track)

These two sections are like two sides of a conversation. Both are depending on Activity Feed timer job.

How do I know what activities I am tracking?

The above section of activities is extensible through object model and hence you can expect more items to appear under this list of activities one can follow. The modification to each user activities is stored under ActivityPreference in the profile database.

SELECT * FROM [dbo].[ActivityPreference]

Interestingly, this table acts as filter and you will not have any entries in this table for a specific user if he has all the activities selected from the above list. On when user make changes to this default setting we make entries to ActivityPreference table.

We use the following procedure call to delete the records from ActivityPreferences table when you include any activity for tracking which was removed earlier.

exec dbo.activityFeed_DeleteIfExistsActivityPreference @partitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',@EntityId=4,@ActivityTypeId=17,@CorrelationId='10D6205D-AC9A-4B0A-B932-71CA44674E87'

The global list of activities and types we have is stored under ActivityTemplate table.

Select *from dbo.ActivityTemplate

The Newsfeed and Recent Activities are driven by the Activity Feed timer job. This is an Hourly job responsible for updating any of the activities seen in the ‘Activities I am following’ list. There is also a daily Cleanup job called Activity Feed Cleanup Job which is responsible for clearing the expired event related to Activit from ActivityEventsConsolidated and ActivityEventsPublished tables. We will see more about these in the following sections.

Let’s have a closer look at how we track each activity which is selected to follow by a user and how we display these changes

Tracking Tables

When user is trying to update his own profile page, the profile data is getting updated.

Profile: Edit profile, change picture, update office location, update phone number …

When user is trying to add colleagues, tags and notes, the social data is getting updated.

Social: Adding Colleagues, tags and notes …

Updating profile data

[Profile DB]

select * from UserProfileEventLog - (for tracking)

select * from UserProfile_Full - (update)

Adding new post in Notes

[Social DB]

select * from socialcomments_changelog - (for traking)

select * from SocialComments - (update)

Adding new tags

[Social DB]

select * from SocialTags_ChangeLog - (for tracking)

select * from SocialTags - (update)

Calculated Ratings

[Social DB]

select * from SocialRatings_ChangeLog – (for tracking)

select * from SocialRatings – (update)

Timer Jobs

Activity Feed Job – hourly

Activity Feed Job reads all the above *.*Log tables and update the below tables based on the activity type. Both tables available in [Profile DB]

select * from dbo.ActivityEventsConsolidated (social)

select * from dbo.ActivityEventsPublished (profile)

Activity Feed Cleanup Job – Daily

Activity Feed Cleanup Job clears all the activities which are older than 14 days. This job will not clear the UserProfile change log.

How it Works

Step 1: Enable Activity Feed Job. By default Acticity Feed Job runs hourly and you can change the schedule based on how often you want to consolidate the changes.

Step 2 :Activity Feed Job runs the below procudures to pick up the changes from respective tables and update the ActivityEventsPublished table or ActivityEventsConsolidated with in the profile database. Its important to understand ActivityEventsPublished will have a combination of both profile related(Adding a colleauge) and social related (Adding Tags and Notes). However, we only make entries to ActivityEventsConsolidated based conditions like if there is colleauge added to the user who made the changes.

Activity Feed Job make use of the following procedures to populate entries in ActivityEventsPublished table and ActivityEventsConsolidated.

Procedure 1- dbo.activityFeed_InsertActivityEventsPublished :

exec dbo.activityFeed_InsertActivityEventsPublished @partitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',@EntityTypeId=N'user',@EntityId=4,@ActivityTypeId=6,@CreationTime='2010-09-26 11:26:31.543',@LastUpdateTime='2010-09-26 11:15:29.060',@TemplateVariable=N'[XML]',@IsRolledUp=0,@ItemPrivacy=1,@CorrelationId='00000000-0000-0000-0000-000000000000'

[XML]

<AccountName>mk\sojeshs</AccountName>

<Email>mk\sojeshs</Email>

<Name>sojesh sreelayam</Name>

<Href>https://o14singleserver:80/my/person.aspx?accountname=mk%5Csojeshs</Href>

<Picture>https://o14singleserver:80/my/User%20Photos/Profile%20Pictures/mk_sojeshs_MThumb.jpg</Picture>

</Owner>

<AccountName>mk\sojeshs</AccountName>

<Email>mk\sojeshs</Email>

<Name>sojesh sreelayam</Name>

<Href>https://o14singleserver:80/my/person.aspx?accountname=mk%5Csojeshs</Href>

<Picture>https://o14singleserver:80/my/User%20Photos/Profile%20Pictures/mk_sojeshs_MThumb.jpg</Picture>

</Publisher>

<Name xmlns="">mk\Administrator</Name>

<Name>mk\Administrator</Name>

<Href>https://o14singleserver:80/my/Person.aspx?accountname=mk%5CAdministrator</Href>

</Link>

</ActivityTemplateVariable>

Procedure2- exec dbo.activityFeed_InsertActivityEventsConsolidated:

exec dbo.activityFeed_InsertActivityEventsConsolidated @partitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',@EntityTypeId=N'user',@EntityId=4,@ActivityTypeId=13,@CreationTime='2010-09-26 14:22:02.210',@LastUpdateTime='2010-09-26 14:21:08.207',@TemplateVariable=N' [XML] ',@IsRolledUp=0,@CorrelationId='00000000-0000-0000-0000-000000000000'

[XML]

<?xml version="1.0" ?>

<AccountName>mk\sojeshs</AccountName>

<Email>mk\sojeshs</Email>

<Name>sojesh sreelayam</Name>

<Href>https://o14singleserver:80/my/person.aspx?accountname=mk%5Csojeshs</Href>

<Picture>https://o14singleserver:80/my/User%20Photos/Profile%20Pictures/mk_sojeshs_MThumb.jpg</Picture>

</Owner>

<AccountName>mk\Administrator</AccountName>

<Email>mk\Administrator</Email>

<Name>mk\Administrator</Name>

<Href>https://o14singleserver:80/my/person.aspx?accountname=mk%5CAdministrator</Href>

</Publisher>

<Value xmlns="">This is awsome</Value>

<Name>Bears.jpg</Name>

<Href>https://o14singleserver:80/Shared%20Documents/Bears.jpg</Href>

</Link>

</ActivityTemplateVariable>

Now we have the data consolidated in those two tables, here is how we get it back in Recent Activities and News Feed. Remember, you need a crawl to get the data updated in the news feed section since this is security trimmed.

ULS View

For an example where User 1, whom we call Selva adds User2 whom we call Sojesh as a colleague (i.e. Sojesh has not yet added Selva as a colleague)

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Foundation Timer 8e45 Verbose Begin invoke timer job UPAONE - Activity Feed Job , id {7F5301AE-020D-48F4-81CD-991007B185EC}, DB n/a f34fc1e1-8cea-48cf-a5ad-6786d15f5479

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Server Database tzku Verbose ConnectionString: 'Data Source=O14SingleServer;Initial Catalog="Profile DB";Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15' ConnectionState: Closed ConnectionTimeout: 15 3c069380-5291-4507-9e9a-ee164e8c372c

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Server Database tzku Verbose ConnectionString: 'Data Source=O14SingleServer;Initial Catalog="Social DB";Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15' ConnectionState: Closed ConnectionTimeout: 15 3c069380-5291-4507-9e9a-ee164e8c372c

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Portal Server User Profiles e88s Medium ActivityGathererProfile.GenerateActivityEvents: Published 1, Consolidated 0 3c069380-5291-4507-9e9a-ee164e8c372c

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Foundation Timer 8e46 Verbose End invoke timer job UPAONE - Activity Feed Job , id {7F5301AE-020D-48F4-81CD-991007B185EC}, DB n/a f34fc1e1-8cea-48cf-a5ad-6786d15f5479

Sojesh now add Selva as colleauge (i.e. Selva has already added Sojesh as a colleague)

— OWSTIMER.EXE (0x4F0C) 0x4C20 SharePoint Portal Server User Profiles e88s Medium ActivityGathererProfile.GenerateActivityEvents: Published 1, Consolidated 2 3c069380-5291-4507-9e9a-ee164e8c372c

Recent Activities - My Site owner’s activities like his own profile updates and social activities will be displayed.

Data from the ActivityEventsPublsihed will be displayed as User’s Recent Activities

exec dbo.activityFeed_GetActivityEventsPublished @viewerPartitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',

@viewerEntityId=19,

@publisherPartitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',

@publisherEntityId=19,

@viewerRights=1,

@useStoredPreferences=1,

@minEventTime='2010-09-11 12:13:54.4630000',

@batchSize=10,

@CorrelationId='56647E2E-7A1E-4225-A276-20C587D7006D'

https://<mysite host>/_layouts/activityfeed.aspx?publisher=<useraccount>

https://<mysite host>/person.aspx

My Newsfeed - Has incoming activities like adding colleagues, tags and notes …

Data from the ActivityEventsConsolidated will be displayed as Newsfeed; the entries will be filtered based on interests defined in “Newsfeed Settings”

exec dbo.activityFeed_GetActivityEventsConsolidated @partitionId='0C37852B-34D0-418E-91C6-2AC25AF4BE5B',

@EntityId=19,@useStoredPreferences=1,

@minEventTime='2010-09-11 12:16:47.0100000',

@batchSize=40,

@CorrelationId='C79E5E58-1496-4EC3-9355-706D4557C261'

https://<mysite host>/_layouts/activityfeed.aspx?consolidated=true

https://<mysite host>/default.aspx

Ratings allow users to give feedback which is eventually recorded via a timer job pushing the average rating score across users to the Content database (AllUserData table).

We can enable this feature for any list or library.

Unlike other forms of Social Feedback – Tagging, Note Board, and Bookmarks – only the Ratings are pushed to the Content Database. The remainder of the mechanisms store data in tables in the Profile DB.

Database and Tables

[Social DB]

dbo.SocialRatings

dbo.SocialRatings_Averages

dbo.SocialRatings_ChangeLog

When there is a new rating on an item, there will be new entry in dbo.SocialRatings and dbo.SocialRatings_ChangeLog for tracking.

Timer Jobs

Social Data Maintenance Job reads dbo.SocialRatings table, calculate average value and stores it in dbo.SocialRatings_Averages table.

Activity Feed Job reads the dbo.SocialRatings_Averages table and updates the dbo.ActivityEventsConsolidated table, which will display the rating information in ‘My Newsfeed’ section for tracking colleagues.

The SocialRatings Synchronization Job reads the dbo.SocialRatings_Averages table, the values will be stored to the Content database - > AllUserData table -> Float1 column

Tags and Notes:

Usage: Social tagging helps users to categorize information in ways that are meaningful to them. It can also connect individuals who want to share information with other users who have like interests.

Social tagging allows users to either select existing tags, or create their own tags (taxonomy and folksonomy).

There are two types of tags we can add

Public

Private

Private tags are not visible to other users; if you do not explicitly mark the tag as private then it is a public tag.

Tag Profiles:

Each tag has its own profile page, where users can choose to follow the tags or add into My Interests.

Any objects in SharePoint 2010 can be tagged

SocialTag Cloud:

Is a webpart where you can see your public and private tags, if you browse to other user’s profile page, you get to see only the public tags of that user. By default it is available in mysite webpage. It gives aggregated view of tags.

Social tags will get stored in multiple tables for different views in “Tag Cloud”

· SocialTags table = For the Current User

· SocialTagCloud_Everyone table = For All Users

· SocialTagCloud_EveryoneByURLBase table = Under the Current URL for All Users

Timer Jobs:

Social Data Maintenance Job – Aggregates the social tags and rating and cleans up the Social data change logs for every hour.

Stored Procedure:

exec dbo.proc_Admin_CompactChangeLogs @partitionID='0C37852B-34D0-418E-91C6-2AC25AF4BE5B'

This SP clears the following tables

SocialTags_ChangeLog

SocialComments_ChangeLog

SocialRatings_ChangeLog

The Social DB contains both Tags and Note Board (Comments) information. The tables are:

SocialComments

SocialComments_ChangeLog

SocialTags

SocialTag_ChangeLog

SocialTagCloud_Everyone

Updated by the Social Data Maintenance Timer Job.

SocialTagCloud_EveryoneByURLBase

Updated by the Social Data Maintenance Timer Job.

Tag Security (Activities for)

When User 1 tries to view User 2’s profile page and his Tags and Notes section, the tag items will be security trimmed by search catalog ACL information.

While SharePoint search crawls any SharePoint site or URL, it crawls available ACL data as well, when user adds tag for an internal SharePoint link, it will get displayed only to the users who has access to the link.

If SharePoint search does not have any ACL information about a tag, then it won’t be displayed to other users. For example, if there is a public link https://www.bing.com added as tag, but SharePoint is not crawling https://www.bing.com, so there is no ACL information for this content. Though the site is publically available for everyone, this tag won’t be displayed to other users, it will get trimmed.

Whether internal or external links added as tag, we need to have search service application to view other users tag under “Activities for” section.

Own profile page:

Internal links public tag - yes

External links public tag - yes

Internal links private tag - yes

External links private tag – yes

Others profile page:

Internal links public tag – yes (Provided SSA crawl the contents of the link)

External links public tag – No (It is visible only if the link is crawled and we have ACL information)

Internal links private tag - No

External links private tag – No

Term Store

It stores all key words and phrases used in web pages and tags; it does not maintain any user information regarding ownership. This is implemented as part of manage Meta data service.

Troubleshooting tips:

Q: Intermittently if some of the tags are not getting displayed when User 1 is trying to browse User 2’s profile page and his added tags.

A: This may happen if there is any security change and there is no crawl happened, make sure you have scheduled crawling, after a full or incremental crawl the link will get updated with ACL information, so that it will be available for other users.

Q: Intermittently tagged internal links are not getting displayed for other users

A: Make sure you crawl the following link thru content source, run a Full crawl and schedule incremental crawl in future.

· https://rootsite

· https://mysite

· Sps3://mysite

Q: Tagged external links are not getting displayed for other users

A: Until you have crawled the external url with security, it won’t be available for others. SharePoint doesn’t categorize whether the tagged url is internal or external. All tags are an item with an associated url.

Social Computing: Part 3 : Activity Feeds, Social Ratings, Tags and Notes.

Activity Feeds

Tracking Tables

Timer Jobs

How it Works

Social Ratings

Database and Tables

Timer Jobs

Tags and Notes:

Tag Profiles:

SocialTag Cloud:

Timer Jobs:

Tag Security (Activities for)

Term Store

Troubleshooting tips:

Additional resources