Share via


MDW Overview Report for Data Collector – MDW Report Series Part 1

By Bill Ramos, Program Manager, SQL Server Manageability Team

The Management Data Warehouse (MDW) overview report that I mentioned in my previous blog post on Viewing MDW Reports with a Low Privileged User Account provides a list of database instances and the last collection set upload times for the MDW database. Below is an example of the report when run inside of SSMS.

MDW Overview Report Two Instance

By clicking on the last upload hyperlink, SSMS launches the report displaying the Server Activity, Query Statistics, or Disk Usage for the instance.

The question that many people have is – how can I run these reports from a Reporting Services server rather than from SSMS? The short answer is that you can’t. However, by understanding the schema of the MDW database and the ways that the reports extract data from the MDW through various stored procedures, you can create your own reports to get data from the MDW.

Unfortunately, there is little documentation on the reports and especially the stored procedures used to access data. In this series of blog posts, I’m going to run through the various reports that SSMS provides for analyzing performance that is collected in the MDW database and how you can create similar reports.

I wish I could simply provide the report source code as part of a codeplex project, but the source code references SSMS based assemblies and uses resourced strings that may it very difficult to modify and use. Instead, I’ll provide RDL files compatible with Reporting Server’s Report Builder 3.0 that ships with SQL Server 2008 R2. I’ll assume that you have a Reporting Server already setup as part of the series. I’ll also assume that you have an MDW database already setup and your collecting performance data through the Data Collector feature that we shipped with SQL Server 2008. See Getting Started with the Data Collector. I’m also going to assume that you are building the reports directly against the Reporting Server using Report Builder 3.0 using Windows security.

MDW Overview Report

The MDW Overview report is the natural starting point since it is the main launch point for all the MDW reports.  Here is an example of what the end result looks like running from Reporting Server.

mdw_overview report example

Connecting to the MDW

The first step is setting up shared data that the reports will use when connecting to the MDW database.  Here is an example of one I created in the root folder for the my Report Server.

mdw_overview shared Datasource MDW

The MDW I’m referencing is sysutility_mdw. This is the same MDW file that is created when you create a Utility Control Point with SQL Server 2008 R2, but it can be any MDW created using the Configure Management Data Warehouse wizard.

Starting with a Blank Report, you need to right click in the Data Sources node in the Report Data pane and create a new data source pointing to the shared connection as shown below.

mdw_overview Datasource MDW

Dataset for Instances

To get the instances that have uploads stored in the MDW along with the last collection upload time, you can use the snapshots.rpt_list_all_servers stored procedure. Below, I show creating a dataset named MdwServerList that calls the stored procedure.

mdw_overview Dataset MdwServerList

If you right click on the new MdwServerList, issue the Query command, and then run the query, you’ll see one row per instance that is uploading to your shared MDW database as shown below.

mdw_overview execute rpt_list_all_servers

The snapshots.rpt_list_all_servers gets its data from the core.snapshots view in the MDW. Feel free to script out the stored procedure to see how it retrieves the data. There is a comment in the procedure pointing out where you should add new collection sets to the result set.

I should point out that the resulting last upload times are represented as a “Coordinated Universal Time” (UTC) that will need to be converted to local time for the report. To convert UTC to local time, I’m going to use a function called UTCtoLocal that will go into the Custom Code section for the Report Properties.  Right click on the report background and issue the Report Properties command and then go to the Code page in the dialog and paste the following code snippet into the Custom Code area.

Public Shared Function UTCtoLocal(ByVal UTCValue) As String
' Get the local time zone and a base Coordinated Universal
' Time (UTC).
Dim localZone As TimeZone = TimeZone.CurrentTimeZone
If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then
   Return Nothing
Else
   ' Calculate the local time and UTC offset.
   Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)
   Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)
   Return localTime.ToString()
End If
End Function

I’ll use this function later on once we’ve created the table showing the results.

Table for the Results

To create the table for displaying the results, I’ll walk you through the steps using the Table Wizard. First choose the MdwServerList dataset.

mdw_overview table choose dataset

Next, specify the Row groups and Values.

mdw_overview table arrange fields

Next, choose the layout.

mdw_overview table choose layout

 

 

 

 

 

Finally, choose a style and finish the wizard.

mdw_overview table choose style

The end result is a table that displays the instance name and the three last upload times for server activity, query statistics, and disk usage collection sets.

mdw_overview table wizard complete

As I stated earlier, if you were to run the report, the date and times reported will be UTC. Let’s fix up the server_activity_last_upload field by using the UTCtoLocal function. Right click in the [server_activity_last_upload] cell in the table and choose the Expression command.

In the Set expression for: Value text box, paste in the following code and click Ok:

=IIf (IsNothing(Fields!server_activity_last_upload.Value),
"No Data Available",
    code.UTCtoLocal( Fields!server_activity_last_upload.Value )
)

mdw_overview table UTC expression

You’ll want to add similar expressions for Fields!query_statistics_last_upload.Value and Fields!disk_usage_last_upload.Value.

Next, you’ll want to clean up the report, add titles and explanation text similar to the shipping version of the MDW Overview report as shown below.

mdw_overview layout of report

The <<Expr>> below the title displays the run time for the report using the following expression:

="at: " & FormatDateTime(Globals!ExecutionTime)

Add Column Sorting

The shipping version of the report includes column sort controls as shown below.

mdw_overview sort column headings example

To add sorting for Instance Name, right click in the Instance Name column header cell and choose the Text Box Properties command. Then complete the Interactive Sorting page as shown below.

mdw_overview column sort by instance name

For Server Activity, you’ll do the same thing, but choose [server_activity_last_upload] for the Sort by field. The same goes for Query Statistics and Disk usage column headings.

Adding the MDW Name to the Report Title

The last step is adding the database name to the report title. In the SSMS version of the report, the MDW database name is passed in as a parameter to the report and its simply appended to the title string. To get the database name for this report, we need to create a dataset that returns the name. Just create a dataset that looks like the one below.

mdw_overview title database name dataset

Then change the expression for report title text box to the following expression:

= "Management Data Warehouse Overview: " & First(Fields!database_name.Value, "database_name")

Almost Done, But Not Quite

If you followed along, you now have an RDL file that runs on your Reporting Server that displays the MDW Overview of instances being collected using the Data Collector. I’ve attached a copy if my version of mdw_overview.rdl as a reference, but I encourage you to follow along.

The next step in the process is to add hyperlinks to collection upload time cells that drill into the detail reports for Server Activity, Query Statistics, and Disk Usage. But, that’s for another blog post.

Acknowledgements

I’d like to acknowledge Jin Chen from MSFT, who posted the source for UTCtoLocal on the MSDN forum.

I’d also like to acknowledge Bart Duncan who is a developer on our team for pointing me to the source files for the MDW reports and the little secrets on how they work.

Technorati Tags: Data Collector,SQL Server 2008 Express,SQL Server 2008 R2,MDW,Management Data Warehouse,SSMS,SSMS Reports

mdw_overview.zip

Comments

  • Anonymous
    August 19, 2010
    This is an excellent article. Thanks a lot for posting this,

  • Anonymous
    April 19, 2011
    This is perfect. Thanks for showing how to create the report. I was searching for the system ones ... as you said ... no avail. The first report runs on my web ... going to the secont article .. thanks alot. great job.