4 ms·

Did the company use a relational database to deal with the billing and subsequent adjustments? I've been there: it can be more straightforward to use an event s

Did the company use a relational database to deal with the billing and subsequent adjustments? I've been there: it can be more straightforward to use an event streaming approach that recalculates billing as new events arrive.

janci 4y ago
Yes, almost everything ended as temporal or bitemporal model. Pain to work with. Even for simple questions like "What is the customer #1234 company name?" the system needs to know when are you asking.
- gnat 4y ago
  By "temporal model" do you mean relational tables of "customer X used Y amount of this resource at price Z from timestamp A to timestamp B" or ... ?
  - benwilson-512 4y ago
    In field metering situations you can have the timeline of each meter, when it records data points, and then you have the different timeline of the backend service, when it actually _receives_ data points. Bonus points if the meters can send data out of order.
    - gnat 4y ago
      Ah, thanks. OMFG and I thought our billing was hard. You're in my thoughts and prayers!
    - ntr-- 4y ago
      I did a tender for aggregating events and emitting notifications from a fleet of transport vehicles that reported metrics over LTE. When connected they would emit events as they happened, but when there was poor service or disconnection they would buffer them and send them as a burst once they reconnected... in reverse order. I forget the finer details, but the service I was writing was supposed to notify of trucks entering and leaving geofences, inactivity etc. anything you can think of where the order of events is important.
    - girvo 4y ago
      You just perfectly described what I’m currently working on. And it’s even more complex than you’ve explained (which I’m sure you’d well know haha)
  - jimsparkman 4y ago
    I assume they are using something like DB2, which has very specific meaning when it comes to temporal or bitemporal tables. https://www.ibm.com/docs/en/db2/10.5?topic=tables-bitemporal https://www.ibm.com/docs/en/db2/10.5?topic=tables-bitemporal
    - janci 4y ago
      Not DB2, but the meaning is the same. In a time-series (or event) table you have one date - when the data point is valid (event occurred). In a temporal table you have two dates - interval when the data is valid. In a bitemporal table you have two intervals. First describes when the data is valid in real world, the second when does the system know about the real-world state. I.e. if company changed address on 2022-05-01 but the system will not get that information until three day later (2022-05-03) you have two validity intervals. Then you may ask - what was the company address on 2022-05-02? And what did the system think the address was on 2022-05-02? You will get two different answers. This is important if you need e.g. to make a correction to previously issued invoice. You need to know what data was used for original invoice and what data should have been used.
GrumpyNl 4y ago
Designed and build the system meter readings and calculations for the Dutch market, all mysql, worked like a dream.
- rocqua 4y ago
  I have heard some horror stories about the dutch smart meters. Apparently the billing system provided for 7 'counters' and hence the smart-meter had to have exactly 7 counters.
Rafsark 4y ago
In my opinion, the best way to do it is ingesting events in a relational db with an idempotency key to prevent duplicated events. Some companies don't need real time billing data, so you could just use it to aggregate these events with the aggregation function that fits your need for a certain feature. Thus, you have the flexibility of (i) calculating pretty much everything you want, (ii) give granularity of usage to your customers and (iii) be closer to real time (you play the query anytime you need it).

janci 4y ago

Yes, almost everything ended as temporal or bitemporal model. Pain to work with. Even for simple questions like "What is the customer #1234 company name?" the system needs to know when are you asking.

gnat 4y ago
By "temporal model" do you mean relational tables of "customer X used Y amount of this resource at price Z from timestamp A to timestamp B" or ... ?
- benwilson-512 4y ago
  In field metering situations you can have the timeline of each meter, when it records data points, and then you have the different timeline of the backend service, when it actually _receives_ data points. Bonus points if the meters can send data out of order.
  - gnat 4y ago
    Ah, thanks. OMFG and I thought our billing was hard. You're in my thoughts and prayers!
  - ntr-- 4y ago
    I did a tender for aggregating events and emitting notifications from a fleet of transport vehicles that reported metrics over LTE. When connected they would emit events as they happened, but when there was poor service or disconnection they would buffer them and send them as a burst once they reconnected... in reverse order. I forget the finer details, but the service I was writing was supposed to notify of trucks entering and leaving geofences, inactivity etc. anything you can think of where the order of events is important.
  - girvo 4y ago
    You just perfectly described what I’m currently working on. And it’s even more complex than you’ve explained (which I’m sure you’d well know haha)
- jimsparkman 4y ago
  I assume they are using something like DB2, which has very specific meaning when it comes to temporal or bitemporal tables. https://www.ibm.com/docs/en/db2/10.5?topic=tables-bitemporal https://www.ibm.com/docs/en/db2/10.5?topic=tables-bitemporal
  - janci 4y ago
    Not DB2, but the meaning is the same. In a time-series (or event) table you have one date - when the data point is valid (event occurred). In a temporal table you have two dates - interval when the data is valid. In a bitemporal table you have two intervals. First describes when the data is valid in real world, the second when does the system know about the real-world state. I.e. if company changed address on 2022-05-01 but the system will not get that information until three day later (2022-05-03) you have two validity intervals. Then you may ask - what was the company address on 2022-05-02? And what did the system think the address was on 2022-05-02? You will get two different answers. This is important if you need e.g. to make a correction to previously issued invoice. You need to know what data was used for original invoice and what data should have been used.

GrumpyNl 4y ago

Designed and build the system meter readings and calculations for the Dutch market, all mysql, worked like a dream.

rocqua 4y ago
I have heard some horror stories about the dutch smart meters. Apparently the billing system provided for 7 'counters' and hence the smart-meter had to have exactly 7 counters.

Rafsark 4y ago

In my opinion, the best way to do it is ingesting events in a relational db with an idempotency key to prevent duplicated events. Some companies don't need real time billing data, so you could just use it to aggregate these events with the aggregation function that fits your need for a certain feature. Thus, you have the flexibility of (i) calculating pretty much everything you want, (ii) give granularity of usage to your customers and (iii) be closer to real time (you play the query anytime you need it).