Bill Ramos, Principal Program Manager, SQL Server Manageability
Up until this point, I’ve walked through step by step how the MDW reports could be recreated to use SQL Server Reporting Services 2008 R2 and edit them in Report Builder 3.0.
At last week’s SQL PASS, I announced in my Wednesday session that I would release the source to the RDL files that I demonstrated.
At the bottom of this post, you’ll find a zip file that contains the RDL files that we converted up to this point. I say we because this was a collaboration with Bart Duncan – author of Query Hash Statistics, Rachna Agarwal from our Microsoft IT department in Hyderabad India and myself.
The first think you will want to do is download and install the SQL Server Query Hash Statistics project on MSDN Code Gallery. This includes a new version of the Query Statistics collection set – the old one – you’ll want to turn off – and a set of reports that can run inside of SSMS as a custom report. I’ve included a slightly modified version of Bart Duncan’s reports that work in SSRS.
You will want to refer back to my post “What are all these reports anyways? – MDW Report Series Part 5” that lists of the MDW reports and there relationship to each other. These reports assume that you have created shared data source called MDW in your Reports manager filder. Just unzip the reports and upload them to your Reporting Services server. Start with the new MdwOverviewCustom.rdl file and you should be good to go.
Here is a list of the reports you will find at the bottom of this posting in MDW RDL Files.zip.
MdwOverviewCustom.rdl – Bart’s new an improved Overview report
That’s it! Have fun with the source. Let me know what you think about them on http://twitter.com and include my @billramo address and the hash tag #MDWReports.
The source reports point to a shared MDW datasource on my old machine at Microsoft. You'll most likely end up with the following error:
This is awesome, i have modified your reports and succesfully published to my sharepoint farm..
Thanks a lot...
I dont find the rdl files for CPU utilization and also for the disk usage collection sets, can you please attach them to your blog?
Thanks in advance..:)
Hey Ashok ,
We are done with the CPU utilization and the the disk usage RDL's . Bart will soon be uploading it here in his blog.
Sorry about the typo in the above . It's Bill and not Bart.
Many thanks for the tremendous work.
Do you have a Report for disk usage?
Thanks a lot for the detailed information about MDW. I have noticed that Server_Activity.rdl timeline and button underneath it are not working.
Please help me out if you can. Thanks.
Fantastic series Bill.
I recommend this as a beginner tutorial for introduction to Performance Studio and SSRS. I had already setup an MDW warehouse database and was collecting data prior to following this series. The instructions were clear and easy to follow.
Thank you for publishing the remaining .rdl's, though I think I will create several of them myself for the practice.
I had been trying to run the final MDW reports. I cant find the table custom_mdw_overview_reports. Please let me know how to proceed?
I have exactly the same problem. Has someone the definition of that view ??
FYI: I also hit the issue of cannot find custom_mdw_overview_reports. However, if you take the MDWOverViewcustom.rdl from archive.msdn.microsoft.com/.../ProjectReleases.aspx & setup the queryhasstats collection set from there the reports should work.
Looks like the version of MDWOverViewcustom in the zip is an outdated one...
For everyone having the custom_mdw_overview_reports problem: i've uploaded the DDL for this table in my blog www.sqlixo.com. Basically, you have to execute this script http://txtup.co/mKoSJ in the data collector database to solve this issue. Cheers.
Great, thanks for these. Are there any similar rdl files available for Utility Explorer reports available in SSMS for UCP MDW?
I think the zip is missing a file.
in the report sql_active_request, it has an action link to go to report query_stats_detail but there is no query_stats_detail.rdl. are you able to update the zip? Thank you.
Can you please attach script of below 2 custom stored procedures which you used in
QueryHashStatsReportsTopN and server_cpu_snapshot