Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using Google Docs, Data Explorer and PowerPivot for Questionnaires

with 9 comments

You may have already seen that the labs release of Data Explorer is now publicly available; there’s a whole load of really useful resources available on the learning page too if you’re interested in finding out more about it.  I’ve been very lucky to have had early access to Data Explorer, and to test it out I put together a simple demo using the cloud service that shows off a typical use-case.

The first thing I did was to use Google Docs (just to have a cross-platform demo, not because I love Google in any way, honest…) to create a simple questionnaire using Google Forms. Before you read any further, please go and fill out the questionnaire I created here:

https://docs.google.com/spreadsheet/viewform?formkey=dDRnNi1fbkotLVd6Q0g4MmhsdFV2OGc6MQ

Don’t worry, there’s only three questions and it’s all non-personal data! For those of you reading offline, here’s a screenshot:

image

Now when you create a questionnaire like this in Google Forms, the responses get put inside a Google Docs spreadsheet. Here’s the link to the spreadsheet behind my questionnaire:

https://docs.google.com/spreadsheet/ccc?key=0Akv4XYo6s_Z2dDRnNi1fbkotLVd6Q0g4MmhsdFV2OGc

image

The good thing about Google Docs (unlike, ahem, the Excel Web App) is that it has an API. The contents of this sheet could easily be exported to a number of formats including csv, which means I could get the data into PowerPivot very easily. But there was a problem: the last question is multiple choice, and for the results of that question I got a comma-delimited list of values in a single cell in the spreadsheet (see the above screenshot) – which was not going to be very useful for analysis purposes. What I really wanted was all this data split out into separate columns, one column for each version and containing a boolean value to show if that version has been checked, so if I was going to analyse my responses by version I clearly needed to do some ETL work. I could do this with a calculated column inside PowerPivot of course, but the problem with this is that every time someone wanted to work with this data in a new PowerPivot model they’d have to repeat all this work, which is a pain, and clearly some users wouldn’t have the DAX skills to do this. The best thing to do would be to perform the ETL somewhere up in the cloud so everyone could benefit from it…

Enter Data Explorer. I created a simple mashup with the following steps:

  • Imported the data from the Google spreadsheet as a csv file
  • Cast that data as a table
  • Split the Timestamp column into two separate Date and Time columns
  • Added new columns to the table for each version of SSAS that contained the value True if that version had been checked in a particular response, False if not

image

Apart from the usual struggles that go with learning a new language, it was pretty straightforward and I was impressed with how easy it was to use. Here’s an example of an expression that adds a new column to show whether the respondent checked the “OLAP Services” box in the final question:

= Table.AddColumn(#"Rename Date Time", "Used OLAP Services", each if Text.Contains([#"What versions of Analysis Services have you used?"],"OLAP Services") then "True" else "False")

Finally, I published the output of the mashup publicly. This page contains all of the links to download the live data in various different formats:

https://ws18615032.dataexplorer.sqlazurelabs.com/Published/Chris%20Webb%20Questionnaire%20Demo

image

If you filled in the questionnaire you should be able to find your responses in there because it’s a live feed.

And you can of course import the data into PowerPivot now very easily, for example by using the OData feed from Data Explorer. First, start Excel, go into the PowerPivot window and click on the “From Data Feeds” button:

image

Then, in the wizard, enter the URL of the OData feed:

image

And you should then have no problems importing the data:

image

…and then analysing the responses. You will want to create a simple measure with a definition something like this to do so to count the number of responses:

=COUNTROWS(‘Questionnaire Data’)

image

I’m looking forward to seeing the data come flooding in!

This approach could easily be applied to scenarios such as analysing feedback from user group meetings or events, and what with the number of online data sources out there there must be hundreds of other potential applications as well. And given that anyone can now publish and sell data on the Windows Azure Marketplace there must be ways of making money from this too…

Written by Chris Webb

December 7, 2011 at 10:23 am

Posted in Data Explorer, ETL

9 Responses

Subscribe to comments with RSS.

  1. Liking the dig about an Excel web app api. Keep plugging away, they’ll cave in eventually.

    Jamiet

    December 7, 2011 at 2:09 pm

    • I’ll shame them into giving us an API if it’s the last thing I do…

      Chris Webb

      December 7, 2011 at 2:13 pm

      • Incidentally, wouldn’t it be cool if the Excel web app had the ability to consume OData feeds too? That way everything you just demo’d could live completely in the cloud – no software install required. Very cool.

        Jamiet

        December 7, 2011 at 2:17 pm

      • Indeed, and I’d also like desktop Excel to do the same thing. I assume that once SSAS-in-the-cloud comes along we’ll see the Excel Web App improve its functionality in the area of consuming data from other web-based data sources.

        Chris Webb

        December 7, 2011 at 2:24 pm

  2. [...] CTP of Data Explorer got released, which of course I’ve blogged about already here and which Jamie blogged about here and [...]

  3. [...] very excited to see how Microsoft’s cloud-BI strategy pans out in the next year: products like Data Explorer suggest that Microsoft is serious about this area and, more importantly, coming up with innovative [...]

  4. [...] is this feature? Basically a rip-off of homage to the Google docs functionality I mentioned here that allows you to create simple questionnaires and save the data back to a spreadsheet. To use it [...]

  5. [...] might have seen me mention Data Explorer a few times over the last year in various blog posts; it’s a self-service ETL tool that is currently available via SQL Azure labs: [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: