Several years ago I started forking on a new project… it was a reporting system. People get wrapped around the axle when it comes to reporting systems because there can be real complexity when it comes to scaling. The reality of the situation is that scale is a nice problem to have but not many people actually get there. And so my problems begin; less scale and more consistency.
If you ran a dozen reports … [a] they cannot be executed all at once. Depending on the available resources there will always be competition. And reporting will use it all. And while reporting is not running these systems will just be generating heat. [b] there is a constant problem with schema. As developers are trying to add features to the frontend the backend is always the last person to know. Schema changes or the context of certain data changes and systems break. When you have 1700 reports, as I do, it’s impossible to manage. [c] then there is archives, delivery, security, PII, and so much more.
The latest system I designed is based on flow based programming. It’s an interesting idea but sort of a bad choice. The example given for FBP was ETL and in that instance it was reasonable. But then FBP also starts to look like J2EE data buss; and in that instance it starts to lose it’s value. This is very similar to the erlang hot pluggable code mechanism. [1] it may not be reproducable [2] it’s not necessarily idempotent (might be the same as #1). This project also implemented a DSL which was just more of a document structure. Instead I should have either used markdown as the structure so that we had inline documentation or implemented in tcl or picol extended and interpreted in golang.
Lastly, while my implementation was designed to run on a replicated copy of the OLTP instance there are a number of challenges. We should have drawn a lin in the sand, ETL’d the data to a SQLite DB and then ran as many reports as needed across as many temporary servers as needed. The benefit here is several fold and should be obvious. [i] The reporting system is now temporal [2] represents backups [3] data can be remodeled so that a suite of reports can be shared regardless of the raw data.
One last thing about my reporting… some reports fit in ram and some do not. Some require large and huge machine instances. The reason is because the dataset is so large and the reporting medium, typically XLSX, are just not compatible; and CSV is not executive ready. So really the customer needs some analytics.
To recap: