Data collection and analytics


  • To collect data from many different systems
  • Data can by of any kind
  • Data for single point may come from many sources over time
  • It should be simple search and to make reports from data (it must be indexed and in proper data types)
  • Data must never leave clients servers


  • I overtook system after 3 months of development by other developer
  • First production ready version has been finished in 2 weeks
  • Scope has been reduce to just what is necessary and reporting was left to 3rd party applications
  • System collects all data in key-value manner in single SQL table (serialized as JSON)
  • System schedules data consolidation, transformation and insertion in custom SQL tables that are specific per report
  • System generates its own SQL to create those tables and insert in them
  • Single data point can end in many data tables
  • Many data points can be consolidated to single row in final table (collected from multiple systems)
  • Consolidation can be extended using plugins


  • I decided not to care about reports as there are already many good reporting tools, just data collection and transformation
  • I decided use capabilities of SQL server rather than try to bend it to what it was not designed for
  • This simplified whole development significantly, system is running for many years now without any intention