The OperationsManagerDW Schema

The OperationsManagerDW Schema

The most important part of writing a custom report is generating the correct query to extract the information you need from the OperationsManagerDW database. To write the correct query, you need to understand the database schema. Reports should only query the database views built into OperationsManagerDW; you should not access the tables directly.

In This Section

The OperationsManagerDW Schema

Before you can start to write a custom report, you need to know what views in the OperationsManagerDW database hold the information you want to retrieve. For custom queries, you only need to access a subset of the schema. This section covers this subset of the schema and introduces the views that you’ll need to access in order to write a custom query.

Inside a Generic Report

A good way to become familiar with the SQL queries you’ll need in order to create custom reports is to look inside one of the published reports that are installed with the Operations Manager Management Pack. This section examines the SQL queries in several of the reports and explains how they function, which will enable you to build your own queries.

Creating Custom Queries

Once you understand the schema and how the queries work in the existing reports, this section shows you how to write your own custom report query. This section will use multiple examples, starting with a basic query that retrieves just one counter across several computers, and then moving to more complex examples. Although these examples address specific custom reporting needs, the intent is that you will be able to use them as a model to create your own custom queries.

Custom Report Parameters

Operations Manager reports contain a parameter area that allows users to specify the bounds of the database query with respect to target objects, monitoring times, and other options. You can define what parameters are available in your custom report, and populate those parameters with the default data that users are most likely to want. This section explains how to customize the parameter area of your custom report.

Localizing Reports

This section describes how to localize management packs for multiple language support.

Using Stored Procedures in a Custom Report

Stored procedures provide more convenience and security than using embedded Transact-SQL queries in a report, but they also require some additional configuration. This section describes how to create stored procedures, use them in reports, and create scripts to enable stored procedures to be distributed with management packs.

Using a Report Builder

Unlike linked reports, custom reports require that you design the report using a report designer, either in SQL Server Business Intelligence Development Studio (BIDS), Visual Studio Report Developer, or SQL Server Report Builder. This section explains how to use BIDS to create clear and useful reports.