Creating nth percentile calculations

Analytics allows you to create nth percentile calculations that can be added to a report. The nth percentile is the smallest value in the set of raw data with the property that n% of the data values are less than or equal to it. You need to define several parameters in order for the calculation to provide the targeted information you need:
  • Value of percentile desired (e.g., 95th percentile)
  • Number of days of data to aggregate (e.g., prior 30 days, this value is bounded by the raw_retention_days value)
  • Business hours to calculate within (if defined, otherwise the default 24-hour "shift" is used)
  • Job filter (also known as a group filter) to limit the calculation to certain devices (otherwise all devices are included)
  • Metrics filter to limit the calculation to certain metrics (otherwise all metrics are used)

The creation of rows for the nth percentile calculation is performed manually by inserting records into the reporting database. The tables of interest are as follows:

  • nth_percentile_config
    The main table for defining the percentile jobs.
    • percentile - Specifies the nth value to calculate; e.g., the 95th.
    • period - Specifies the number of days of data to use for the nth percentile calculation
    • name - Not used for the nth percentile; only exists for integration with an interface.
    • description - Not used for the nth percentile; only exists for integration with an interface.
  • business_hours_config

    Specifies the time ranges that has been defined as "business day" or "shift".

  • nth_percentile_business_hours_assoc
    Relates nth_percentile_config records with business_hours_config records.
    • nth_percentile_config_id - Specifies the nth_percentile_config foreign key.
    • business_hours_config_id - Specifies the business_hours foreign key.
  • group_filter

    Optional filter used to reduce the set of devices included in the aggregation.

  • nth_percentile_group_assoc
    Relates nth_percentile_config records with group_filter records.
    • nth_percentile_config_id - Specifies the nth_percentile_config foreign key.
    • group_filter_id - Specifies the group_filter foreign key.
  • meta_metric

    Optional filter used to reduce the set of metrics included in the aggregation.

  • nth_percentile_metric_assoc
    Relates nth_percentile_config records with meta_metric records.
    • nth_percentile_config_id - Specifies the nth_percentile_config foreign key.
    • metric_key - Specifies the meta_metric foreign key.
For each aggregate table to be projected, the table name from which to read the data is constructed as follows:
nth_percentile_<nth_percentile_config.name><business_hours_config.name, if any><meta_metric.metric_name>
The table name to which to write data is constructed as follows:
proj_<projection_config.name><nth_percentile_config.name, if any><business_hours_config.name, if any>_<meta_metric.metric_name>