Time Series Aggregation

In this article, we are launching a series of publications on specific tasks to be addressed as part of the implementation of AggreGate, which we hope will be able to help you in your real projects.

Some time ago, our partner in the manufacture and maintenance of integrated power supply systems for base stations was contacted by a mobile operator in Africa. The project was designed for 1000-2000 base stations. As part of this project, AggreGate was used to monitor, manage, visualize and build reports. During the operation of the system, reports were required, with which you can quickly see the main parameters of the equipment. These reports were subsequently used to formulate the KPI of the power supply system, select the optimal parameters, determine the maintenance schedule and build the route for the engineering team.

We have created a flexible tool for AggreGate, which allows you to conveniently create this table using both historical data and statistical data. Functionsummary () . In this article, we will look at how to get a table of data by historical values.

In our example, I propose using data on the ambient temperature and the SOC temperature used in the sites to control energy sources.

STEP 1: Choose which fields of the variable we need.
The most convenient way is to study a snapshot of the device and determine which names the fields of interest to us.


STEP 2: We write an expression to get the data and understand the parameters of the function.


To call the summary () function, which is in the context of utilities, we used the callFunction () function, which allows you to call a function of any context and pass parameters for this function:

subtable (callFunction ("utilities", "summary", "systemCPUTemperature", "System CPU Temperature", date (2015, 05, 10, 0, 0, 0), date (2015, 05, 19, 0, 0, 0 ), "Users.d3.devices.TH3DGM", "device_data", 1, "{system_cpu_temperature}", null, 4, 2, 0, 0, null, null, null, true, false, 0), "periodName" , "Context", "systemCPUTemperature")

Where:
1) "systemCPUTemperature" ID of the field in the resulting table (without spaces).
2) “System CPU Temperature” Description of the field in the resulting table.
3) date (2015, 05, 10, 0, 0, 0) Start date
4) date (2015, 05, 19, 0, 0, 0) End date
5) “users.d3.devices.TH3DGM” Context path device or mask.
6) "device_data" ID of the variable by which the function will work.
7) 1 Data type, in our case - a variable, it is also possible to work with events.
8) "{system_cpu_temperature}" Expression for calculating the field value in the resulting table. In our case, this is the value of the system_cpu_temperature field
9) null Expression for calculating the date. (By default, a time stamp is taken from the database, if you need to use a different date, then you can write an expression so that the field of the same variable is considered as a date)
10) 4 The period of data aggregation.
11) 2 Type of aggregation (max in our example)
12) 0Type of value changes (we have raw data)
13) 0 Normalization of data (we ignore such data in our example)
14) null The minimum allowable value for the input data of the series
15) null The maximum allowable value for the input data of the series
16) null Time zone of the source data (we use the server’s time zone)
17) true Whether to show the result for each aggregation period.
18) false Whether to show the total result for the entire selected period of the source data.
19) 0 Sort by time or by device (we use by time)

We


get the following table: STEP3: We remove the extra fields and enjoy the result.

We use the subtable () function , the first parameter we feed our table, and then we list which fields we want to leave.



In the next publication, we will look at how to use events with the built-in AggreGate tools and how to parameterize your queries and reports to make them more flexible and universal.
We will be glad to any suggestions on what to describe in the following articles and your personal experience, solutions to specific problems.