Database questions are a powerful feature of IBM® SPSS® Data Collection. They allow creating single response, multi-response, and loop questions whose category lists are generated by connecting to an external database. However, using database questions adds complexity to the process of authoring and activating surveys. Database questions, while quite useful, do have some disadvantages and limitations.
The purpose of the paper is to introduce the concept of using database questions in conjunction with survey wizards. Database questions, in this case, are not part of the final survey (run-time); they are only used by the wizard during survey authoring. The wizard’s category list that is shown to the survey author is a dynamic list based on database questions. The final survey contains a snapshot of the category list (the wizard uses database questions to create normal questions). Most database question limitations can be avoided when they are used in this fashion. The survey author still benefits from the dynamic nature of database questions.
This article is written for IBM SPSS Data Collection 6.0 and above.
Database questions are used to generate question responses by connecting to an external database. They enhance Text, Long, Double, Date, Loop, and Grid to define the questions responses. Database questions can be:
- Single response database question
- Multiple response database question
- Database array question
There are several advantages to using database questions:
- Provide the ability to change category lists while a survey is running, thus eliminating the need to rewrite and reactivate the survey. Database questions ensure that the questions’ responses are dynamic and always up-to-date.
- Allow survey authors to write mrScript that can be used to further filter the response list returned from the database, or compose custom SQL Where clauses.
Of course, there are restrictions when using database questions and specifics to be considered when using database questions:
- Database questions are dependent on the appropriate database drivers being installed. For example, a database driver that is available on a client machine may not be available to the interview engine that hosts the survey.
- Databases that are available when working on one network may not be available on another network. The database used to create a database question must be available on the same network as the Interviewer Server.
- Any DSNs that are used to setup the connection strings on the local machines must also exist (and point to a compatible database or file) on the Interviewer Server.
- When working with database questions in an Interviewer Server environment, the Anonymous User (defined during Server installation) must have permission to log into the appropriate database server. This applies when the database question connection string is configured for integrated security.
- The database credentials are stored in the survey file’s connection string as plain text.
- Translations are not provided for categories defined by database questions.
Survey wizards are usually created by a scripting expert and used by a survey author. Wizards use small surveys to prompt authors for information when they attempt to execute a questionnaire wizard item. When an author has entered the required information, the wizard creates a series of questions based on the author provided information. These questions are defined in a target questionnaire that can then be further edited by the author.
A wizard can also be part of a project template. When used with a project template, a wizard is executed when the survey author attempts to create a new survey. Another method for deploying wizards is to include them in a questionnaire library item. The included wizard is run when the library item is inserted into a survey.
It should be noted that when database questions are used in survey wizards, the category list is fixed after the survey is activated. If the survey author wants to update the category list, the categories must be manually changed and the survey reactivated.
The University of Pine Island asks Sarah to survey the students every semester regarding their classes. Sarah decides to author separate surveys for each department. Each survey starts by listing the classes offered during the semester and asks detailed questions about each class.
Sarah asks Steve to help her in creating the necessary survey template. Steve creates the project template, which includes a wizard to help Sarah generate the class list. The wizard uses database questions to retrieve the class list from the university’s database server.
As shown in Figure 1, the wizard starts by prompting Sarah to pick a semester from the choices Spring 2011, Summer 2011, Fall 2011 or Spring 2012.
Figure 1 – The survey wizard prompting the user to select one of the recent semesters
The list of semesters are generated by connecting to the database server. An SQL filter defined in database questions is used to display the most recent semesters.
The wizard then prompts Sarah for the department from the list of English, Math, Computer Science, Chemistry and Physics (as shown in Figure 2).
Figure 2 – The survey wizard prompting the user to select one of the departments
The wizard uses the provided department and semester to query the databases for the classes offered. The wizard then prompts Sarah to select the classes she wants to be included in the survey. As shown in Figure 3, the choices to select from are Computer Science Basics, Relational Database, Artificial Intelligence, Programming Languages and Data Mining).
Figure 3 - The survey wizard prompting the user to select the classes to be included
The wizard creates a series of questions and defines them in a new survey. The questions in the new survey do not require a connection to server database.
While database questions are a powerful feature, they have several run-time limitations when used in an Interviewer Server environment. To avoid these limitations, and retain most of the feature benefits, database questions can be used in survey wizards to create surveys that do not require a connection to a server database.
Moataz Burhan is a software engineer at SPSS with Analytical Decision Management team in Rochester, Minnesota. Prior to joining Analytical Decision Management team, he worked on SPSS ShowCase products for 6 years. After that, he worked on SPSS Data Collection team as a software engineer for 4 years. He has been with Analytical Decision Management team for 2 years now where he participated in the development of Analytical Decision Management team 6.2 and 7.0.