AlesRuzicka.eu
#B2B #DotNet #SQL

Data collection and analytics

Problem

  • 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 client’s servers

Solution

  • I overtook system after 3 months of development by other developer
  • First version was done in 2 weeks
  • Scope has been reduce to just what is necessary to do and reporting was left to 3rd party applications
  • System collects all data in key-value manner in single SQL table
  • System schedules data consolidation, transformation and insertion SQL tables that are specific per report
  • System generates it’s onw SQL create those tables and insert in them
  • Single data point can end in many data tables
  • Consolidation can be extended using plugins

Advantages

  • 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