The Excel Power User’s Guide to Tableau: Bring Your Towel

Far out in the uncharted plains of the Gartner BI Quadrant of the data visualization software options of the data universe lies a small unassuming enterprise piece of software. Approaching this at a volume of roughly ninety-eight million rows is a seemingly ill-equipped analyst whose Excel-descended visualizations are so amazingly retro that users still think exploding pie charts are a pretty neat idea.

The move from Excel to Tableau is quite a transition.  You see, Excel focuses on generating calculations at the cell level, hence the ‘Cel’ part of its name.   Each cell contains a unique calculation, whether it be raw data that’s manually generated (“Hitchhiker’s Guide”) or tabulated from an array (=SUMIF(C8:C16,”Hitchhiker’s Guide”,D8:D16) to get 42) or pulled from another source (LOOKUP, GETPIVOTDATA).

While data can be dumped in Excel from a number of sources, such as SQL, cubes, and other intergalactic stores, its presentation is ultimately gathered from cells.  These cells can be hodge-podged into some type of visualization (exploding pie charts, small planets, intergalactic freeways, etc) but they are usually independent of each other and run off calculations somewhere at the cell level or via pivot tables.

At some point, lobbing potatoes ceases in sprouting success.  Potato launchers only fling the spuds so far, and the green ones can only take out some many windows at a time.  Tableau offers something more, but it doesn’t quite work the same.

You see, Tableau DOESN’T operate at the cell level.  Its massive freeway system overtakes the galaxy via a funny thing called VizQL.  VizQL, like its far removed cousin, SQL, is a semantic language that relies upon the creation of a view via structured queries that uniformly lay out the data in some logical fashion that then interact via a series of actions as part of a dashboard.  Huh?

Let’s go back a tick.  Perhaps as far back as the pub where I didn’t grab a towel.

You see, when we were building Excel dashboards, we ultimately had to answer The Question.  It could be the question our boss had, or perhaps our chatty coworker who sent us 6 hours of email, or even someone on the far edges of the country, of whom we’ve only ever met once.  We built the dashboard to answer The Question.  As Excel is cell-based, we learned we had to map out the answer first, then build, then understand.  Any changes were slow and painful, so building RIGHT the first time was essential.

Tableau works differently.  At the far left of the program lives the data source, the heart that powers our work.  Below that are dimensions.  Now, dimensions are just bits of data like names, phone numbers, and information that we may use to slice and dice, but not add or aggregate directly.  It’s qualitative in nature.  It tells me the titles of Douglas Adams’ books, but not how many.  To see how many, I’d want to look at Measures or use VizQL.  Measures I can add, average, and aggregate to my heart’s content.  This is still at the source side.  As are parameters and sets, but we’ll explore those another time.

In the grey expanse next to my data source is the galaxy of VizQL.  I have Cards for Pages, Filters, and Marks.  Pages are a bit deceptive in name and nature, so we’ll hold on that, too.  Filters, like Excel filters, allow us to prune out data we don’t need.  The Marks Card, however, is unlike anything we’ve ever seen.  It controls just about everything within the view.  Just about, I say, because we also have Columns and Rows above our canvas.  Those buggers act as the primary slicers of data and seem like a pretty logical place to drop fields.  It looks like a pivot table almost, but with no place to drop my measures.  But wait, maybe that’s what I could do with the Marks card…I also have a Canvas.  It, too, reminds me of a pivot table.  I could drop things there as well.

I have some placeholders for other cards, but for now, I’ll pay them no more mind.  If I have a small screen, I can move some of the cards into these spaces.  I can also close cards and get them back at any time by selecting from the upper menu “Worksheet | Show Cards.”   Lastly, I have this “Show Me” character.  It looks rather dodgy.

But wait!  This gives me all the charts I need, right?  Well…maybe?  Surely, it’ll get me started.  If I were in Excel, I’d have a lot more options for formatting and wizards upon wizards to format it.  The Marks card might do.  I did see color there…and text…and some other things.

Now, what is this VizQL stuff?  I keep hearing about it.  I’m not a programmer: my degree is in interpreting!  Surely, I’m not going to spend another 3 years learning calculations…

You see, when the time came to knock down a few planets to make room for an intergalactic data visualization highway, Tableau (as in “dramatic picture,” not French for “table”) decided to let users bypass this whole cell stuff and instead look at data in a window.  It’s like a pivot table on steroids…or maybe asteroids.  This “dramatic picture” allows characters like color, size, and even shapes to take on meaning.  What a way to end a planet, yes?

In the world of Tableau, the analysis can occur in a number of different places:

1. At the source level via many calculations: Things like strings, conditionals, and certain aggregations are done at the source.
2. At the window level via visualization, table calcs, and other select calculations: Yes, you can even feel a bit at home with the cell-level calc, though the pub is gone.  Sometimes, Arthur, you just can’t go home again.
3. Via interaction at the dashboard level: This is the part of the galaxy that is unfathomable until you lay your eyes upon its starry  beauty.

You see, data viz is big.  Really big.  You just won’t believe how vastly hugely mind-bogglingly big it is.  I mean, you may think it’s a long way down the row to the record, but that’s just peanuts to data viz.  Now, Tableau allows the viewing of this data simply and easily, just by pressing the button, or dragging pills (fields from the data source) onto the canvas, rather.  Though, you can double-click them on there if that’s your thing…

When creating a calculation at the source level, such as a standard logic calculation (IF…THEN…ELSE…END), it’s typed once, stored as a field, and then applied to every single row in the source.  No dragging, absolute values, or cross-sheet links required.

Table calculations are reflexive to the view that is created on the canvas.  They can be created and have their direction altered with just a couple clicks – no coding required.

But, the magic of Tableau isn’t calculations or even the views created within worksheets.  No, the secret of the universe exists within the interactive power of dashboards.

One view, or worksheet, shows sales by year.  Selecting 2014 drills down a number of the other views, including the map.  Hovering on the map not only shows a tooltip, but filters the Profit Ratio by Region.  This is what ’42’ really means: it’s the answer to life, the universe, and everything.

You see, when Excel exploded and left only dust, Tableau offered a chance to explore the galaxy in a new way, sans blue screens and scripted maneuvers.  But, it requires a change in approach.  We don’t give a dingo’s kidneys about the chart, calculation, or even what we think we know.  You see, the Guide tells us, “The history of every major Data Visualization tends to pass through three distinct and recognizable phases, those of Survival. Inquiry, and Sophistication, otherwise known as the How, Why, and Where phases.”

With Excel, we had to focus on Where and How, leaving Why to another time.  If we explore as Tableau allows us to, we’ll likely find the Why.

So, how do you survive a trip to the galaxy?

1. Always bring your towel.  Or at least grab the question if there’s no towel in sight.
2. Recognize that the home planet is gone.  The old ways of doing things are a bust.  Yes, some things may be similar, but the tea you think you’re drinking isn’t really tea.
3. Use your babel fish to understand and accept you may still be lost.  Even if the lingo seems familiar, the concepts are all new.
4. Consult the Guide and the guide.  Tableau’s Knowledge Base, forums, and user groups are a great place to start for materials, but Ford Prefect lives on Twitter or may be your best Tableauni.  Use both.
5. Start living in the Galaxy.  Nothing will ever be like home was, but the Galaxy is a fun place to live and explore.  Many a great adventure awaits, if you’re ready to launch.