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:
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'
|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||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:
- Run the query for SPS-Skills in something like SQL Manager
- Highlight the data in SQL Manager results table and copy and paste it into a new Excel Workbook
- Now highlight the data in Excel (I use Excel 2013), click the INSERT tab and click PivotTable
- If you’ve done everything right so far, a dialog box will pop up looking like this:
- Click the OK button to add the PivotTable into a new worksheet.
- 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:
- 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.