I have an incoming stream of data where I monitor devices belonging to clients, and need to aggregate the data every month for billing purposes.
Starting situation:
-
The readings of a device are averaged out over the time period.
-
If a client owns multiple devices, the above averages are added together. Ex.: If Acme has a device that averaged 1.23 and another that did 4.56, then Acme's monthly reading is 5.79
-
The daily data intake is about 1.1 million documents.
-
There is no Index Lifecycle Management in place right now (I'm not certain that would help anyway for this particular issue)
When I performed the aggregation last month, I used a Data Table visualisation. My metric was a sum of averages, with the data split by customer name. This kind of worked. I was dealing with partial data, and yet Kibana would spit out errors of all kinds when building and refreshing the visualization, sign that it was trying to work with much more data than it could handle and present to me.
I had the idea of using roll-up jobs to pre-aggregate the data so it would be easier to handle come the end of month. For now, I made a daily roll-up as a prototype. Now I have daily averages per device, so I'm partway there, but I can't seem to proceed any further. Attempting to make a visualization using the roll-up index pattern, I find that I can't perform any sort of further calculation on the data: I can't sum the averages, I can't aggregate on a per-customer basis.
Is there a way I can get to a customer-based sum of averages via roll-up indexes? Does the entire logic need to be in the roll-up job? I'm not too sure how the path is laid out so I can get to my goal.
