Using IT Analytics KPIs in SQL Server Reporting Services Scorecards

By Rob Reyes at 1/5/2012 8:24 PM
Filed Under: IT Analytics, OpsMgr, Scorecards, SSRS

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.


 Click to view larger image


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.



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.


Click to view larger image


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.


Click to view larger image


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." 


Click to view larger image


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


Click to view larger image


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.


Click to view larger image


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.


Click to view larger image


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.


Click to view larger image



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.

Using IT Analytics as a datasource for a Reporting Services Drilldown Report

By Rob Reyes at 8/9/2011 4:29 PM
Filed Under: IT Analytics, OpsMgr, Private Cloud, SSRS, VMware

Most out of the box reports are designed to answer a specific question at a given point in time. However, business requirements change frequently and limit the effectiveness of out of the box reporting. These changes require the constant tuning and even recreation of standard reports. By using IT Analytics for Microsoft System Center, another option is creating a drill down report that shows a high level summary of key data that also allows the user to click on the report to view detailed information.

In this post, we will show you how to quickly and easily create your own custom drill down report using IT Analytics and SQL Server Report Builder.

For this use case, our reporting requirement is to list the number of machines that require a software update and the percent complaint. We also want the ability to click on the required computers count to see a list the computers that require that software update. 

To make this drill down report possible, we will follow a three step process: create an overview report, a details report, and finally the necessary parameters.
Overview Report 
Using the steps outlined in a previous post, we create a report that includes the software update, the count of required computers, and the count of installed computers.  Lastly, we create a calculated field showing overall compliance for a bulletin.
 Click to view larger image
 Here is a preview of the report.
Click to view larger image
Details Report 
For the Details Report, we include the bulletin name, computer name and required computer count:
Click to view larger image
Here is a preview of the report:
Click to view larger image
To make the drilldown work, we need to add a parameter on both reports to pass the correct value to present the correct information.
On the Patch_L1 report, in the "Required Computer Count" column, we right click to bring up the "Text Box Properties" dialog box:
  Click to view larger image
For now, we leave the default options and only edit the "Actions" section:
In "Actions," we click "Go to Report" and select the "patchdetails" report.  
We also add a parameter called "pSoftwareUpdate" and the following formula:
="[Software Update].[Software Update - Bulletin ID].&[" +Fields!Software_Update___Bulletin_ID.Value + "]"
This formula will contain the same values as presented in the report.
Next, in the "patchdetails" report, we will add a new parameter:
Click to view larger image
 On the first page, we will give the parameter a name, title, data type and make the parameter visible.  
Next, we will set the available values.  We will get the values from a query to the dataset so the values match.  The parameter dataset is the same dataset used for the report, the value field is set to "ParameterValue" and the label field is "ParameterCaption."  The reason we recommend these settings is to match the data as it exists within IT Analytics.
To maintain consistency, we set the default value to "All." The formula used below is a direct reference to the IT Analytics cube:
Last, we leave the default settings:
Report Review
In the overview report, we now see that our cursor changes when we hover over the "Required Computer Count" number.
Click to view larger image
Here is the resulting details report:
Click to view larger image
Using the power of Report Builder and the available data within IT Analytics, we were able to build out a detailed drilldown report in a very short time without writing any additional code or SQL queries.  Our admins are able to present data quickly and easily, while our management team is informed and can navigate the data at their convenience!


Configuring a SharePoint 2010 Business Intelligence Center for IT Analytics

By Rob Reyes at 6/8/2011 1:21 PM
Filed Under: Install/Config, IT Analytics, SSRS
In previous posts, Ryan shared how we can provide rich, interactive charts and graphs with SharePoint 2010 and IT Analytics cubes.  In this post, I will show you how to set up a new, basic instance of SharePoint 2010 Enterprise, along with installing and configuring a Business Intelligence Center site that uses PerformancePoint Servces to use with IT Analytics.
 Click to view larger image
You can find the SharePoint 2010 hardware and software requirements listed at the following link:
I am using a Hyper-V instance of Windows Server 2008 R2 Enterprise with SQL Server 2008 R2 installed with 3CPUs, 3Gb RAM and 40Gb of disk space.  Please note that PerformancePoint 2010 is licensed only with SharePoint 2010 Enterprise. 
SharePoint Installation
Once the Microsoft SharePoint Server 2010 installation dialog box appears, I recommend clicking on "Install software prerequisites" first.  The install is great and installing any updates, roles and settings to ensure the install goes smoothly.  After the software prerequisites installation completes, click on "Install Sharepoint Server."
After entering my SharePoint 2010 Enterprise license, you will be prompted to select Standalone or Server Farm.  Given the available functionality and flexibility, I will be installing a Server Farm.   When promoted for server type on the next page, I will select "Complete" so all components are installed and available.
That is the last step for installing SharePoint 2010 Server.  We will now launch in to configuring my SharePoint 2010 instance.
A few clicks in to the wizard, I am asked if I want to connect to an existing server farm or create a new server farm.  In this post, I'll create a new server farm. 
Next, I specify the server and credentials on the following page.
After setting credentials, I need to create a passphrase that I will use as I add more SharePoint servers to this farm.
The rest of the options, I will leave at default and click through until the configuration is complete.
Next, I must configure the server farm.  A browser comes up and gives me an option to go through a wizard or manually configure the server.  I'll use the wizard.  I'll leave all the services at their default settings and select "Use existing managed account," and click Next. 
Click to view larger image
The next page is important.  This is where I will define my PerformancePoint "site" on SharePoint.  I'll give this site a title of "IT Analytics" and have the site run right off the root folder.
Click to view larger image
I scroll down to define what sort of site this is.  There are a number of templates out of the box, but the one I am interested in, Business Intelligence Center, is located in the Enterprise tab.  The Business Intelligence Center is where I will use IT Analytics as my datasource for custom reports, dashboards, charts and tables.  I click OK and a processing window appears.  Once that completes, my farm configuration is complete.
Click to view larger image
Two critical elements must also be configured:
1. The Secure Store and Unattended Service Account - This must be a shared domain account that will be used for accessing data sources we define within PerformancePoint.
Click to view larger image
2. Secure Store Service - PerformancePoint uses this service to store the unattended service account password.  Additionally, the credential database is encrypted by a key.  This key must be created before defining any data sources in PerformancePoint.
Click to view larger image
Now that my SharePoint Performance Point site is configured, I can now start creating custom dashboards, charts and tables using IT Analytics, and enabling my users do the same! 
 Click to view larger image