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.
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.
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.
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.
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.
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.
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.
Next, specify the Row groups and Values.
Next, choose the layout.
Finally, choose a style and finish the wizard.
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.
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 )
)
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.
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.
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.
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.
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
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.