In our previous two posts, found here and here, we showed you how to create and present KPIs in a number of different ways. In this last article of our KPI series, we will show you how to present these KPIs by creating a scorecard in Microsoft Excel 2010.

Business Need

Management has been very excited with the number of new reports available, along with the speed in which these have been made available.  There are some additional users that are offline and would like to view this data in Excel.

Create the Pivot Table View in Excel

We will start by creating a Pivot table view that filters on the Windows 2008 Monitoring Management Pack and the Counter Name "%Free Space." We then add "Average" as our measure, filter on the servers we are interested in and add  "Entity - Name" to see the average value for each associated disk drive.

We now click on the "Export to Microsoft Excel" button to recreate our pivot table view in Excel.

With the pivot view ready, we have access to all the dimensions and measures within Excel.  We will add the KPI Trend measure to the spreadsheet, rename the column to "Trend" and add some formatting.

Create the Scorecard

Now that we have the data, we can focus on formatting the scorecard.

First, we will add indicators to the "Average" measure.  We highlight the average values in the "B" column and click on "Conditional Formatting" and select "Shapes."  These shapes are then automatically added to the cells we had highlighted.

We will next select on "Manage Rules" under "Conditional Formatting" and click "Edit Rule".  For this example, we select the third option so the shape formatting is applied to all "Average" fields.  The last change is regarding the scale for the icon values.  We change the values to match the thresholds we defined in our SSRS report and change the type to "number" since the "Average" is already set as a percentage.

Below is a screenshot of how our scorecard is looking.

Next is to format the "Trend" column by selecting the trend value in column "C."  We click on "Conditional Formatting" and click on directional shapes.

We then go to "Manage Rules" and click on "Edit."  We select the third option to apply this formatting to all trend values.  We check the box "Show Icon Only" and leave our values and percentages at default.

After some additional formatting to the spreadsheet, we have our final scorecard.

Summary

IT Analytics has made it extremely easy to find data, allowing us to focus on presenting data in a number of formats.  We were asked to create a scorecard in Excel so even more users could view the data.  We built out a pivot table view, exported this view in to Excel and added formatting to the pivot table view.  In Excel, a multitude of users can view the latest information quickly, and act accordingly.

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.

Business Need

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:

Drilldown Scorecards

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:

Summary

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.

IT Analytics makes it easy to navigate System Center data and identify metrics to measure against.  While these metrics can be identified and managed within the Service Manager console, Microsoft has a number of technologies that makes it easy to share this information.  In this post, we will show you how to create a Scorecard that shows performance against a defined goal and the KPI Trend relative to that goal, all within a SQL Server Reporting Services Report.

Business Need

Management continues to ask the team about disk space utilization and how we can continue to optimize available disk space to support our growing environment.  Since all this storage data does exist in SCOM, we will create a scorecard listing the servers of interest, the drives associated to each  server, and free space metrics.

New KPI

The first item we will need to create is a Key Performance Indicator.  This KPI will be based on the "Windows Server 2008 Operating System (Monitoring)" management pack counter "% Free Space."   By creating this KPI based on this single counter, we will be able to split this metric across servers and drives in our scorecard.

We highlight the grand total value and click "New KPI."  In the "Edit KPI" dialog box, we add a goal of "75" and trend this over the past 180 days.

After clicking on "Save KPI," the KPI values are saved to the cube.  We can now include these values in a SQL Server Reporting Services data set.

SSRS Scorecard

Following the steps outlined in this blog entry to connect to the IT Analytics SCOM Performance Daily cube, we create the dataset we are interested in.  We include KPI Trend data as well.

Next, we build out the report by adding host name and entity name to our rows, counter name as a column and the average as the main value.

The next two columns we add will make our scorecard really come to life.  We right click on the cell that contains the "average" value and select "Insert Column - Inside Group - Right." 

We will do this action twice: one for the state indicator and one for the trend indicator so our table looks like this:

The first indicator we will insert is the "state" indicator.  We will place this indicator in the first cell to the right of the average.

Once the indicator has been placed, a dialog box will appear allowing us to select what indicator we want to include.  For this first indicator, we will select Shapes:

Once in place, we right click on the icon and select "Indicator Properties" to modify the default settings.  On the first page, we name our indicator.

In the Value and States section, we set "Value" to [Sum[Average]].  While these can be adjusted to match requirements, in this example we will leave the default settings.

In the actions page, we leave the default settings of "None."

We will repeat the process above and insert a second indicator to the right.

The second indicator will be a directional indicator:

We then right click on the indicator to change the Value properties.  Since the KPI Trend is part of our data set, we can use that value to calculate the trend indicator. We set the value field, adjust the states and click OK.

After some additional formatting, we can view our scorecard detailing for each server, the percent free disk space for each associated drive and how the percent disk space has been changing over the past 180 days.

Summary

Management asked for a report based on free space data within SCOM and to present this data in an easy to understand format.  Within IT Analytics, we were able to quickly investigate the Free Disk Space data and define a Key Performance Indicator.  This data was then the basis for our SQL Server Reporting Services Scorecard.  This scorecard can now be embedded in a web page, included in a SharePoint site, or subscribed to for frequent updates.  This new report will enable the team and management to review performance and trending relative to defined goals and make more informed decisions regarding disk space utilization.