Working with Data in the Surveys Application : Using SQL Azure

The Surveys application uses Windows Azure storage to store survey definitions and survey responses. Tailspin chose to use Windows Azure storage because of its lower costs and because those costs depend on the amount of usage, both in terms of capacity used and the number of storage transactions per month. However, to control the costs associated with storage, the Surveys application does not offer a great deal of flexibility in the way that subscribers can analyze the survey responses. A subscriber can browse through the responses to a survey in the order that users submitted their responses, and a subscriber can view a set of “pre-canned” summary statistical data for each survey.

To extend the analysis capabilities of the Surveys application, Tailspin allows subscribers to dump their survey responses into a SQL Azure database. They can then perform whatever detailed statistical analysis they want, or they can use this as a mechanism to download their survey results to an on-premise application by exporting the data from SQL Azure.

---

Note:

SQL Azure allows subscribers to perform complex analysis on their survey results.

---

This feature is included in the monthly fee for a Premium subscription. Subscribers at other levels can purchase this feature as an add-on to their existing subscription.

1. Goals and Requirements

The application must be able to export all survey data to SQL Azure, including the question definitions in addition to the survey responses.

Subscribers who choose to use this feature have their own, private instance of SQL Azure to ensure that they are free to analyze and process the data in any way that they see fit. For example, they may choose to create new tables of summary data or design complex data-analysis queries. A private instance of SQL Azure also helps to ensure that their data remains confidential.

Giving each subscriber a separate instance of SQL Azure enables them to customize the data, and it simplifies the security model.

2. The Solution

During the on-boarding process, the application will provision a new SQL Azure instance for those subscribers who have access to this feature. The provisioning process will create the necessary tables in the database. As part of the on-boarding process, the Surveys application saves the information that the application and the subscriber require to access the SQL Azure instance in BLOB storage as part of the subscriber’s details.

A task in a worker role monitors a queue for messages that instruct it to dump a subscriber’s survey results to tables in SQL Azure. Figure 1 shows the table structure in SQL Azure.

Figure 1. Surveys table structure in SQL Azure

3. Inside the Implementation

Now is a good time to walk through the code that dumps the survey responses to SQL Azure in more detail. As you go through this section, you may want to download the Visual Studio solution for the Tailspin Surveys application from http://wag.codeplex.com/.

The following code example shows the task in the worker role that executes when it is triggered by a message in a queue. The Run method is in the TransferSurveysToSqlAzureCommand class.

public void Run(SurveyTransferMessage message)
{
    Tenant tenant =
        this.tenantStore.GetTenant(message.Tenant);
    this.surveySqlStore.Reset(
        tenant.SqlAzureConnectionString, message.Tenant,
        message.SlugName);

    Survey surveyWithQuestions = this.surveyRespository
        .GetSurveyByTenantAndSlugName(message.Tenant,
        message.SlugName, true);

    IEnumerable<string> answerIds = this.surveyAnswerStore
        .GetSurveyAnswerIds(message.Tenant,
        surveyWithQuestions.SlugName);

    SurveyData surveyData = surveyWithQuestions.ToDataModel();

    foreach (var answerId in answerIds)
    {
        SurveyAnswer surveyAnswer = this.surveyAnswerStore
            .GetSurveyAnswer(surveyWithQuestions.Tenant,
            surveyWithQuestions.SlugName, answerId);

        var responseData = new ResponseData
        {
            Id = Guid.NewGuid().ToString(),
            CreatedOn = surveyAnswer.CreatedOn
        };

        foreach (var answer in surveyAnswer.QuestionAnswers)
        {
            var questionResponseData = new QuestionResponseData
                {
                    QuestionId = (from question in
                    surveyData.QuestionDatas
                    where question.QuestionText ==
                    answer.QuestionText
                    select question.Id).FirstOrDefault(),
                    Answer = answer.Answer
                };

            responseData.QuestionResponseDatas
                .Add(questionResponseData);
        }
        if (responseData.QuestionResponseDatas.Count > 0)
        {
            surveyData.ResponseDatas.Add(responseData);

        }
    }

    this.surveySqlStore
        .SaveSurvey(tenant.SqlAzureConnectionString, surveyData);
}

					  

The message parameter to this method identifies the survey to export. The method first resets the survey data in SQL Azure before it iterates over all the responses to the survey and saves the most recent data to SQL Azure. The application does not attempt to parallelize this operation; for subscribers who have large volumes of data, the dump operation may run for some time.

The application uses LINQ to SQL to manage the interaction with SQL Azure. The following code from the SurveySqlStore class shows how the application uses the SurveyData and SurveySqlDataContext classes. The SurveySql.dbml designer creates these classes.

public void SaveSurvey(string connectionString,
    SurveyData surveyData)
{
    using (var dataContext =
        new SurveySqlDataContext(connectionString))
    {
        dataContext.SurveyDatas.InsertOnSubmit(surveyData);
        try
        {
            dataContext.SubmitChanges();
        }
        catch (SqlException ex)
        {
            Trace.TraceError(ex.TraceInformation());
            throw;
        }
    }
}