Rollup

June 5th, 2020


Rollups are used to make sure a reasonable amount of data is displayed on charts and point value history tables. Rollups work with a time period and a time period type. For example you can rollup the data into 30 Day average values. The rollups available for a data point depend on the data type.

Each Data Point has an option to specify a default rollup which will be used throughout the UI. You can manually override the default roll up on the [date bar][1]. Also see below about the Simplify option which is powerful alternative to rollups for numeric Data Points

When Editing a Data Point you can specify the default Rollup, Plot type, and Simplify type to use on charts throughout the MangoUI. This is very useful as it allows you to display multiple data points with different rollups on the same chart.

Data point rollup

Some example use cases would be:

Counter value that increments up such as kWh or water meter readings - Rollup type would be delta to display the actual consumption in a time period.

Totalized Value to give the total amount consumed in a period - Rollup type would be sum to display the amount consumed over a period of time

Variable Analog signal such as Temperature, kW, or Pressure - Rollup type would be Average, Min or Max. Better results might be achieved by using Rollup of None and Simplify.

Rollup Axioms

  1. A starting time is inclusive, an ending time is exclusive
  2. Every period starts with the value that existed prior to the period as though timestamped at the start of the period and not before, unless there is a value at the exact start time of the period.
  3. Periods with no value prior to the start time have a NULL startValue
  4. No value prior or within the period all statistics are NaN except count and sum which are 0 and First (value and time) and Last (value and time) which are NULL
  5. No statistic will ever require a value in the next time period
  6. The last value added to the statistics (currently the done method) is the last value within the period.
  7. The end value is the last value unless it doesn’t exist, in which case it’s the start value
  8. Previous value is the latest value prior to the period

Date Truncation

Rollup queries can have thier from and to times truncated to round date/time values using the trucate=true parameter. When truncating a date/time all smaller period types are set to 0. Truncation by the various period types is as follows.

Period Description
Years date - (periods % year of century)
Months date - (periods % month of year)
Weeks date - (periods % aligned week of year) Where the aligned week of year represents concept of the count of weeks within the period of a year where the weeks are aligned to the start of the year. For example, in a calendar systems with a seven day week, the first aligned-week-of-year starts on day-of-year 1, the second aligned-week starts on day-of-year 8, and so on. Thus, day-of-year values 1 to 7 are in aligned-week 1, while day-of-year values 8 to 14 are in aligned-week 2, and so on.
Days date - (day of year % periods)
Hours date - (hour of day % periods)
Minutes date - (minute of hour % periods)
Seconds date - (second of minute % periods)
Milliseconds date - (millisecond of second % periods)

Numeric Rollups

Rollup Description
NONE No rollup, return raw values
ACCUMULATOR The last value minus the previous value
AVERAGE The start value and all values in the period weighted by time
COUNT The number of sampled or logged values within the period
DELTA The change in point value within the period. 0 values and no start value: NaN, 0 values w/ start value: 0, 1 value with start value could be anything, 1 value with no start value is 0
FIRST The first logged value within the period
INTEGRAL The integral of the values during the period, taking into consideration the start value. The weighted sum of values by their durations
LAST The last logged value within the period
MINIMUM The largest value of any values within the period, taking into consideration the start value
MAXIMUM The smallest value of any values within the period, taking into consideration the start value
START The value before or exactly at the start of the period
SUM The total of all values within the period, doesn’t count the start value unless the start value occurred exactly at period start

Non Numeric Rollups

Rollup Description
NONE No rollup, return raw values
ACCUMULATOR The last value minus the previous value
AVERAGE The start value and all values in the period weighted by time
COUNT The number of sampled or logged values within the period
DELTA The change in point value within the period. 0 values and no start value: NaN, 0 values w/ start value: 0, 1 value with start value could be anything, 1 value with no start value is 0
FIRST The first logged value within the period
INTEGRAL The integral of the values during the period, taking into consideration the start value. The weighted sum of values by their durations
LAST The last logged value within the period
MINIMUM The largest value of any values within the period, taking into consideration the start value
MAXIMUM The smallest value of any values within the period, taking into consideration the start value
START The value before or exactly at the start of the period
SUM The total of all values within the period, doesn’t count the start value unless the start value occurred exactly at period start

Simplify Options

The purpose of the Simplify option is to reduce the data while keeping the same overall shape of the line. In many cases this can dramatically improve charting while still maintaining the overall important high and low values. The target value for Simplify is the number of data points it will attempt to display. If you have 40,000 point values in a month and your target is 1000 then the algorithm will attempt to simply it to 1000 points. You can optional use the tolerance to fine tune the algorithm to remove data samples within a specific tolerance range. The below animation illustrates how simplify works.

Simplify-animation.gif

Using Rollups

Help-ScreenShots-DateBar-RollupControls.png

Rollup settings will be shown in the Date Bar if they can be used by the current page. For instance, rollup settings will not show on the Events Page as they do not apply to that page.

By default Auto rollup settings will be used but if you turn off Auto rollup you can manually specify a rollup interval. Changing Rollup type allows you to view rollups other than Average, such as Delta, Maximum and Minimum. If you have a data point selected on the Data Point Details page which is not Numeric, the Rollup type will automatically be set to None and only certain types will be available.

If you don’t manually specify a date range, then you are using one of the preset ranges in the Header Bar (such as "Last 5 minutes") you will see the update interval options.

When Auto update is checked, the time range will refresh on the given Update interval and re-query values to use in the charts and history tables. Whether Auto update is selected or not, you can click the reload icon on the far right of the Date bar to cause a refresh of the time range selected and load the latest data:

Copyright © 2020 Radix IoT, LLC.