So recently on a LinkedIn discussion it was asked if it was possible to build an out of the box Skills Matrix from the information stored in the Skills field of the SharePoint User Profiles.  Having written about how to create a SQL View to get User Profile Property Bag Values in a previous post, I thought this would be another interesting exercise on a real world use for these views.

Setting up the view is pretty simple, here is the structure we’ll use (See original post for how to set everything up):

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value, 
                      upv.SecondaryVal AS SecondaryValue
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

Quick note, in my original posting last year, I only looked for value.  However, with certain SharePoint fields where keywords are used, Value ends up being a GUID and what we’re really interested in is the SecondaryValue field which has human readable content, or as I like to call them actual words.

Once you have your view, running it will produce some nice results:

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 UserProfile_GUID 1A89D7FA-BLAH-BLAH-BLAH-1B5C0A3642DD NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SID 0x010500000000000BLAHBLABLAHBLA NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 AccountName Domain\Admin NULL
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 FirstName Admin NULL

 

Looking at the Property field, we can find the specific property we want (You can also go into Central Admin and manage User Profile Properties to see what the field names are called, but a lot of them will be self-evident by either their name or the values they contain) which in this case would be the “SPS-Skills” property, so a quick query of our PropertyBag view for just this Property will give us a great list of all the profiles with their associated skills:

SELECT *
  FROM [WSS_Content_Views].[dbo].[vw_UserProfilePropertyBag]
  where Property = 'SPS-Skills'

Login

Name
Email
Modified
Property

Value

SecondaryValue
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills D272CB6F-9286-4DFA-A62A-48AA4F189B3B Mad skills
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 26F8647A-DE5C-48A1-A923-8E791093E23C C#
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 6B8B9757-F7D8-4C8C-908B-873956DCD78C SharePoint
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SPS-Skills 86519AFC-3643-4D49-A77C-BBBE373EAFFC Social Media

So now that we have our raw data we can do a couple different things with it that aren’t possible with out of the box SharePoint.  Obviously we can query for a specific name to give us a list of their skills, but we can do that already in SharePoint by just looking at their user profile page.  However, we can also query for a specific SecondaryValue, and get a list of all the people who have that skill in their profile.  If we want to take this a step further and create a skills matrix of all the users in the company, just follow these steps:

  1. Run the query for SPS-Skills in something like SQL Manager
  2. Highlight the data in SQL Manager results table and copy and paste it into a new Excel Workbook
  3. Now highlight the data in Excel (I use Excel 2013), click the INSERT tab and click PivotTable
  4. image
  5. If you’ve done everything right so far, a dialog box will pop up looking like this:
  6. image
  7. Click the OK button to add the PivotTable into a new worksheet.
  8. From here, you can set up a basic PivotTable to show you a Users/Skills matrix by selecting the fields and dropping them into the correct Rows/Columns/Values regions:
  9. image
  10. And now you have what could be a very large User/Skills matrix.

That’s just the basics, obviously there’s lots of room to improve the functionality and usability.  You can for instance link the data as a query an external SQL data source.  You can include additional properties so you can further filter the PivotTable by department or office location.  You could event publish the Excel spreadsheet to SharePoint and use Excel services and web parts to expose the data to the end users on their own SharePoint sites.  So with a little creativity and a couple of views you can create some very slick “no-code” applications that rely on data stored in the User Profiles.