Thursday, December 3, 2009

SharePoint KPI Best Practices

So, I'm hoping people will see this in google and give me some ideas. I'm trying to figure out some best practices for KPIs in MOSS (SharePoint) 2007. Thus far, it seems best to explain to those new to the topic why I'm looking for this.

A KPI stands for Key Performance Indicator, which basically means this is a report-at-a-glance of something specific in SharePoint. The more you track in SharePoint, the more you can generate these quick pictures that highlight (high-level) how areas are performing and give management a way to analyze the health of their departments, teams, divisions, etc. An example of this would be a task list:

1. You have a task list in SharePoint that shows every task that your team is working on right now and in the past.
2. You are given the responsibility of figuring out how many tasks are over a week behind schedule for every team member. Now, you could do this by sticking a bunch of the data in Excel and then using filtering and conditional formatting to get your data...but that data would be flat/static and you'd have to do a little extra work to make it dynamic...even then, it's not dynamic without a refresh. So, you want to make a KPI.
3. To make a KPI, you use the Site Actions->Create menu and create a KPI list (this is only available in MOSS 2007 and with the "Office SharePoint Server Enterprise Site features" is activated on your site). Form there, you can create a KPI based off of a SharePoint list and then set it to the URL of a view and then either count the number of items or a percentage of items exist there (even add additional filters for percentages if there's a number column involved). You then set whether a higher or lower number is better and what numbers change from an unsatisfactory to "ok" to satisfactory. This KPI list can then be opened directly or else placed on a web-part page in a dashboard style.

This is a common scenario and you may need to report quite a bit of information off of the exact same data. So, let's look at some performance best practices for KPIs:

1. SharePoint KPIs are based off of list views which means that the KPI will only be as good as your view. Views can be optimized by remembering the 3 F's: Folders, file indexes, and filters. Create folders to separate data (but don't let everyone create folders, make them yourself). File indexes involve taking a specific/key column in your list and having SharePoint make a copy of just that column so that it can reference it when searching the list (in views and, by extension, KPIs). You can turn on an index for a column by going to the List Settings and looking under the Column Section for "Indexed Columns"...just remember not to index more than 1 or 2 columns. Finally, filters will help your views keep from retrieving too much data at once (which would then be interpreted by KPIs). Use filters FIRST on indexed columns and THEN on non-indexed columns. Make sure that no view, personal or public, would retrieve more than 2000 items at once. This isn't a hard-and-fast requirement but it's best practice for SP 2007 (2010 raises this limit significantly as it handles your views/queries a little differently). Also, even if you only see items in batches of 5, you can still be pulling a massive amount of data (you just have it set to display them in batches of a certain number) verify the number of items returned when you switch to a certain view.

2. KPIs can be generated through a KPI list and also through calculated columns/data view web parts (see for more details on the latter). Where this is displayed is a key performance difference: if you can display a KPI inside a list (as a column) then you will be able to maximize performance. The reason that this can maximize performance is that each KPI queries a list view...which means that 10 KPIs that use the same view but show slightly different information will re-query the same view 10 times. If you have a column built into the list then you can include a KPI as a part of a specific view (maybe a reporting view).

3. If a KPI collection is going to be on a special page (maybe a dashboard for management), then there are some factors to think about: the number of KPIs, the views associated with those, and any custom graphics that we're trying to display; all of these will be performance hits on your web part page. To minimize these, try to actually use a list form web part and show a specific view of information in a list whenever possible. This can be a specific view of all late tasks grouped by person instead of a KPI that reports that information (so you don't get a pretty icon stating whether or not there's too many tasks behind schedule, but you'll see a number and be able to see corresponding data too). If we have more than 10-20 KPIs on a page then it takes maybe 30 seconds to display the data correctly (though some of that will be through inefficiencies in our own organization), so use KPIs to seriously high-level ONLY the information that managers need to see in a web part page and RESTRICT ACCESS to this page to the specific manager or manager SharePoint Permission group so that the page isn't constantly refreshed.

I'm welcome to receive other suggestions so that someone can reference this information as we discover more about KPIs. If any of this information is incorrect, please comment and explain why. Hope this helps!