February 2017

Volume 32 Number 2

[Cutting Edge]

In-House Business Intelligence with Events and CQRS

By Dino Esposito | February 2017

I was discussing a few wireframes with a customer recently to verify the effectiveness of a business process and overall quality of the UX the team was building. In the system were a few shared items of work that users could pick up and start processing. The customer was an overall technical person, but all she could envision was a relational database table with a record indicating who picked up what and the current state of processing.

Needless to say, a similar architecture would definitely do the job. With a different and event-based architecture, though, you can grab a lot more information about what’s going on in the system. The core question, therefore, is the following: Might your customer need—now or in the near future—to access and consume the extra information you can get out of events?

In this column, I’ll pick up where I left off in last month’s column and add a business intelligence (BI) module to the sample application dealing with meeting rooms to book. The term BI these days is quite an overloaded buzzword often associated with specific technologies and products. At its core, though, BI refers to a set of techniques to grab and transform raw data into useful information available to analysts to improve processes or to use as mere statistical evidence. 

By adding event sourcing to an application, you get a hold of raw data. By combining event sourcing and CQRS you end up with raw business events stored in the command stack properly denormalized for the sake of the application core functions. At any time, though, you can add an extra module that reads raw data and transforms that into other meaningful chunks of information for whatever business purpose you might have.

Never Miss a Thing

When it comes to highlighting the benefits of event sourcing, the first point usually mentioned is this: “With events you never miss a thing of what happens within the system.” It couldn’t be truer, but it probably deserves a bit more of a pragmatic explanation. Have a look at Figure 1.

Figure 1 Multiple Projections Can Be Built on Top of Raw Events

In a create, read, update, delete (CRUD) system, you typically have one representation of data—mostly relational—and one or more simple projections that most of the time just adapt tabular data to the needs of the presentation layer. With event sourcing, you take this model much further, and lowering the abstraction level of the stored data is the key factor. The more domain-accurate information you store, the richer and more numerous projections you can build at any later time.

In a software system, user actions are the smallest piece of observable behavior, and business events caused by those actions are the most basic piece of information you can store. In my previous column (msdn.com/magazine/mt790196), I used the MementoFX framework via NuGet to transparently handle and persist relevant events in the lifetime of domain aggregate objects. I also used special synchronization tools—called denormalizers—to create a viewable projection after each relevant business event. In the end, all the column showed was rewriting a CRUD system according to the Event-Command-Saga (ECS) pattern. Let’s see what it takes now to add another projection to fill out the dashboard screen of a manager.

Toward Your Own BI Layer

In Figure 2, you see the primary screen of the sample application. As mentioned, it’s a booking system for a shared resource like a set of meeting rooms.

Figure 2 The UI of the Sample Booking System

As you can guess from the figure, any users allowed into the system have the chance to book a room and then move or cancel the reservation. In a classic CRUD-oriented system there’s a Booking table where each record identifies a reservation. When a reservation is moved, starting time and length are overwritten and when a reservation is canceled, the record is simply deleted. At any time, a plain query of records returns the current state of the bookings. If you turn the plain CRUD into a historical CRUD (see the May and June 2016 installments of this column at msdn.com/magazine/mt703431 and msdn.com/magazine/mt707524, respectively), you can track a fixed number of events and have them saved with any relevant information in some other tables. In which way, then, is an event-sourcing, MementoFX-based solution preferable over a plain-and-simple historical CRUD? It’s all about code flexibility and resiliency of the final software artifact.

With MementoFX, you focus on the relevant domain behavior and events. You model domain objects around these needs and let it go. In return, you have an API to query what happened, the state of the system at a given date and time, and whether you have a log of all events or just raw data aggregated and transformed in some custom way that makes sense for some occasional business purpose. This is the raw, intended meaning and essence of what people call BI.

Building a Plain Log of Events

With reference to the sample application, you have a RavenDB database that stores all raw events and a SQL Server denormalized table that contains the list of pending bookings. Stored events comprehend the event that created a booking and all successive events that moved that booking to a different time, and even events that canceled that booking. A canceled event doesn’t show up anymore in the main UI and so it’s for intermediate slots to which a booking was moved. All of these events aren’t relevant for building the primary UI, but they’re crucial for building a dashboard UI for a manager or an administrator.

As an example, let’s see what it takes to group all bookings in the system (or bookings created in a given interval of time) and show the entire history of each, as shown in Figure 3.

Figure 3 The Full Log of Events in the System

The system counts 16 pending bookings, but each booking consists of one or more events. For example, the highlighted booking was first created and then moved twice to different slots on different days.

To produce a similar screen, you must definitely query all events in the event store and then work them out to reach a shape that suits the intended UI. The Razor code that produced the actual view received a data model like this:

public class BookingWithHistory
{
  public BookingWithHistory()
  {
    History = new List<BookingHistory>();
  }
  public BookingSummary Current { get; set; }
  public IList<BookingHistory> History { get; set; }
}

The BookingSummary class represents the current state of a given booking and is the class behind the primary view of Figure 1.

public class BookingSummary : Dto
{
  public Guid BookingId { get; set; }
  public string DisplayName { get; set; }
  public DateTime Day { get; set; }
  public int StartHour { get; set; }
  public int StartMins { get; set; }
  public int NumberOfSlots { get; set; }
  public BookingReason Reason { get; set; }}

An instance of this class is created during the denormalization process after each “create” or “move” action. This class is persisted and read via Entity Framework to and from a classic SQL Server database. In other words, this class is the item that forms the default view of the system around the current and up-to-date snapshot of the state. Technically, this class belongs to the read model.

The view you get in Figure 3 instead collects data also from raw events logged in the event store, a RavenDB store in the example. The following code snippet shows the queries ran against the event store to get all events of interest that happened in the lifetime of the booking with the given ID:

var createdEvents = EventStore.Find<NewBookingCreatedEvent>(e =>
  e.BookingId == bookingId).ToList();
var movedEvents = EventStore.Find<BookingMovedEvent>(e =>
  e.BookingId == bookingId).ToList();
var deletedEvents = EventStore.Find<BookingCanceledEvent>(e =>
  e.BookingId == bookingId).ToList();

The union of those events delivers the full history of a given aggregate. Replaying all those events—namely, applying sequentially the state of each event to a fresh instance of the aggregate object—returns the current state of the object for display or processing purposes. It goes without saying, though, that you can add a date filter to the query on events and thus rebuild the state of the domain object—the booking—up to a given date. 

Extrapolating Some Business Information

Let’s say you’re a manager responsible for internal processes. In your role, you want shared resources, such as a meeting room, to be used effectively. How can you verify that and update booking policies accordingly? Figure 4provides useful information to answer that question.

The pie chart shows how many bookings were created in the time interval and how many of them have been later moved or canceled. The bar chart, instead, breaks up the same information on a weekly basis. At a first rough analysis, it seems that nearly half of reservations are moved at some point and about one of every four is even canceled.

If you’re a manager willing to improve processes, the graphs in Figure 4 might sound an alarm. Whatever way you look at it, the number of changes after reservations are made is significant. Is this preventing other potential users to book their rooms with ease? To stop the alarm from ringing, you might want to look at the average coverage of each room in the same time interval. If this particular coverage graph isn’t in your dashboard already, having developers add it doesn’t really require long hours of work. In the end, it’s just about writing another predicate for a LINQ-style query:

var eventsByWeek = bookingStatuses.GroupBy(b => b.WeekDate).ToList();
foreach (var weekEvents in eventsByWeek)
{
  var wr = new WeekActivityReport
  {
    StartOfWeek = weekEvents.Key,
    TotalBookingCreated = weekEvents.Count(),
    TotalBookingMoved = weekEvents.Count(b => b.Moved),
    TotalBookingDeleted = weekEvents.Count(b => b.Deleted)
  };
  reports.Add(wr);
}

Figure 4 A Graphical Report of Booking Actions in a Given Time Interval

In particular, the sample application gets all events for all bookings in the given time interval and groups them by week. Next, it creates a weekly activity report object that is then easy to pass to ChartJS for creating dazzling graphics.

The most important aspect of event sourcing that I meant to emphasize in this column is that once you have raw information saved at the lowest possible level of abstraction, you can use it in many different ways at many different times. Sticking to the meeting rooms demo, you can deploy the manager dashboard in a successive release or make it another brand-new product. You can also parse all events in the lifetime of the application and build new data projections on top of it for whatever business goals you can think of. More than everything else, though, any successive development effort is for the most part independent from what you have and building it doesn’t affect what you have in place already. That’s the ultimate return on any investments you make on CQRS and event sourcing combined.

Wrapping Up

Processing business events in software is nothing new. You can probably achieve similar results using a historical CRUD (H-CRUD)—just a fancy name for any sort of handcrafted solution that lets you track all the different states of a business object. Event sourcing does the same job, except that it operates at a different level of abstraction and relies on more powerful and tailor-made tools (such as event stores) and patterns (such as event sourcing) in the context of specialized architectures (such as CQRS).

I’ve been writing about CQRS and event sourcing in MSDN Magazine for quite some time now and in a way I formed the idea that most people agree on the relevance of CQRS and events, but find it hard to find a place in which to start. To these people I recommend getting back to H-CRUD, which I wrote about back in May (msdn.com/magazine/mt703431) and June (msdn.com/magazine/mt707524), and then back to more recent articles about the ECS pattern (also known as CQRS/ES) and MementoFX. That should help you start from a familiar mindset and then progressively enhance it up to reaching a point in which you’re doing old things in a new and more powerful way.

All this said, software is not about magic or religion. Software is about getting things done, preferably in a way that works for the customer and for the development team. Along with event stores, buses, event patterns, and architectures, MementoFX helps in having things done quickly and effectively.

Dino Esposito is the author of “Microsoft .NET: Architecting Applications for the Enterprise” (Microsoft Press, 2014) and “Modern Web Applications with ASP.NET” (Microsoft Press, 2016). A technical evangelist for the .NET and Android platforms at JetBrains, and frequent speaker at industry events worldwide, Esposito shares his vision of software at software2cents.wordpress.com and on Twitter: @despos.

Thanks to the following Microsoft technical expert for reviewing this article: Andrea Saltarello

Discuss this article in the MSDN Magazine forum