# 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 here *

*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.Column Name

Data Type

Description

Key column

SYSMETRICID

NUMBER

System metric ID

PK

ROLLPERIOD

NUMBER

Roll period reports the roll-up period. If it is less than 10000, it is expressed in DAYS. If it is greater than 10000, it is expressed in the number of hours as ROLLPERIOD-10000 (for instance, if it is 10024, it will be expressed as 10024-10000 = 24h).

PK

TS

DATE

The start of the roll period for this metric instance

UPDATETS

DATE

The end of the roll period for this metric instance, which is also the time at which the statistics were calculated

DURATION

NUMBER

The actual total validity duration of the hours that were available and were used for the computation, in seconds

COUNT

NUMBER

The actual number of hourly samples that were available and were used for the computation

MIN

NUMBER

Minimum sample value found in the hourly samples

MAX

NUMBER

Maximum sample value found in the hourly samples

SLOPE

NUMBER

Daily rise (slope) found in the hourly samples. Calculated using robust linear regression whenever the JAVA option is found in the Oracle instance

INTERCEPT

NUMBER

The intercept of hourly samples calculated at the "TS" date

PCTILE95

NUMBER

The 95th discrete percentile of the hourly samples

PCTILExx

NUMBER

Similar percentiles for xx = 05, 10, 25, 50, and 75 of the hourly samples.

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.

## Comments