#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 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 intervention