Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I - Page 4

March 19, 2007

Procedure: Perform Remaining Setup Steps within SQL Server Management Studio

Once the installation package has been run, we are ready to run the Setup file that can be found in the same directory into which we placed the reports. We can then open the primary dashboard report with the new Custom Reports option within SQL Server Management Studio. We will take these final setup steps in the sections that follow.

Run the Provided Setup Script against the Desired Server Instance

We can easily run the necessary SQL Setup script included with the Performance Dashboard Reports from our immediate position within the SQL Server Management Studio.

1.  Select File -> Open from the main menu within SQL Server Management Studio.

2.  Select File... from the cascading menu that appears, as shown in Illustration 16.


Illustration 16: Select Open -> File ...

3.  Navigate to the Performance Dashboard subdirectory that we created earlier (within the Custom Reports directory), which we populated with the Performance Dashboard Reports as a part of running the installation package.

4.  Click the setup.sql script file, as depicted in Illustration 17.


Illustration 17: Select Setup.sql ...

5.  Click Open to place the script into a newly opening query tab of the Management Studio.

The new tab, containing (and named after) the setup script, appears as partially shown in Illustration 18.


Illustration 18: Setup Script Open in Management Studio Tab (Partial View)

6.  Click the Execute (!) button in the Management Studio toolbar, as depicted in Illustration 19.


Illustration 19: Execute the Query ...

The query executes, and the Command(s) completed successfully message appears in the Message pane, underneath the Query pane, as shown in Illustration 20.


Illustration 20: Successful Completion of Query is Indicated ...

The script has set up a new schema, called MS_PerfDashboard, in the MSDB database, together with a set of functions and procedures referenced by the Performance Dashboard Reports. It is important to remember that the Performance Dashboard Reports rely solely upon information made available in the SQL Server 2005 dynamic management views (DMVs), and that no databases / tables are created (as is the case with standard Reporting Services reports), or used to store trending or other data for use by the reports.

Open and Examine the Primary Dashboard Report with the Custom Reports Functionality

All that remains to complete setup is to open our primary dashboard report using the new Custom Reports functionality that is introduced, as we have seen, within Service Pack 2.

1.  Right-click the appropriate server instance within the Object Browser.

2.  Select Reports ... from the context menu that appears.

3.  Select Custom Reports ... from the cascading menu that appears next, as depicted in Illustration 21.


Illustration 21: Select Reports -> Custom Reports ...

4.  Navigate to the Performance Dashboard subdirectory we created earlier, and select the performance_dashboard_main.rdl file contained therein, within the Open File dialog, as shown in Illustration 22.


Illustration 22: Select the Primary Dashboard Report File ...

5.  Click Open to open the file within Custom Reports.

As we shall discuss in Part II of this article, the first time we choose the Custom Report option, we are prompted to choose the report to run, as we see here.  Once we have browsed to the share housing the primary dashboard report file, and loaded it into the viewer, the dashboard can be accessed prospectively simply by using the recent file list, instead of manually browsing to the share.

6.  Click Run on the Run Custom Report dialog that appears next, as depicted in Illustration 23.


Illustration 23: Click Run to Execute the Report ...

The message Retrieving Data briefly appears, and then the dashboard opens, presenting status information for this point in time (for purposes of this exercise, I am performing a full process of the Adventure Works Analysis Services database, as well as a simultaneous full backup of a copy of the Adventure Works database), as shown in Illustration 24.


Illustration 24: The Performance Dashboard in Action ...

At this point, we can drill through to various supporting reports, via the hyperlinks that appear on the dashboard. We will explore the set of supporting reports in the subsequent article in this series. In the meantime, if you wish to test drillthrough and other features, this is a great time to become familiar with all the parts and pieces.

7.  Perform drillthrough to underlying support reports, as desired, by clicking the respective hyperlinks in the Performance Dashboard.

8.  Select File -> Exit, when ready, to leave SQL Server Management Studio, as depicted in Illustration 25.


Illustration 25: Select the Primary Dashboard Report File ...

Having loaded the primary dashboard report as a Custom Report within Management Studio, we can easily open and refresh it (and, of course, access all supporting drillthrough reports from it) anytime we enter Management Studio. In our next article we will examine the ease with which gaining such access is possible, as well as review the purposes and uses of the supporting report files that ship with the Performance Dashboard Reports, together with other topics related to this useful new administrative tool.

Conclusion ...

In this article, we introduced the Performance Dashboard for MSSQL Server, a new, useful administrative report pack offered for download by Microsoft just after the release of MSSQL Server 2005 Service Pack 2. After discussing some of the details of the contents within, and valuable uses for, this new reporting option, we prepared for installation by downloading and running the installation file (.msi) available from Microsoft. As an additional part of preparation, we ascertained that system requirements were met for installing the Performance Dashboard Reports in our local environments.

We next performed remaining setup steps within SQL Server Management Studio, including taking the steps to load the primary dashboard report within the new Management Studio Custom Reports functionality. We examined the face of the dashboard within SQL Server Management Studio. Finally, we looked ahead to our examination of the individual, supporting drillthrough reports in Part II of this article.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers