No More SQL! – How Drop-Downs become a Query

Posted on by

Explore Analytics empowers user to understand information through advanced analytics using an intuitive, self-service user-interface.

It does this by allowing non-technical users to generate powerful visualizations using drag-and-drop selections and drop-down choices, rather than needing scripts, code, or database work.

For many who’ve worked with business intelligence tools in the past, this may be a conceptual adjustment. As such, I’m frequently asked the question, “Where do I put the SQL?” In fact, Explore Analytics makes it even easier — allowing you to do a lot of what you did before in SQL using an intuitive graphical interface.

To show you what I mean, we’ll walk through an example that shows how we build queries easily, and how you can shift away from writing code to getting information.

Let’s suppose I have a sales database, and I’d like to understand how my customers in the US are paying for my products.

In Explore Analytics, I can analyze this easily using a couple easy drop-down selections:

Screenshot 2015-12-31 13.16.45

I’ve created a report on my Payment table, categorized by payment Processor, and filtered where Currency = USD.

Behind the scenes, this report will be translated into this query:

SELECT `processor`, COUNT(*)
FROM `demo_payment`
WHERE `currency` = ?
GROUP BY `processor`
The SELECT clause comes from the view’s Category and Value fields.
The COUNT function applies to the view’s Value field.
The FROM clause comes from the view’s table.
The WHERE clause comes from the view’s filter.
The GROUP BY clause comes from the view’s Category field. (If there are additional Category fields, there will be additional groupings).
In this straightforward example, you can see that a business user doesn’t need to think in terms of SQL or in terms of programming code to build a query; instead, the user is just handling the objects they’re interested in understanding. If we had selected the Customer Country field, for example, the Customer table would have been automatically added to the query.
Explore Analytics performs additional calculations on top of the queries that return. For example, the SQL returns a count for Mastercard and a count for Visa. Explore Analytics goes on to calculate the % of Total for the value labels on the pie chart.
As you can see, even in an easy use case, any user can run the sorts of queries that you may be used to be using SQL for, without having to break open a line.
Moreover, the real differentiation comes when you look at a more advanced example:
Screenshot 2016-01-08 18.34.48
There’s a few things going on in this mash-up view:
  • The Calls query is running against a SQL database.
  • The Incidents query is running against an application API (ServiceNow)
  • The Ratio calculation is combining against those two data sets in real time.

Without copying data, and without creating a database view, any business user can query multiple data sources and combine the information together.

No SQL statement or statements alone could combine the data as above; you would need to write SQL statements and SOAP API calls, and then write additional code to combine the data together.

Instead, with Explore Analytics’ intuitive user interface, the calculation looks simply like this:

Screenshot 2016-01-08 18.44.08
So instead of asking, “Where do I put the SQL?” you can start to ask, “What problems do I want to solve?”