CQRS and Event Sourcing Query Historical Data

Question

When using CQRS and Event Sourcing how does one query historical data. As an example if I am building a timesheet system that has a report for revenue I need to query against hours, pay rate, and bill rate for each employee. There is a EMPLOYEE_PAY_RATE table that has EmployeeID, PayRate, and EffectiveDate, as well as a BILL_RATE table which has ClientID, EmployeeID, Rate, and EffectiveDate. The effective date in those tables is basically keeping the running history so we can report accurately.

If we were to take a DDD, CQRS, and Event Sourcing Route how would we generate such a report? It's not like we can query the event store in the same way. I've looked at frameworks like Axon but not sure if it would allow us to do what we need to do from a reporting perspective.

Answer 1

When using CQRS and Event Sourcing how does one query historical data.

Pretty much the same way you query live data: you build the views that you want from the event history, and then query the views for the data that you want.

To borrow your example - your view might be supported by an EMPLOYEE_PAY_RATE table and a BILL_RATE table. Replay your events, as something interesting happens update the appropriate table. TaDa.

An important idea that may not be obvious - for something low latency like a history report, you'll probably want the historical aggregator to be pulling the events from the event store, rather than having a bus push events to the aggregator. The pull approach makes it a lot easier to keep track of where you are, so that you don't need to repeat a lot of work, worry about whether you've received all of the events you should, ordering, and so on.

Answer 2

You report is just another read-model/projection of the events, for example a SQL table that is populated by listening to the relevant events.

If the table is big, i.e. a lot of employees, in order to be fast, you should avoid using joins, by keeping the data denormalized; so, for every employee and day (or whatever granularity you want) you would have a row in a table containing the Employee ID and name, the start date and the end date of the day and other columns containing relevant data, i.e. the pay rate. You put heer the employee name also in order to avoid the joins and you keep it up-to-date by listening to the relevant employee events (like EmployeeChangedName).