For a few weeks I have been pondering how best to handle ‘survey data’ for an organization that collects a lot of survey data and needs to make it available to people for a variety of ‘business intelligence’ needs.
The key needs are:
– must be in a format that is easy for the contributor of the survey data
– must allow for the same surveys to be repeated over time e.g. quarterly survey, for the same survey respondents e.g. Company ABC completes survey a number of times over a period of time
– must allow for survey results to be easily compared and contrasted e.g. same contributor, different survey submissions ( this quarter versus last quarter) and different contributors for the same time period e.g. Company ABC versus Company XYZ
– allow for the content of surveys to change over time e.g. questions can be added, deleted, changed
– allow people to access the data as surveys e.g. all data for a given survey’s instance (all data for this quarter) or accessing the data for a given question over time e.g. answers to question 4 this quarter versus last quarter
– allow easy mapping of survey data to broader business concepts e.g question 2’s answer is ‘annual revenue’ for a given respondent
*whew* I think that’s about it…. easy, huh?
much of the research stuff I read suggested:
– XML is a good way of representing a survey and collecting survey data
– translate the XML data into a relational data model view of the survey and store in a SQL database
… sounds great but nothing a read really convinced me there could be a simple, generic approach that would work for all situations.
Keeping the data in an XML format, to allow data to be accessed in its survey form seems to hold some promise for addressing some of the requirements, at least partially, but thability to easily query the XML data seemed clumsy, at best… enter JSON
JSON seems to provide similar advantages for receiving survey data in a structured way and, with the advent of modern document databases, also the ability to easily query the data in its survey form
more to follow in this series… 🙂