Creating custom reports

The Advanced Reporting node on the Administration tab allows you to achieve two primary objectives:

• Provides a set of features for designing, scheduling, and publishing reports that have a custom structure and presentation. For more information, see Creating-reports-and-report-templates.
• Provides an interface to BMC Helix Capacity Optimization data and model results to third-party reporting tools.

Overview of databases in BMC Helix Capacity Optimization

Before discussing specific concepts, it is useful to recall some common definitions.

A relational database stores information in data structures called tables. More specifically, a data warehouse (such as the BMC Helix Capacity Optimization DWH) uses two kinds of tables:

• Fact tables (FT): Contain all recorded data, also called observations.
• Dimension tables (DT): Contain all the information necessary to decode keys used in fact tables to identify data samples; decode keys are also called analysis dimensions.

Dimension tables have a simple primary key, while fact tables have a compound primary key consisting of the aggregate of relevant dimension keys.

An example of such a star schema is shown in the following figure:

Example of star schema

The BMC Helix Capacity Optimization database model is a relational star schema organized with fact tables at different time resolutions and dimension tables.

All tables containing the suffix _DATA in their names are fact tables, wherease tables identified by the same prefix but not suffixed with _DATA are the corresponding dimension tables.

Another important concept is the difference between horizontal and vertical tables:

• A horizontal table stores all data related to a single fact in one row; this practice saves disk space but requires to modify the table structure, should the collected data types change.
• A vertical table enforces a fixed structure (typically: timestamp, fact ID, name, value) and uses one row for each data value. This is more space-consuming but allows for greater flexibility.

The following table is an example of a vertical table:

The following table is an example of a horizontal table:

Vertical tables can be transformed into horizontal ones.

The BMC Helix Capacity Optimization DWH schema is based on a vertical structure; should a horizontal structure be required for exporting and reporting, the AR module offers several ways to export data in a horizontal format.

There are two main kinds of fact tables, identified by the prefixes SYS and WKLD: one for system and the other for business driver observations. The main dimensions used by BMC Helix Capacity Optimization are: entity, object, subobject, and location; for more details, refer to Maintaining-ETL-tasks.

The basic structure that BMC Helix Capacity Optimization uses to collect counters is shown in the following figure:

The BMC Helix Capacity Optimization data model

Where to go from here

The Advanced Reporting module produces a number of public views that can be accessed using the CPIT_REP account. For more information on these public views, see Accessing-data-using-public-views.