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.

Was this page helpful? Yes No Submitting... Thank you

Comments