Setting up the Environment

  • Article

Applies To: Operations Manager 2007 R2

Operations Manager 2007 Reporting requires that you install Microsoft SQL Server 2005 or SQL Server 2008 and SQL Server Reporting Services (SSRS). Because Operations Manager 2007 Reporting includes SQL Server Reporting Services, you do not need to install it separately. For more information about Operations Manager 2007 Reporting, see the Operations Manager 2007 Deployment Guide in the System Center Operations Manager 2007 R2 Technical Library. The default configuration of SQL Server 2005 and SQL Server 2008 includes Business Intelligence Development Studio (BIDS).

BIDS is a subset of Microsoft Visual Studio 2008 designed specifically for use with SQL Server 2008. It has various tools for SQL Server users including the robust Report Designer. You can create custom reports for use in Operations Manager with a variety of tools that include SQL Server Reporting Services Report Builder, Visual Studio, and Microsoft Excel. This guide focuses on BIDS, but you can use the tool that you are most comfortable with. For more information about the tools in BIDS, see Introducing Business Intelligence Development Studio in the SQL Server 2008 R2 Books Online (https://technet.microsoft.com/en-us/library/ms173767.aspx).

If you already have Visual Studio 2008 installed on your Operations Manager Reporting server, Business Intelligence Design Studio uses Visual Studio instead.

A common practice for authoring reports is to create a read-only account in SQL Server that enables multiple authors to access the OperationsManagerDW database to test their reports without giving each author individual access. To create a read-only data source, you first must create a database user that only has Read permissions to the OperationsManagerDW database.

To create a database user that has only Read permission

  1. Open Microsoft SQL Server Management Studio (SSMS) and connect to the database server.

  2. In Object Explorer, right-click the server name, and then click Properties. When the Server Properties dialog box opens, click the Security tab.

  3. In the Server authentication section, ensure that SQL Server and Windows Authentication mode is selected. Click OK. You might have to restart SQL Server for the change to take effect. Close the Server Properties dialog box.

  4. In Object Explorer, expand Security, right-click Logins, and then click New login. The Login – New dialog box appears. Enter a login name, select SQL Server Authentication, and enter a password. Clear the User must change password at next login check box.

  5. Select the User Mapping page on the left. In the Users mapped to this login box, select OperationsManagerDW. The box at the bottom of the dialog box changes to Database role membership for: OperationsManagerDW. Select the db_datareader role. Click OK.

As an alternative to creating a database user that has Read-Only permission, especially in environments where operating in mixed mode is not desired, you can create a login for each operator’s Active Directory account, and then grant that login db_datareader role membership on the OperationsManagerDW database.

To set up BIDS to create reports, you must create a new data source to connect to the reporting data warehouse.

To create a new report project

  1. On the Start menu, point to SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.

  2. On the Start page, click File, click New, and then click Project, or click the Create: Project link. This opens the New Project dialog box.

  3. In the Project types pane, ensure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project. In the Name box, enter a name for the project. Change the location where the project is stored or accept the default. Click OK.

The new report project is now ready for you to create a data source.

To create a new data source

  1. After you create a new report project, the Solution Explorer and Properties panes appear on the right side of the Start page. In the Solution Explorer, right-click Shared Data Sources, and then click Add New Data Source.

  2. In the Shared Data Source Properties dialog box, enter a Name for the data source. Leave the Type set to Microsoft SQL Server. Click the Edit button next to the Connection String. The Connection Properties dialog box appears.

  3. In the Server name box, enter the name of the server where the reporting data warehouse is installed. If you created a user that has Read-Only permissions, in the Log on to the Server section, select the Use SQL Server Authentication button, and enter the credentials for the SQL Server Login that you created. If not, use whatever authentication is appropriate in your environment. In the Connect to a Database section, select the Select or enter a database name button, and then select OperationsManagerDW from the box. If you do not see OperationsManagerDW, verify that you have selected the correct server and authentication.

  4. Click OK. The connection string in the Shared Data Source Properties dialog box will be filled in for you. Click OK to close this dialog box. The data source now appears in the Properties pane, with the extension .rds.

  5. To make the data source available to other projects, right-click the data source in the Solution Explorer and click Properties. The Property Pages window for this data source opens. Click TargetServerURL and enter the URL for the report server where the project is deployed. Click OK.

  6. Right-click the data source in the Solution Explorer and click Deploy. The Output window shows the progress of the deployment.

The data source is now ready to use with a custom report.