This article by the lead author Samuel Erskine, along with the co-authors Dieter Gasser, Kurt Van Hoecke, and Nasira Ismail, of the book Microsoft System Center Reporting Cookbook, discusses the drivers of organizational reporting and the general requirements on how to plan for business valued reports, steps for planning for the inputs your report data sources depends on, how you plan to view a report, the components of the System Center product, and preparing your environment for self-service Business Intelligence (BI). A report is only as good as the accuracy of its data source. A data source is populated and updated by an input channel.

In this article, we will cover the following recipes:

• Understanding the goals of reporting
• Planning and optimizing dependent data inputs
• Planning report outputs
• Understanding the reporting schemas of System Center components
• Configuring Microsoft Excel for System Center data analysis

(For more resources related to this topic, see here.)

Understanding the goals of reporting

This recipe discusses the drivers of organizational reporting and the general requirements on how to plan for business valued reports.

Getting ready

To prepare for this recipe you need to be ready to make a difference with all the rich data available to you in the area of reporting. This may require a mindset change; be prepared.

How to do it...

The key to successfully identifying what needs to be reported is a clear understanding of what you or the report requestor is trying to measure and why.

Reporting is driven by a number of organizational needs, which may fall into one or more of these sample categories:

• Information to support a business case
• Audit and compliance driven request
• Budget planning and forecasting
• Current operational service level

These categories are examples of the business needs which you must understand. Understanding the business needs of the report increases the value of the report. For example, let us expand on and map the preceding business scenarios to the System Center Product using the following table:

In a majority of cases for System Center administrators, the requestor does not provide the business objective. Use the preceding table as an example to guide your understanding of a report request.

How it works...

Reporting is a continual life cycle that begins with a request for information and should ultimately satisfy a real need. The typical life cycle of a request is illustrated in the following figure:

The life cycle stages are:

• Report conception
• Report request
• Report creation
• Report enhancement/retirement

The recipe focuses on the report conception stage. This stage is the most important stage of the life cycle. This is due to the fact that a report with a clear business objective will deliver the following:

• Focused activities: A report that does have a clear objective will reduce the risk of wasted effort usually associated with unclear requirements.
• Direct or indirect business benefit: The reports you create, for example using System Center data, ultimately should benefit the business.

An additional benefit to this stage of report planning is knowing when a report is no longer required. This would reduce the need to manage and support a report that has no value or use.

Planning and optimizing dependent data inputs

A report is only as good as the accuracy of its data source. A data source is populated and updated by an input channel. This recipe discusses and provides steps for planning for the inputs your report data source(s) depends on.

Getting ready

Review the Understanding the goals of reporting recipe as a primer for this recipe.

How to do it...

The inputs of reports depend on the type of output you intend to produce and the definition of the accepted fields in the data source. An example is a report that would provide a total count of computers in a System Center Configuration Manager environment. This report will require an input field which stores a numeric value for computers in the database.

Here are the recommended steps you must take to prepare and optimize the data inputs for a report:

1. Identify the data source or sources.
2. Document the source data type properties.
3. Document the process used to populate the data sources (manual or automated process).
4. Agree the authoritative source if there is more than one source for the same data.
5. Identify and document relationship between sources.
6. Document steps 1 to 5.

The following table provides a practical example of the steps for a report on the total count of computers by the Windows operating system. Workgroup computers and computers not in the Active Directory domain are out of scope of this report request.

Plan to document the specific fields you need from the authoritative data source. For example, use a table similar to the following.

The steps provided discusses an example of identifying input sources and the fields you plan to use in a requested report.

Optimizing Report Inputs

Once the required data for your reports have been identified and documented, you must test for validity and consistency. Data sources which are populated by automated processes tend to be less prone to consistency errors. Conversely data sources based on manual entry are prone to errors (for example, correct spelling when typing text into forms used to populate the data source). Here are typical recommended practices for improving consistency in manual and automated system populated data sources:

• Automated (for example, agent based):
  1.     Implement agent health check and remediation.
  2.     Include last agent update information in reports.
• Manual entry:
  1.     Avoid free text fields, except description or notes.
  2.     Use a list picker.
  3.     Implement mandatory constraints on required fields (for example, a request for e-mail address should only accept the right format for e-mail addresses.

How it works...

The reports you create and manage are only as accurate as the original data source. There may be one or more sources available for a report. The process discussed in this recipe provides steps on how to narrow down the list of requirements. The list must include the data source and the specific data fields which contain the data for the proposed report(s). These input fields are populated by manual, automated processes or a combination of both.

The final part of the recipe discussed an example of how to optimize the inputs you select.

These steps will assist in answering one of the typical questions often raised about reports: "Can we trust this information?" The answer, if you have performed these steps will be "Yes, and this is why and how."

Planning report outputs

The preceding recipe, Planning and optimizing dependent inputs, discussed what you need for a report. This recipe builds on the preceding recipes with a focus on how you plan to view a report (output).

Getting ready

Plan to review the Understanding the goals of reporting and Planning and optimizing dependent inputs recipes.

How to do it...

The type of report output depends on the input you query from the target data source(s). Typically, the output type is defined by the requestor of the report and may be in one or more of these formats:

• List of items (tables)
• Charts (2D, 3D, and formats supported by the reporting program)
• Geographic representation
• Dials and gauges
• A combination of all the listed formats

Here is an example of the steps you must perform to plan and agree the reporting output (s):

1. Request the target format from the initiator of the report.
2. Check the data source supports the requested output.
3. Create a sample dataset from the source.
4. Create a sample output in the requestor's format(s).
5. Agree a final format or combination of formats with the requestor.

The steps to plan the output of reports are illustrated in the following figure:

These are the basic minimal steps you must perform to plan for outputs.

How it works...

The steps in this recipe are focused on scoping the output of the report. The scope provides you with the following:

• Ensuring the output is defined before working on a large set of data
• Validating that the data source can support the requested output
• Avoids scope creep. The output is agreed and signed off

The objective is to ensure that the request can be satisfied based on what is available and not what is desired. The process also provides an additional benefit of identifying any gaps in data before embarking on the actual report creation.

There's more...

When planning report outputs, you may not always have access to the actual source data. The recommend practice is not to work directly with the original source even if this is possible to avoid negatively impacting the source during the planning stage. In either case, there are other options available to you. One of these options is using a spreadsheet program such as Microsoft Excel.

Mock up using Excel

An approach to testing and validating report outputs is the use of Microsoft Excel. You can create a representation of the input source data including the data type (numbers, text, and formula). The data can either be a sample you create yourself or an extract from the original source of the data.

The added benefit is that the spreadsheet can serve as a part of the portfolio of documentation for the report.

Understanding the reporting schemas of System Center components

The reporting schema of the System Center product is specific to each component. The components of the System Center product are listed in the following table:

This recipe discusses the reporting capabilities of each of these components.

Getting ready

You must have a fully deployed configuration of one or more of the System Center product components. Your deployment must include the reporting option provided for the specific component.

How to do it...

The reporting capability for all the System Center components is rooted in their use of Microsoft SQL databases. The reporting databases for each of the components is listed in the following table:

Use the steps in the following sections to view the schema of the reporting database of each of the System Center components.

Configuration Manager

Use the following steps:

1. Identify the database server and instance of the Configuration Manager site.
2. Use Microsoft SQL Server Management Studio (MSSMS) to connect to the database server. You must connect with a user account with the appropriate permission to view the Configuration Manager database.
3. Navigate to Databases | CM_<site code> | Views, as shown in the following screenshot:

   

4. The views listed form the reporting schema for the System Center Configuration Manager component. Note that not all the views are listed in the screenshot.

Data Protection Manager

Use the following steps:

1. Identify the database server and SQL instance of the Data Protection Manager environment.
2. Use MSSMS to connect to the database server. You must connect with a user account with the appropriate permission to view the Configuration Manager database.
3. Navigate to Databases | DPMDB_<Server Name> | Views, as shown in the following screenshot:

   

4. The views listed form the reporting schema for the System Center Data Protection Manager component. Note that not all the views are shown in the screenshot.

Orchestrator

Use the following steps:

1. Identify the database server and instance of the Orchestrator instance server.
2. Use MSSMS to connect to the database server. You must connect with a user account with the appropriate permission to view the Orchestrator database.
3. Navigate to Databases | Orchestrator | Views, as shown in the following screenshot:

   

4. The views listed form the reporting schema for the System Center Orchestrator component.

Operations Manager

Use the following steps:

1. Identify the database server and instance of the Operations Manager management group.
2. Use MSSMS to connect to the database server. You must connect with a user account with the appropriate permission to view the Operations Manager data warehouse reporting database.
3. Navigate to Databases | OperationsManagerDW | Views, as shown in the following screenshot:

   

4. The views listed form the reporting schema for the System Center Operations Manager component. Note that not all the views are listed in the screenshot.

Service Manager

Use the following steps:

1. Identify the database server and instance of the Service Manager data warehouse management group.
2. Use MSSMS to connect to the database server. You must connect with a user account with the appropriate permission to view the Service Manager data warehouse database.
3. Navigate to Databases | DWDataMart | Views, as shown in the following screenshot:
4. The views listed form the reporting schema for the System Center Configuration Manager component. Note that not all the views are listed in the screenshot.

   

Virtual Machine Manager

Perform the following steps:

1. Identify the database server and instance of the Virtual Machine Manager server.
2. Use MSSMS to connect to the database server. You must connect with a user account with the appropriate permission to view the Virtual Machine Manager database.
3. Go to Databases | VirtualManagerDB | Views, as shown in the following screenshot:

   

4. The views listed form the reporting schema for the System Center Configuration Manager component. Note that not all the views are listed in the screenshot.

How it works...

The procedure provided is a simplified approach to gain a baseline of what may seem rather complicated if you are new to or have limited experiences with SQL databases. The view for each respective component is a consistent representation of the data that you can retrieve by writing reports. Each view is created from one or more tables in the database. The recommended practice is to target report construction at the views, as Microsoft ensures that these views remain consistent even when the underlying tables change.

An example of how to understand the schema is as follows. Imagine the task of preparing a meal for dinner. The meal will require ingredients and a process to prepare it. Then, you will need to present the output on a plate. The following table provides a comparison of this scenario to the respective schema:

In addition to using MSSMS, as described earlier, Microsoft supplies schema information for the components in the online documentation. This information is specific for each product and varies in the depth of the content. The See also section of this recipe provides useful links to the available information published for the schemas.

There's more...

It is important to understand the schema for the System Center components, but equally important are the processes that populate the databases. The data population process differs by component, but the results are the same (data is automatically inserted into the respective reporting database). The schema is a map to find the data, but the information available is provided by the agents and processes that transfer the information into the databases.

Components with multiple databases

System Center Service Manager and Operations Manager have a similar architecture. The data is initially written to the operational database and then transferred to the data warehouse. The operational database information is typically what is available to view in the console. The operational information is, however, not the best candidate for reporting, as this is constantly changing. Additionally, performing queries against the operational database can result in performance issues. You may view the schema of these databases using a process similar to the one described earlier, but this is not recommended for reporting purposes.

See also

• The official online documentation for the schema is updated when Microsoft makes changes to the product, and it should be a point for reference at http://technet.microsoft.com/en-US/systemcenter/bb980621.

Configuring Microsoft Excel for System Center data analysis

This recipe is focused on preparing your environment for self-service Business Intelligence (BI).

Getting ready

Self-service BI in Microsoft Excel is made available by enabling or installing add-ins. You must download the add-ins from their respective official sites:

• Power Query: Download Microsoft Power Query for Excel from http://www.microsoft.com/en-gb/download/details.aspx?id=39379.
• PowerPivot: PowerPivot is available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013.
• Power View: Power View is also available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013.
• Power Maps: At the time of writing this article, this add-in can be downloaded from the Microsoft website. Power Map Preview for Excel 2013 can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=38395.

How to do it...

The tasks discussed in this recipe are as follows:

• Installing the Power Query add-in
• Installing the Power Maps add-in
• Enabling PowerPivot and Power View in Microsoft Excel

Installing the Power Query add-in

The Power Query add-in must be installed using an MSI installer package that is available at Microsoft Download Center. The installer deploys the bits and enables the add-in in your Excel installation.

The functionality in this add-in is regularly improved by Microsoft. Search for Microsoft Power Query for Excel in Download Center for the latest version. The add-in can be downloaded for 32-bit and 64-bit Microsoft Excel versions.

Follow these steps to install the Power Query add-in:

1. Review the system requirements on the download page and update your system if required. Note that when you initiate the setup, you may be prompted to install additional components if you do not have all the requirements installed.
2. Right-click on the MSI installer and click on Install.
3. Click on Next on the Welcome page.
4. Accept the License Agreement and click on Next.
5. Accept the default or click on Change to select the destination installation folder.
6. Click on Next. On the Ready to Install Microsoft Power Query for Excel page, click on Install.
7. The installation progress is displayed. Click on Finish on the Installation Completed page.
8. The Power Query tab is available on the Excel ribbon after this installation.

Installing the Power Map add-in

The Power Map add-in must be installed using an executable (.exe) installer package that is available at Microsoft Download Center.

The functionality in this add-in also is regularly improved by Microsoft. Search for Microsoft Power Map for Excel in the Download Center for the latest version.

Follow these steps to install the Power Map add-in:

1. Review the system requirements on the download page and update your system if required.
2. Double-click on the EXE installer (Microsoft Power Map Preview for Excel) and click on Yes if you get the User Access Control dialog prompt.
3. When prompted to install Visual C++ 2013 Runtime Libraries (x86), click on Close under Install. Check to agree to the terms and click on Install.
4. Click on Next on the Welcome page.
5. Click on the I Agree radio button on the License Agreement page, and then click on Next.
6. Accept the default folder or click on Browse to select a different destination installation folder. Make your selection on who the installation should be made available to: Everyone or Just me. Click on Next.
7. Click on Next. On the Confirm Installation page, click on Next.
8. The installation progress is displayed. Click on Close on the Installation Completed page.
9. The Power Map task will be made available in the Insert tab on the Excel ribbon after this installation.

Enabling PowerPivot and Power View in Microsoft Excel

Perform the following steps in Microsoft Excel to enable PowerPivot and Power View:

1. In the File menu, select Options.
2. In the Add-Ins tab, select COM Add-Ins from the Manage: dropdown at the bottom and click on the Go... button, as shown in this screenshot:

   

3. Select the Power add-ins from the list of Add-Ins available, as shown in the following screenshot:

   

4. Click on OK to complete the procedure of enabling add-ins in Microsoft Excel. After you've enabled the required add-ins, the different types of add-in tasks and tabs should be available on the Excel ribbon, as shown in this screenshot:

   

5. This procedure can be used to enable or disable all the available Excel add-ins.

You are now ready to explore System Center data, create queries, and perform analysis on the data.

How it works...

The add-ins for Microsoft Excel provide additional functionality to gather and analyze System Center data. Wizards can be added, interfaces can be made available to combine different sources, and a common language, Data Analysis SyntaX (DAX), can be made available for calculations and performing different forms of visualizations.

The steps discussed in this recipe are required for the use of the Power BI features and functionality using Microsoft Excel. You followed the steps to install Power Query and Power Map, and you enabled PowerPivot and Power Views. These add-ins provide the foundation for self-service Business Intelligence using Microsoft Excel.

See also

• Different types (enhanced) of functionality and integrations are available for you when you use Microsoft SQL Server or SharePoint, which are not discussed in this article. Refer to http://office.microsoft.com for additional information on them.

Summary

In this article, we covered the goals of reporting and how to plan and optimize dependent data inputs. We also discussed planning of report outputs, the reporting schemas of System Center components, and configuring Microsoft Excel for System Center data analysis.

Resources for Article:

Further resources on this subject:

• Adding and Importing Configuration items in System Center 2012 Service Manager [article]
• Mobility [article]
• Upgrading from Previous Versions [article]