Microsoft's SharePoint 2010 and PerformancePoint offer powerful tools to create custom KPIs and Scorecards. You can also use the SharePoint framework to easily and securely share this information to any user in your enterprise.
Previously, we showed you how to create KPIs within IT Analytics. In this post, we will walk you through the process of creating custom KPIs and scorecards exclusively within SharePoint using IT Analytics data.
In the first post of our KPI Series, we were able to present a scorecard based on IT Analytics data in SQL Server Reporting Services. Management has now asked that we expand the presentation of this data to SharePoint. Along with the scorecard that was already created, additional metrics like CPU, Memory and Network utilization would like to be included.
Find the Data
First, we investigate the data in the Performance Daily Cube and filter on the "Windows 2008 Operating System (Monitoring)" management pack. Next, we select he counters we are interested in. We will be using these values to filter our data, and use that resulting data set to create specific KPIs.
Create the Key Performance Indicators
Data Source Configuration
To create KPIs within SharePoint, we must configure the "Time" tab in our data source. This configuration will allow us to define the date range for the trending of our KPI.
- First, select the data source for the Performance Cube and click on the "Time" tab. Set the "Time Dimension" field to "Date.Performance Evaluation.Performance Evaluation Date - Date."
- To define the Reference Member, click the "Browse" button and select a date where the trending will begin. For this example, "2011-01-01" was selected.
- There are several options in Hierarchy (year, quarter, month, hour, etc.), so in our example, we will select "day" since that maps to the time dimension we selected.
- Next, the reference date should map to the date selected as the Reference Member. Here, we will select "2011-01-01."
- Lastly, the aggregation should be the same as hierarchy level. In this example, it is set to "Day."
NOTE: If you are interested in trending across a different hierarchy levels, say Month or Quarter, create a separate data source for each level. Reason for this is to ensure calculations are across the proper level.
Our first KPI will be for CPU Utilization over the past 30 days. In the Dashboard Designer, we click on "Create - KPI."
The first row is the "Actual" value. To set this value, we click on the "Data Mappings" cell to bring up a new dialog box. We will change the source to map to the IT Analytics Performance Daily Cube data source we configured earlier and click "OK."
Once the data source is set, we will filter down to our actual value for the current counter. We do this by selecting the "Average" measure and by adding a couple "Dimension" filters. The first is the Windows 2008 Management Pack and the "% Free Space" counter. We then aggregate the members by "Average."
We now click on the second row to define the target. We click on the "Data Mapping" cell for Target and enter "75" in the value field.
We can also adjust the scoring pattern and change the indicators for the "Target" value to match how we want the data represented. In this example, we leave the default indicators and thresholds.
Next, we must define the trend value for this KPI. We will click on the "New Target" button and a new row will be added to our KPI. We rename that row to "Trend," and click on the "Data Mappings" cell. We will repeat the steps we followed to get the "Actual" value, but we will be adding one new filter, a "Time Intelligence Filter."
Since our data source has a hierarchy of "day" we enter in the formula "day:day-30" to show data only from the past 30 days. You can see the formula and a sample of the resulting data set below:
Finally, we configure the scoring pattern and indicator. After highlighting the "Trend" row, we click on the "Set Scoring Pattern and Indicator" button. In this dialog box, we want to show that an increase is better, but also display the trend relative to the actual value.
After clicking next, we select a half-circle indicator to show the trend.
The last page does not require any additional configuration, so we click "Finish."
After renaming our KPI, our final configuration should look like this:
Creating a new Scorecard
To view the KPI, we will have to add the KPI to our first Scorecard. From the "Create" tab, we click on "Scorecard" and select the Analysis Services template.
We will also select our Performance\KPI data source:
Then select "Create KPIs." Don't worry, we will be able to import the KPIs we just created.
On the next page, we click "Select KPI," we select the KPI we just created, and then click OK.
Once the KPI has been added, we leave the default settings for the remaining pages and click "Finish" on the last page. With some additional formatting, our new scorecard and KPI is available.
We created 4 additional KPIs and added them to our new Scorecard:
Being able to view overall KPI trends is invaluable, but being able to see that data at a server or entity level is the sort of forensic analysis IT Analytics and SharePoint 2010 can make a reality.
We start by creating a new KPI called "Win2008 Server Disk Space Utilization." We based this KPI on the Windows 2008 Monitoring Management Pack and the Performance Counter "% Free Space." We set a goal of 75% as our upper threshold and in our scoring patter options, we select increasing is a positive trend.
Next, in the "Create" tab, we click on "Scorecard" to create a new scorecard and add our newly created KPI.
To build out the drilldown, we will drag and drop the "Host Entity - Display Name" dimension, located in the right pane, after the KPI name as a "last child."
After the dimension is added, we are prompted to select the target servers for this KPI. We select the servers we are interested in, and then click "Refresh" on the "Edit" tab and we get the resulting scorecard.
Next, we will add "Entity - Name" to the right of the server as a "last child."
Again, we click on "Refresh" on the "Edit" tab and the scorecard is updated.
To ensure that we don’t have any empty rows in our scorecard, we will right-click on the "Target" and "Trend" headers and select "Filter Empty Rows" for both columns. Note that the filtering does not appear in the Designer view, but will be in the final published scorecard.
Publish the Scorecards
Finally, we add both scorecards to a Dashboard and publish. We can now view the KPIs and Storage scorecards in this new SharePoint page:
Using feature rich Microsoft technologies and robust IT Analytics data, we were able to locate the metrics our management was interested in and quickly created a number of Key Performance Indicators and two different scorecards to display these metrics. These scorecards offer tremendous insight and are readily available on SharePoint, enabling the team and management to view the current state of the environment, without any additional custom report or custom query development time.