SharePoint Server 2007 offers five different methods to create Key Performance Indicators (KPIs)
- Using data from published Microsoft Excel 2007 spreadsheet.
- Using KPIs defined in SQL Analysis Service Cube.
- Using dashboard created in PerformancePoint 2007.
- Using data from SharePoint list.
- Using manual data entry.
This post compares the relative merits of methods 1-3 and suggests scenarios where a particular method is suitable over others.
| Excel 2007 | Analysis Service Cube | PerformancePoint | |
| Feature Set | Basic feature set, complete with indicator icons and basic rules. | Rich feature set. Enables to build KPIs and Trends limited only by the cube design. | Feature set is richest compared to other methods. Enables building simple and nested KPIs and having the capability to filter and set dynamic targets from multiple data sources. |
| Ease of creation | Easiest of the all the methods. Developer skills not required. A Business Analyst can create a KPI with few clicks. | Requires a Developer with good MDX skills to define KPIs in the cube (with inputs from Business Analysts)Once the KPI is created in the cube, the Business Analyst can easily create SharePoint KPIs. | Business Analyst can create KPIs from Cube Measures and Dimensions. Knowledge of the underlying cube structure is required. Relatively easier than building a KPI in Cube but bit more involved than Excel KPIs. |
| Dynamic Data Refresh | Static. Since the Excel Spreadsheet is a static document in the report library, the KPIs don’t refresh automatically. Data refresh can be achieved by some external means like periodically replacing the published Excel with a new one with latest data. | KPIs refresh dynamically with the cube data. | KPIs refresh dynamically with the cube data. |
| Interactivity | Does not respond to SharePoint out-of-box filters in dashboard since they are static. | Responds to out-of-box SharepPoint’s Analysis Server filters as well as custom webpart filters . | Responds to Performance Point filters but not SharePoint filters. |
| Special Features | Excel Spreadsheets can be built from variety of data sources. This removes data source technology constraints to build KPIs. | *Trends can be defined only in Cubes.*For Complex KPI’s, cube KPIs is the only way. | *Nested KPI’s.*Multiple data sources.*Flexible Targets/Benchmarks.*Wide array of indicators for better presentation. |
| Typical Scenarios | Excel KPI’s can be used where data does not change often for e.g. KPI’s built from quarterly survey s | Any scenario where personalization and filtering is required E.g. My Revenue | Firm-wide KPI’s that does not require filtering and same for all SharePoint users. |
Benny Austin 