Accessing data using public views

A data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts can be used to prepare data for a custom view or report. For information about managing data marts, see SQL-based data marts.

Data marts rely on a basic set of views that hide the BMC Helix Capacity Optimization data model and expose a more report-oriented view over data, known as public views. The Advanced Reporting module produces a number of public views that can be accessed using the CPIT_REP account (the default database user account for generating reports).

These views let you access relevant BMC Helix Capacity Optimization data in a simple and straightforward manner. The following sections describe these views in detail.

To view example queries developed using these public views, see Query Examples.

Public views produced by the Advanced Reporting module

Public views produced by the Advanced Reporting module that can be accessed using the CPIT_REP account are divided into the following categories:

System-related views

PV_SYSTEM

Each System is created by a connector, and it remains in BMC Helix Capacity Optimization until either all of its data ages out, or the System and all of its data is explicitly dismissed and deleted by a user.
This view contains one row for each system, keyed by its unique entity ID (SYSID).

PV_SYS_METRIC

This view contains information on metric instances belonging to systems.

A metric instance contains time-varying data, either as a time series of numbers that can change on a frequent interval, or as a configuration string that changes only occasionally. For more information on metrics and metric instances, see Capacity-Agent-metric-groups-and-metrics.

A metric instance is created when data for a particular metric for a System is first loaded by a connector. It remains in BMC Helix Capacity Optimization until its owning system is either aged out or dismissed and deleted by a user.

This view does not contain the time series or configuration data itself. Time series data is in the PV_SYS_DATA_DETAIL and similar views. Configuration data is in PV_SYS_CONF_LAST and PV_SYS_CONF_VARIATION views.

This view contains one row per metric instance, keyed by SYSMETRICID.

The 5-tuple structure of a metric instance listed in Capacity-Agent-metric-groups-and-metrics is a synonym table for the above view, with key series attributes corresponding to the above column names as follows:

This table shows how connector data corresponds to the public view.

PV_SYS_EVENT

This view contains information on system events.

PV_SYS_METRIC_EVENT

This view contains information on events owned by system metrics.

PV_SYS_CONF_VARIATION

This view describes historical changes in configuration metric instances (CONF). See how CONF metrics are recorded in BMC Helix Capacity Optimization, in the page Capacity-Agent-metric-groups-and-metrics.

This view shows every past value of each configuration metric instance. If you are trying to extract the current values of configuration metric instances, you probably want to use the view PV_SYS_CONF_LAST below. That view contains only the last value of the configuration parameters.

The key column in this view is SYSMETRICID, which is an internal BMC Helix Capacity Optimization-generated unique number assigned to each metric instance.

To get the value of SYSMETRICID we need to use PV_SYS_METRIC view. We can run select query on PV_SYS_METRIC with inputs of sysid and metric.

E.g. select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_NUM'

Here sysid can be retrieved from console by mouse over on the required system Or user can query on PV_SYSTEM view to get the sysid.

Apart from the changes in system configuration metrics values, if you want to check the historical changes in workload configuration metrics you can use PV_BDRV_CONF_VARIATION view.

PV_SYS_CONF_LAST

This view reports only the last value of system configuration metric instances.

See how CONF metrics are recorded in BMC Helix Capacity Optimization, in the page Capacity-Agent-metric-groups-and-metrics.

If you are looking for past values, use the view PV_SYS_CONF_VARIATION above.

The key column is SYSMETRICID, which is an internal BMC Helix Capacity Optimization-generated unique number assigned to each metric instance.

To get the value of SYSMETRICID we need to use PV_SYS_METRIC view. We can run select query on PV_SYS_METRIC with inputs of sysid and metric.

E.g. select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_NUM'

Here sysid can be retrieved from console by mouse over on the required system Or user can query on PV_SYSTEM view to get the sysid.

Apart from the last value of system configuration metric if you want to check the last value of workload configuration metrics you can use PV_BDRV_CONF_LAST view.

PV_SYS_DATA_DETAIL

This view contains system data in its original state. It does not have any default resolution.

The key column for this view is SYSMETRICID, which is an internal BMC Helix Capacity Optimization generated unique number assigned to each metric instance.

To get the value of SYSMETRICID we need to use PV_SYS_METRIC view. We can run select query on PV_SYS_METRIC with inputs of sysid and metric.

E.g. select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_MHZ'

Here sysid can be retrieved from console by mouse over on the required system Or user can query on PV_SYSTEM view to get the sysid.

The recommended aggregation to preserve data semantics in this view is explained in the following code:

SUM(duration) AS duration,
DECODE(SUM(duration),0,0,SUM(avgvalue*duration)/SUM(duration)) AS avgvalue,
MIN(minvalue) AS minvalue,
MAX(maxvalue) AS maxvalue,
SUM(sumvalue) AS sumvalue,
SUM(sumquadvalue) AS sumquadvalue,
SUM(countgood) AS countgood,
SUM(countwarn) AS countwarn,
SUM(countpoor) AS countpoor,
SUM(counterror) AS counterror,
SUM(countvalue) AS countvalue,
SUM(weight) AS weight,
DECODE(SUM(weight),0,0,SUM(wavgvalue*weight)/SUM(weight)) AS wavgvalue,

In order to compute the variance of data samples, you can apply the following statement to the result of a time aggregation query:

decode(countvalue,1,0,
(sumquadvalue/(countvalue-1)-power(avgvalue,2)*countvalue/(countvalue-1))

PV_SYS_DATA_DH

This view contains system data at hourly resolution, So for given date there will be 24 rows for given sysmetricid. This view has the structure of, and uses the same aggregation recommended for and present in the PV_SYS_DATA_DETAIL view.

The key columns for this view are SYSMETRICID,TS,DCLASSID,PCLASSID.

Here SYSMETRICID is an internal BMC Helix Capacity Optimization generated unique number assigned to each metric instance.

TS is the timestamp of the of the value available for metric instance.

DCLASSID: This column is calculated by the DWH starting from the sample ts and using the calendar definition. This can be used for filtering purposes i.e. to consider/exclude set of day classes.

PCLASSID: A user can manually mark some point in an analysis as regular outlier or relation outlier.

when a point is marked as outlier its contribution is separated from non oultier samples (pclassid=0) like the MDCH case
So also at hour level (when the outlier is marked at detail level and the original resolution is less than 1h) you can have two samples for the same hour

To get the value of SYSMETRICID we need to use PV_SYS_METRIC view. We can run select query on PV_SYS_METRIC with inputs of sysid and metric.

E.g. select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_MHZ'

Here sysid can be retrieved from console by mouse over on the required system Or user can query on PV_SYSTEM view to get the sysid.

PV_SYS_DATA_D

This view contains system data at daily resolution. This view has the same structure of, and uses the same aggregation as present in the PV_SYS_DATA_DETAIL view.