Introduction:
The Business Intelligence team at PointBridge was recently sent the following email:
Guys,
How would we do these:
1. Display on one dashboard only the KPIs that are red (or Y or G) from an entire SharePoint site collection (SC)
2. Display on one dashboard all KPIs in a SC that if the current trending continues will be red in a certain predefined timeframe (i.e. 2 weeks, 1 month). It would be nice to use a parameter to allow the user to determine the timeframe
3. Display on one dashboard all KPIs in a SC that change to a new category (any change from G to Y, Y to G etc.)
4. Display on one dashboard all KPIs in a SC whose rate of change is increasing or decreasing (again a parameter based item) and displayed by category (G,Y,R)
I have spent time looking online and had conversations amongst other members of the BI team regarding the questions above. I did not find an easy out of the box solution with SharePoint or PerformancePoint to do exactly what was asked, however, there are a few options. All of the comments below are based off of my experiences and any responses or additions to my thoughts would be greatly appreciated!
Gathering KPIs to a centralized location:
First off, for all of the scenarios described it was general consensus that it would be beneficial to get all of the KPIs into one centralized location. In a scenario where there are a lot of separate SharePoint sites within a site collection that contain unique KPIs, one could either:
a. Source all of the KPIs from a single KPI list managed by a site admin
b. Create an SSIS package to pull all of the KPI lists into an Analysis Services cube
c. Create all of the KPIs in Analysis Services in the first place, deploy these KPIs through PerformancePoint, SSRS, or Excel
Getting all of the KPI data into Analysis Services can prove to be a best practice since Analysis Services allows for the most flexibility and customization for the KPIs, KPI calculations, and KPI distribution.
Sorting and Filtering KPIs:
Secondly, I did not find any built in functionality to sort / filter KPIs based on their status (i.e. Red, Yellow, or Green). However, there are a few options:
a. In the screenshot below a user has the ability to sort the KPIs based on their status by clicking on the "Percent" link. Keep in mind that the only reason this works is because this custom web part is not filtering based on the status, it is filtering based on the Percent. Since all of the KPIs on this page are a comparison of the same key metrics (Budget to Actual), sorting on the percent can be done and produce logical results.
b. An SSRS report could be created to consume data from an Analysis Services cube. Nested IIF statements and / or CASE statements could be used to replicate the KPI logic within the cube in order to allow a user to filter KPIs based on a value (-1 = Red, 0 = Yellow, 1 = Green).
c. PerformancePoint and the built in SharePoint KPI web part do have the ability to either "Show Only Problems" or "Show All Indicators". When a user chooses to "Show Only Problems" this will show all red and yellow indicators, however, this does not sort the KPIs in any specific order. By selecting "Show All Indicators", all red, yellow, and green indicators are displayed.
Trending / Rate of Change:
If the KPIs were stored in an Analysis Services database MDX could be used to create powerful trending / rate of change functions for the KPIs. Without being able to use MDX I am not sure how this would be done. Once again I believe Reporting Services could be used to pass a parameter into an MDX query in order to allow a user to "control" or provide input into the trending and / or rate of change that they desire to view.
Conclusion:
In a perfect world it would be nice if all KPIs on a SharePoint site were referenced from a single KPI list. This typically does not happen since groups of users tend to manage their own material separately on an as needed basis within their own team sites. Getting KPIs into a single consolidated location (an Analysis Services cube) would allow for more flexibility with KPI calculations and distribution. MDX could be leveraged in order to help provide some more advanced user-driven trending analysis.
