For one of our customers, we built a system that simplifies creating questionnaires and processing questionnaire data.
One of the challenges was designing a simple language, that can be used by a non-technical analyst to describe the questionnaire structure (i.e. questions, possible answers, single/multiple choice, etc.). Based on such survey description, the system is able to generate various Word documents that can be used to communicate with the customer ordering the survey, the field agency doing the actual data collection, etc. Also, based on the survey description, the system is able to automatically import data delivered by the field agency into an efficient internal format or into an SQL database. Thanks to the application of fuzzy string matching techniques, the import algorithm is able to automatically handle and correct errors in the data delivered by the field agency.
Another challenge was designing a query language and a high-performance query engine to analyse the questionnaire data. Questionnaire data is multi-dimensional and doesn’t fit very well into the relational model of SQL-based databases. Together with the customer, we designed a novel, multi-dimensional data model and an easy, intuitive query language based on this model. We then built a query engine, which uses modern database techniques such as column storage and vectorization to achieve high performance.
The system makes it easier to handle questionnaire data by eliminating a lot of manual work in the first phase of working with a questionnaire (document creation, data cleaning and import) and by simplifying the queries and improving their performance in the second phase (data analysis).