Using OData queries to calculate simple statistics
Posted by Steve Lay
In previous blog posts we’ve looked at how to use OData clients, like the PowerPivot plugin for Excel, to create sophisticated reports based on the data exported from your Questionmark Analytics results warehouse. In this post, I’ll show you that your web developers don’t need a complex tool like Excel to harness the power of OData.
There are several third party libraries available that make it easy for your developers to incorporate support for OData in their applications, but OData itself contains a powerful query language and developers need nothing more than the ability to fetch a URL to take advantage of it. For example, suppose I want to know what percentage of my participants have passed one of my exams.
Step 1: find out the <id> of your assessment.OnDemand
To start with, I’ll build a URL that returns the complete list of assessments in my repository. For example, if your customer number is 123456 then a URL like the following will do the trick:
https://ondemand.questionmark.com/123456/odata/Assessments (This and the following URLS are examples, not live links.)
The resulting output is an XML file containing one record for each assessment. Open up the result in a text editor or ‘view source’ in your browser, scan down for the Assessment you are interested in, and make a note of the entry’s <id>, that’s the URL of the assessment you are interested in. If you’ve got lots of assessments you might like to filter the results using an OData query. In my case, I know the assessment name starts with the word “Chemistry”, so the following URL makes it easier to find the right one:
All I’ve done is add a $filter parameter to the URL! The resulting document contains a single assessment! I can see that the <id> of my assessment is actually the following URL:
Step 2: count the results
I’m not interested in the information about the assessment but I am interested in the results. OData makes it easy to navigate from one data item to another. I just add “/Results” to the URL:
This URL gives me a similar list to the assessment list I had earlier but this time I have one entry for each result of this assessment. Of course, there could be thousands, but for my application I only want to know how many. Again, OData has a way of finding this information out just by manipulating the URL:
By adding /$count to the URL I’m asking OData not to send me all the data, but just to send me a count of the number of items that it would have sent back. The result is a tiny plain text document containing just the number. If you view this URL in your web browser you’ll see the number appear as the only thing on the page.
I’ve now calculated the total number of results for my assessment without having to do anything more sophisticated than fetch a URL. But what I really want is the percentage of these results that represent a pass. It turns out I can use the same technique as before to filter the results and include only those that have passed. My assessment has Pass/Fail information represented using the ScorebandName.
https://ondemand.questionmark.com/123456/odata/Assessments(77014)/Results/$count?$filter=ScorebandName eq ‘Pass’
Notice that by combining $count and $filter I can count how many passing results there are without having to view the results themselves. It is now trivial to combine the two values that have been returned to your application by these URLs to display a percentage passed or to display some other graphic representation such as a pie chart or part filled bar.
As you can see, your developers don’t need a sophisticated library to write powerful client applications with OData. And these HTTP documents are just a few bytes in size so they won’t use much bandwidth (or memory) in your application either. For additional resources and definitions of all the OData filters, you can visit the OData URI conventions page at odata.org.
Questionmark Users Conferences offer many opportunities to learn more about Questionmark Analytics and OnDemand. Registration is already open for the 2014 Users Conference March 4 – 7 in San Antonio, Texas. Plan to be there!