I work in an industry where a lot of data is expressed as a set of values valid over contiguous, variable-length date ranges.
For instance, we will use such a construct to designate the maximum capacity of a pipeline or a delivery point, i.e. how much gas can flow in any given hour, in kilowatthours. This typically results in the following table :
| Object Key | Capacity (kWh/h) | Valid From | Valid To |
|---|---|---|---|
| NETPOINT | 1,000 | 1/10/2021 | 15/11/2021 |
| NETPOINT | 1,500 | 15/11/2010 | 28/3/2022 |
| NETPOINT | 2,000 | 28/3/2022 | 31/12/9999 |
The key characteristics are that
- the date ranges are totally variable and do not obey any specific rule, they can cover a day, a month, a set of days
- they can stretch indefinitely into the future
The problem arises when business users start wanting analyzing and aggregating the data. To aggregate for instance over two delivery points and calculate the total capacity over a given period, you typically need the following algorithm :
- "periodize" the data by calculating over for every day or hour between Valid From and Valid To the capacity
- Aggregate by point and day/hour
- Voilà, you have the result.
The trouble is : if you have 10 or 15 such points and you want to aggregate over 20 years, with the granularity being at hourly level, you quickly end up with billions of rows. Current solutions are either
- bite the bullet and develop a datawarehouse or datalake where the values are periodized and the presented to the busines user. Advantage : fits well with a BI analytics tool like powerBI. Disadvantage : volumetry. And it just isn't intellectually satisfying to generate 1000 or 10000 identical values just to be able to do proper aggregations.
- Develop a custom-made application which will on the fly generate the data based on the start and end dates given by the end user
- Store the variable date range data in a datawarehouse and perform the "periodization" at OLAP level, for instance in DAX in PowerBI. I tried it and it works, however as soon as you reach 10,000 or more variable date ranges, you will need several seconds if not minutes to calculate the results.
The question
Given all the above, it would be tempting to imagine a specialized database engine with a native data type which would store a set of values with associated date ranges, and then handle natively sum, difference, product and so on, as well as generate on-the-fly periodized data for the end user, without having to generate corresponding database rows. Similarly, a specialized data visualisation component taking as an input variable date range data, and displaying it at any aggregation level (hour, day, month...) on the fly would be awesome.
Do such components exist, have other solutions been found, or is there simply not enough business like ours to justify people creating such solutions ?