My experience with Microsoft technology

My experience with Microsoft technology

Creating a Performance Report on raw data

leave a comment »

Lately I had a request to support a performance test of some SQL systems with Operations Manager. I thought no problem, just use the standard performance report and everything is fine. Unfortunately the requestor wanted to see the raw data which is not possible with the standard report. So I had to create a performance report on my own.

My first try was to change the standard report but this sounds easier than it is. I encountered several problems:
1. You can’t open the report in Visual Studio 2008 without errors because the custom chart control used in the performance report can’t be registered in Visual Studio 2008. You can do this in Visual Studio 2005 how to do that is described here.

2. Ok so I installed Visual Studio 2005 and I was able to open the report. Unfortunately my data source is a SQL Server 2008 which is not natively supported by Visual Studio 2005. There is a hotfix which should add this feature but it simply won’t install in my environment.
So I just gave up and created my own report from scratch. I will describe the entire process of creating a performance raw data report here. You should consider reading the Report Authoring Guide (Download guide) from Microsoft as it contains some basics and useful hints. The mentioned sample is available here (Download sample)

OK let’s start.
At first you need to get the data you want to see out of your database. Very helpful with this task is the list of SQL queries from Kevin Holman which you can find here: SQL query list. Thanks for that.

I used this query :

select Path, FullName,DisplayName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName like @counters1
and [Path] like @machine1
and [DateTime] > @start1 and [Datetime] < @end1
and DisplayName like @instance1

For non SQL related counters or if your SQL Server has only one instance installed the “DisplayName like” statement can be deleted. Just play around a bit to get what you want.

Now it is time to open Visual Studio 2008 and start a new Report Server Project.
At first we need a data source:

1 datasource

Type in your server and choose the data warehouse database:

2 datasource

Click ok and name your data source DataWarehouseMain so that the existing one on your report server is used when the report gets deployed. In older posts you see the name with spaces but this is not possible with Visual Studio 2008.
In the Solution Explorer right click Reports and choose: Add new Report .
Click next on the welcome page. Ensure that the data source we created is selected on the next page.
In the query designer windows add your query and click next. When you use parameters in your query a windows will pop up. Choose blank instead of null for all your parameters:

3 query

On the next page you can choose if you want a tabular or matrix. Because we want a nice chart it doesn’t matter what you choose on the next pages. Just click trough them. On the last page specify a descriptive name for your report.
Now you should see something like this:

4 report

Change the Headline to something meaningful and delete the table. Next extend the canvas through the properties window. If you want to print out your report use 8.5in and 11in and keep in mind that depending on your printer you can’t print up to the edges of the paper. Just test out how far you can go.

5 properties

Click on the free space in your report and insert a chart . I want it to be a line chart with dots for each value but basically you can choose whatever pleases you.

6 chart

Click on the chart and drag and drop the items from the data set to the chart as follows:
Sample values to the top field, Date Tme to the bottom and CounterName DisplayName and Path to the right:

7 chartprop

Now we are ready to preview the report but we have to specify the parameter values each time. To prepopulate them do the following:
On the left in the Report Data Window double click the parameter you want to prepopulate. In the default value tab you can specify the value you want or you can even write a query to the database which results in a dropdown list from which the user can pick the value he wants.

8 param

When all values are configured and the report looks the way you want it is time to upload it or use it in a management pack.
To upload it directly to your report server right click the report project in the solution explorer and configure your server in the property window. Then right click the report project and choose deploy .

9 server

10 deploy

If you want to use this report in one of your management packs open it in the Authoring Console (or create a new one). Click on Reporting and create a new report .
Specify an ID and a name for the report than click on the Definition tab. Click on Load data from file and browse for the .rdl file in your report project folder.
Once the content is loaded you have to delete the first line: <?xml version=”1.0″ encoding= “utf-8”?>< /STRONG> otherwise the console will throw an error.
Last but not least you have to change the
visibility to true (I can’t imagine why the default is false). Set the accessibility to public if you want to be able to reference this report from other management packs.

11 mpack

That’s it. Save the management pack and import it to Operations Manager. It takes some time until the report is uploaded to the report server. You should watch the Operations Manager event log on the RMS for Data Warehouse related errors.

If you want to present the parameters with the standard operations manager looks, the magic words are report parameter block. Start with this post and the Report Authoring Guide



Written by alexanderschmitt

15. January 2010 at 19:13

Posted in Miscellaneous

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: