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

read more …