Custom SQL in Tableau: Subqueries and SQL Injection

I recently answered a question on the Tableau Community forums that arose from confusion over why some (perfectly correct) SQL wasn’t working as custom SQL in Tableau. The poster wanted a list of Tableau’s supported syntax.

But as it turns out, that’s the wrong question: Tableau doesn’t have a list of all the custom SQL syntax it supports because it really is just passing along the SQL code as you’ve typed it.

So why would a perfectly reasonable custom query fail? And what’s the link to SQL injection? Read on!

Continue reading

Row-Level Security: A Cautionary Tale

Row-level security is a common requirement for people trying to control access to data. Some systems provide this natively, but when it’s not provided, people often roll their own using the tools they have—with mixed results

In this post we’ll explore a common way to implement row-level security on top of a relational database and see why it may not be as secure as it looks.

A Pop Quiz

But before we get to the crux of the issue, here’s a quick quiz. I promise it’s relevant.

What will each of the following languages do when a is equal to 0​?

  1. C, C++, C#, Java, and most other C-family languages:
    if (a != 0 && 1/a > 0) { /* Do something */ }
  2. Pascal:
    IF a <> 0 AND 1/a > 0 THEN (* Do something *)
  3. SQL:
    SELECT *
    FROM T
    WHERE a <> 0 AND 1/a > 0

Obviously, I’m asking about short circuiting behavior. I’ll let you ponder and reveal the answers in a moment. But first, back to row-level security.

Continue reading

A Dozen Notes, Give or Take

Introduction

Western classical music typically uses twelve distinct notes: C, C#, D, D#, E, F, F#, G, G#, A, A#, B. After this, you hit an octave and repeat.

It always bugged me: why twelve?

Here I try to provide an answer with a few rules and a few graphs. This is a little hand wavy, I’m sure that it’s not original, and I know someone who knows more could make this more convincing and more rigorous.

But this is the internet. Who cares?

Continue reading

Tableau Data Sources: Live vs Extract

Continuing last-week’s trend, we’ll again take a look at an aspect of Tableau that people often find confusing: the difference between live and extracted data sources. And again, we’re going to take a bit of a database perspective to clarify the situation.

The impetus for this post is a number of statements I’ve seen along the lines of:

A live data source is just a real-time extract of your data.

This is my favorite kind of wrong: subtly wrong.

Continue reading

Dimensions and Measures: A SQL Perspecitive

I thought I’d kick this off gently. I remember going through Boot Camp after joining Tableau and learning about dimensions and measures. And I remember finding the descriptions rather confusing.

I don’t recall the precise phrasing, but it went something like this:

Dimensions are usually those fields that cannot be aggregated; measures, as its [sic] name suggests, are those fields that can be measured, aggregated, or used for mathematical operations.

Or this:

Measures are the result of a business process event… Dimensions are reference variables that give context to measures.

I don’t really mean to criticize these definitions, but to a database guy, they seem rather imprecise. For someone with a little SQL know-how, the actual definition is both crisp and helpful in understanding what Tableau really does under the covers—this helps predict what actions in the UI will do, so you don’t just blindly drag-and-drop until things look right.

The rest of this post is a crisp explanation of dimensions and measures for someone who knows a little SQL.

Continue reading

Hello World!

It’s really: “Hello again!”

A long time ago in a job not so far away, I kept a blog over at MSDN. Back then, I was a new program manager in SQL Server, and that blog really caught it’s wind as we were pulling together SQL Server’s geospatial support. After I left that project, things just sort of trailed off.

Over the years, I’ve considered starting up a blog again, but I’ve struggled to come up with a theme that was coherent, current, and that I could actually write about publicly.

But recently, I’ve started to think this is too high a bar. My goal with this new blog is merely to write a weekly post on something interesting to me. It’s selfish, but I hope you find it interesting enough to read along.

So buckle up—it might get bumpy.

And welcome!