We Made SQL Visual - Why and How

I’ve spent almost a decade now obsessed with the problem of truly enabling anyone—not just data teams—to explore and understand their business data. I still obsess over this as passionately as ever. It’s a much harder problem than I ever realized, but it’s just as important.

35 years ago, Microsoft Excel was first released. It was an incredible product that truly empowered business users to work with spreadsheets of data—and remains so today.

But when data gets larger than a spreadsheet, Excel no longer suffices, and there still aren’t any tools intuitive enough to take its place. Users are forced to learn databases, SQL, or complex Business Intelligence (BI) interfaces—or rely on data experts to do it for them.

Today, we’re excited to announce that we have—through thousands of design iterations, dozens of functioning prototypes, several hundred user tests, and countless hours of development—created an interface that truly enables the business user to work with data. We call the interface Visual SQL.

Reality and research

A little over a year ago, after a long customer roadshow for Chartio, I was forced to admit that we hadn’t yet solved the problem. We’d built an incredible product, created a profitable business with fantastic customers, and been rated as the most usable BI product on the market for years. But it was clear that we still weren’t usable enough.

I wrote a document summarizing the problems we were still facing in truly democratizing data, and it ended up being over 40 pages. I then had my product team run a series of user tests (thanks, usertesting.com!) on both our product and others in the self-serve BI space.

The tests confirmed that we were the most usable, but still not close to being intuitive. Filtering for users who claimed to be at least moderately competent at Excel, we found that only one in ten of them could use BI products. Users weren’t confident in what data they were exploring, what “measures” and “dimensions” (ie. rows and columns, groups and values) meant, and how the interface was going to map to their desired outcome in general. There was clearly a giant learning curve—and we needed to shrink it.

Problems and insights

We went back to basics, and—with a lot of effort—condensed our years of experience working with data and thousands of customers into the following two truths:

1. SQL is the most flexible and powerful way to query data, but it’s not good for exploration

I love SQL—so much so, in fact, that I wrote a whole SQL interface and tutorial to help people more easily get started learning it. SQL is an incredibly flexible language, and it’s THE language for working with data.

But SQL just isn’t going to be learned by a significant percentage of people. Even for people who do know SQL, it’s not a great way to explore. There are so many things to memorize that writing a SQL query frequently requires quite a few Google searches for that date format, or the right name of some function, or how to do window functions again. It’s also verbose, and prone to error. With straight SQL as your interface, you’ll never have the agility you need to truly explore your data.

2. BI is better for exploration, but still requires too many steps and is not nearly intuitive enough

Business Intelligence products have for years been promising to tackle this problem, but have some serious limitations. Most BI exploration interfaces are built to explore a single dataset or table at a time. This reduces complexity, but at a cost. It limits what the end user can do, and requires extra steps for data teams to build datasets.

Making these datasets also requires impossible foresight into what and how the end user might like to explore the data. It results in a lot of duplication in an ill-fated attempt to flatten a 3-D model into sets of 2-D tables mostly because of the limitation that the interface cannot handle relationships during exploration.

The result is that business users still have the data team as a bottleneck for a great percentage of what they might want to explore. While a noble attempt, these BI products will never allow truly democratized data exploration.

Insight - make SQL visual

With those truths in mind, we realized that to truly enable business users to work with databases, we’d need to build something that’s both as flexible as SQL and more intuitive than BI.

SQL is flexible and powerful because it is a language, so we concluded that any interface we might make needed to be a language as well. Making a visual language isn’t easy, but it turns out that its flexibility has many advantages. For example, with a language, the query definition can be broken down into small actions or steps rather than one complex, everything-at-once interface.

This reminded us of the stories of when Apple, and then Microsoft, first made the DOS operating system visual: many attempts were first (and after) made to just make one giant application that did everything. These attempts obviously didn’t work. To make DOS visual, the interface needed to be as flexible as an operating system too. BI has faced the same problem; it doesn’t work to make one giant application interface to do what SQL can do. To deal with the complexity of data, a language is needed.

With that insight, we realized we would need to design not an application but a language - a visual version of SQL.

Design, Prototype, Test

I’m an extreme optimist, so I knew we could do this. With an amazing newly-recruited designer Martin we created a ton of mocks, put an internal task force together, and built a prototype.

The initial results were awful. Our designs were cool, but they didn’t work. Still, only one in every ten user tests were able to work with their data.

We tried a totally new approach. And then another. We built real working prototypes of NLP, simpler versions of text-based SQL, drag-and-drop solutions, spreadsheet interfaces, and lots of combinations.

But 3 months in, despite 2000+ figma artboards, 10 highly functioning prototypes and 500 user tests, the one in ten number hadn’t budged.

We were completely out of ideas, and my optimism started sinking, fast.. It seemed like there was just no way to get more people over the learning curve of working with databases without training them on how to use BI. I was realizing that our efforts would not result in a breakthrough, but instead in definitive proof that the mission I’d spent almost ten years on was impossible—or at least wouldn’t be solved by us.

Shit.

A week later, we’d made no progress on any ideas or designs for a new approach and my morale was incredibly low. But what I didn’t realize was that our latest iteration—what we were internally calling “Visual SQL”—was showing signs of promise with users.

Suddenly, I started to notice a lot of excitement in our internal #visualsql Slack channel. “Listen to this guy, he’s so excited, he’s loving it,” they were saying about one user. “6/10!” someone declared. We’d finally had a breakthrough, and in the latest batch of user tests 6 in 10 people could now write queries and make charts from databases with no training or no real knowledge of databases! A number of iterations later, we got up to eight in ten. We realized that any further improvements required a full solution; the prototype stage was over, and we moved onto development!

What worked?

We don’t pretend to have all the answers, and we’re not done innovating yet—eight in ten is not ten in ten—but it’s worth reflecting on what has worked so far.

Browse tables instead of a list of columns

Most BI products, including Chartio’s previous version (the “Data Explorer”), feature a vertically-stacked list of columns for users to browse. We found that, in addition to this list, showing a preview of the first 10 rows of the table, in a familiar tabular layout was much more intuitive to users.

Instead of choosing a column name from a list and hoping for the best, they can get a glimpse of what’s in their data source and gain confidence that they’re making the right selection. It both displays more helpful information, and gives a real feeling of browsing real tables.

Intelligent and adjustable grouping and aggregation

Almost all BI interfaces have what I call the Grouping problem: to use a column, you have to decide right away whether you want to group by it or apply some aggregation. In Chartio’s Data Explorer, we called these dropzones “Dimensions” and “Measures.” Others call them “rows” and “columns,” or “groups” and “values.” These categories are confusing, and force users to imagine too much data transformation in advance.

With all of our historic usage data, we discovered that we could correctly predict a user’s desired grouping or aggregation 88% of the time. So we decided to remove that decision by automatically apply it and then instantly showing a preview of what the results will look like.

In our testing, users preferred this automatic grouping and responsive preview even in the 12% of cases where the mapping was wrong. They appreciated that we made a good guess, that we showed the structure of what the output would look like, and that it was easy enough to change. They no longer had to worry about the concepts of grouping and aggregating, nor imagine any data manipulation in their heads.

Writes directly to SQL, which you can edit

While Visual SQL is truly written on top of SQL, it can’t (and probably shouldn’t) do 100% of the things that you can do in regular SQL. That’s why we’ve made it easy to switch into text-based SQL mode when needed.

Similar to how visual operating systems let you go into the terminal when you’d like, Visual SQL lets you go into text mode and review and modify the SQL that’s been written.

This was true for our original Data Explorer as well. Over the years, we’ve found that even power users who know they’ll eventually go into SQL mode prefer to start in the visual mode, as the grouping, date formatting, and joins are all done automatically.

Results table just like a spreadsheet

When you ask business users to envision their dream solution for working with databases, they usually just ask for a better Excel. That’s why we’ve incorporated elements into Visual SQL that will be familiar to Excel users. For example, the lower half of the Visual SQL interface is a results table fashioned after a spreadsheet interface.

Query results are displayed here, and users can take all kinds of actions on them—like moving, renaming, sorting, pivoting, forecasting, and creating new columns with custom formulas—just like in Excel.

A true language

The power of SQL lies in its flexibility. With a language, you can piece together simple words to create complex expressions. This is necessary for working with data, and it’s something that traditional BI solutions, with their monolithic interfaces and single, prepared tables, haven’t gotten right.

Like SQL, Visual SQL is also a language. The actions taken on results tables are processed sequentially in a pipeline that can be rearranged and modified. Each action is relatively simple, but can be flexibly combined to define complex expressions.

The pipeline allows Visual SQL to extend regular SQL in a number of ways, allowing things that would otherwise have required writing code—including merging data from different data sources, performing pivots, and generating forecasts.

Launch

Today we’re officially launching this Visual SQL to the public. You can sign up for a free trial to give it a spin. We’d love to hear your feedback.

Existing Chartio customers can try Visual SQL today as well. Just look for the “Try Visual SQL” button in your Data Explorer. We wrote a whole other post for you on what’s new with Visual SQL explaining all the new improvements over our previous Data Explorer and how we’re planning on rolling Visual SQL out as we make it 100% backward compatible and work with you at your speed to transition.

What’s next

This has been a big journey, but it’s only the start. We have a great team who continue to design and develop Visual SQL. Some of the many improvements we have in store are:

• Better error/warning messaging
• Backwards compatibility with our old Data Explorer (both Basic and Advanced mode)
• Improved visualizations and chart settings
• More intuitive spreadsheet interactions
• More intelligent grouping and aggregation recommendations
• More intelligent visualization recommendations
• Query time estimations
• A more intuitive automatic join interface
• SQL mode editor upgrades
• Integration of Visual SQL with Alerts and Data Stores

And of course, we continue to improve usability through more user tests, extensive usage and user feedback (send us some!)

I’ll be writing more on our design, test and build process for creating this interface and on everything we’ve discovered so far and what we continue to discover, so follow along if you’re interested.

Thank you

I’m immensely proud and grateful for the incredible people we’ve had focused on designing and building Visual SQL. It’s been an honor working with them. The Visual SQL focus team and I are also grateful for the whole company for being patient, excited and supportive during all stages. They’re an amazing group - if our mission and work interests you, you should join us!

As a company we’re also incredibly thankful to our many fantastic customers who have inspired (and funded!) this innovation. Thank you all, and I look forward to continuing to build this out together and bring Visual SQL to companies everywhere.