Public views containing rolling statistics
This topic provides information about public views containing rolling statistics.
PV_SYS_DATA_ROLL and PV_BDRV_DATA_ROLL views
For convenience in creating data marts, some special statistics for a certain subset of metrics are pre-calculated on a regular basis and made available through these "rolling statistics" views. Right-click and select Save link as to download the Excel file that includes the list of metrics.
For information about the public views needed for creating data marts, see Accessing Capacity Optimization data using Public Views. The views described here are additional public views that pre-calculate some commonly needed statistics: percentile statistics and linear regression parameters. These views are called ROLL views because their calculations are based on a rolling window of data.
These two ROLL views are identical except that the first contains data for Systems while the second contains data for Business drivers. We describe below the PV_SYS_DATA_ROLL view. The description for the BDRV view simply replaces SYS with BDRV.
PV_SYS_DATA_ROLL contains percentile statistics and linear regression parameters, for systems for a selected sub-set of metric instances. Each row contains the latest data only; it is refreshed each day by a back-end service.
The set of metric instances is selected based on certain meaningful combinations of Entity Types (SYSTYPEID) and Metrics (METRIC). For these selected metric instances, hourly data samples for the previous so many consecutive days (e.g., 30 days) are used to compute these statistics. For each metric, the meaning of the metric is used to decide whether to use the AVERAGE or MAXIMUM sample as the basis for computations. For CONF type metrics, the VALUE is used and converted to a number. For the meanings of these terms, see Metric values and summarization.
The key columns for this view are:
- SYSMETRICID: An internal BMC TrueSight 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.
For example: Select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_MHZ' ROLLPERIOD: The number of consecutive calendar days previous to the time of calculation, whose hourly data is used to compute the statistics. Roll periods of 30 days, 7 days, and 1 day are used for some metrics, while for others, only a 30 day period is used.
The [confluence_table-plus] macro is a standalone macro and it cannot be used inline. Click on this message for details.
The percentile statistics and linear regression model are useful representations of the hourly sample data available in the roll period. The back-end service tries to keep the statistics and model valid for as long as possible. Each day that the service runs, it checks to see if any new hourly data has been loaded into the data warehouse from the time of the last update. If it finds no new hourly data for any metric instance, it keeps each row of that metric instance unchanged. Thus, you can use the UPDATETS field to understand how old the computation is, and the combination of TS and UPDATETS to understand the exact time window whose hourly samples have been used.