May the source be with you! MDW Report Series Part 6–The Final Edition

May the source be with you! MDW Report Series Part 6–The Final Edition

Rate This
  • Comments 14

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.

2010PASS_HomePg

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.

Download Query Hash Stats

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.

May the Source Be With You

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

mdw_overview.rdl – The original one as part of the blog series

QueryHashStatsReportsDetail.rdl – From Query Hash Stats that includes a query to get the execution plan.

QueryHashStatsReportsTopN.rdl – The new and inproved Query Statistics report from Query Hash Stats. You’ll never want to look back after seeing this report.

server_activity.rdl – From the blog series.

sql_active_requests.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

sql_activity.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

sql_disk_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

sql_memory_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

sql_waits_blocking_chain_detail.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_blocking_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_generic_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

sql_wait_stats_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5

Send Your Comments Via Twitter

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.

One Little Gotcha

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:

  • The report server cannot process the report or shared dataset. The shared data source 'MDW' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

The workaround is pretty simple, create your own MDW shared data source and then after uploading the report, open it up in Report Builder 3.0 or BIDS. Simply go the MDW data source, right click and select properties. Click on the Browse button to find your version of the MDW data source and click OK. Save the report,, and you should be good to go.

Attachment: Final_MDW_RDL's.zip
Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • This is awesome, i have modified your reports and succesfully published to my sharepoint farm..

    Thanks a lot...

  • Hi Bill,

    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.

    Thanks,

    Rachna

  • Sorry about the typo in the above . It's Bill and not Bart.

  • Hi Bill

    Many thanks for the tremendous work.

    Do you have a Report for disk usage?

    Thanks

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

  • Many thanks for the tremendous work.

    Do you have a Report for disk usage?

    Thanks

  • 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?

  • Hi,

    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.

Page 1 of 1 (14 items)