Tableau Tip Tuesday: Always Manually Backup Tableau Server Before Upgrading

Tableau Tip

Last night I upgraded a Tableau Server for one of our clients1.  They weren’t ready to go to 8.3 yet (they’re holding out for 9.0,), but we had agreed to upgrade from 8.2.1 to 8.2.7. Since an upgrade requires an uninstall of the existing Tableau Server, we also agreed to do it after hours.

When you uninstall Tableau Server it makes a backup of the internal database (including workbooks, extracts, users, and settings) and when you install a newer version it restores everything so the new version is ready to go with everything the old version had.  Usually…

Backup
(You probably see where this is going…)

 

Last night, I downloaded the installation package, ran a manual backup of Tableau Server, uninstalled the server, and ran the installation package.  But about half-way through the installation, I realized I’d made a mistake.  I had downloaded the latest installation package, which was 8.3 instead of the desired 8.2.7.

I rolled back the installation, got the right package and re-installed.  Everything seemed to go smoothly… until the install finished and I logged into the server – there were no workbooks, no extracts, and no users (except me).  It was a clean install.  Rolling back the installation had lost the automatic backup.

I sure was glad to have the backup of the server I had just run manually.  With that backup, it took only one command to restore the workbooks, extracts, users, and settings.  If I hadn’t made the backup, I would still be picking up the pieces this morning2.

It may be tempting to rely on the fact that Tableau Server backs up on uninstall and restores on re-install – but don’t!  Always make a manual backup prior to an upgrade.
The command is simple:

tabadmin backup backupfile.tsbak

 

And if you need to restore:

tabadmin restore backupfile.tsbak

 

You can learn more about backup and restore here.


1 I have been a consultant for Teknion Data Solutions for over 10 years!  It’s a great company and we’re hiring!

2 Of course, I have regular backups scheduled too, so it might not have been too bad.  ( that’s another tip :) )


 

Check back each Tuesday for another Tableau Tip…

Tableau Tip Tuesday: Don’t Use Table Calcs

Tableau Tip

No, not really!  I love table calculations! They are powerful and very useful.  They are one of the best things in Tableau.  Don’t be afraid of them and don’t avoid them, except…

  • Avoid table calculations when a simpler aggregate calculation will work.
  • Avoid aggregate calculations when a simpler row level calculation will work.

Why?  Because you lose control, flexibility, and efficiency.  When I first started to get the hang of table calcs, I found myself using them to solve every problem.  But that’s not the best approach.  The bottom line for this tip: keep it simple!

“Your table calc complexity will only grow.  There is beauty in it, but also great danger.”
“Your table calc complexity will only grow. There is beauty in it, but also great danger.”

Row Level Instead of Aggregate

If you use an aggregate calculation, when you could have used a row level, then you will never* be able to use row level logic on that calculation.  Additionally, aggregate calculations must be computed at run-time.  Row level calculations may be materialized in an extract, meaning that they are only computed once.

*the only exception is when you use a Set or fixed Level of Detail calc

Example:
You want the cost of goods sold when you have sales and profit.

Don’t do this:

Cost of Goods Sold as an Aggregate Calculation

You won’t be able to change it to an average cost of goods sold or compare a minimum and a maximum in the same view.

Instead, do this:

Cost of Goods Sold as an Row Level Calculation

As a row-level calculation, you’ll be able to specify the aggregation in the view.  It might also get materialized (computed once and stored) in an extract which means better efficiency.

Exception: When you specifically want to limit a designer using a published connection or web editing to a specific level of aggregation (e.g. you always want the designer to use average Cost of Goods Sold.)

Aggregate (or Row Level) Instead of Table Calculation

If you use a table calculation when you could have used a simple aggregate, then you introduced a level of complexity that was not needed and made it harder for others to understand.  Furthermore, table calculations are calculated based on field arrangement in a view. So any changes to the level of detail or layout of the view can fundamentally change the way they work.  Simple aggregates are done at the level of detail defined by the dimensions in the view, but the fundamental calculation remains unchanged when you rearrange, add, or remove dimensions.

Example:
You want to add a reference line for the SUM of Sales for Appliances across the table to give a visual benchmark.

Sales by Category

 

Don’t do this:

Table Calc to get SUM(Sales) for Appliances (assuming it's the first category)

Even if you can make the assumption that Appliances will always be first in terms of Category, how can you be certain that it will always be first based on the scope and direction of the First() table calculation when other dimensions are introduced?

Instead do this:

Aggregate to get SUM(Sales) for Appliances

As a simple aggregate, the calculation is less brittle and can be performed at the data source level.

Or, do this:

Row Level Calculation to get Sales for Appliances

Each row will keep the value for sales if it was an “Appliances” row (and NULL by default otherwise).  As a row level calculation, the results could potentially be materialized in an extract and is the most flexible.  You can choose to aggregate later.  And you can choose how.

A reference line at the value of Sales for Appliances

When creating calculated fields, always look for the simplest approach.  Check back next Tuesday for another Tableau Tip!

My Favorite Tableau 9.0 Feature

Tableau 9.0 has moved from alpha to beta and I’m loving it!  There are many new features to enjoy:

  • Improved calculation editor
  • Ad-hoc calculations
  • New functions (like regular expressions)
  • Data interpreter and cleanup tools like pivot and split
  • Drag and drop analytics
  • Map Search
  • New selection options (radial, lasso)
  • Responsive tool-tips
  • Formatting for Story Points
  • And more!

Many of these are incredibly cool and make Tableau that much more smooth and easy to work with.  I’m sure I’ll blog on many of these in the coming weeks.

But there is one new feature that is a true game changer

(I don’t use the term lightly)

Level of Detail Calculations

Previously, in Tableau, every aggregation was done at the level of detail based on dimensions in the view.  You could roll things up using totals and subtotals.  You could even get a little fancy using sets or table calcs, but these all had their limits.

Here, SUM(Sales) is calculated at the level of detail of Department / Category
Here, SUM(Sales) is calculated at the level of detail of Department / Category

No longer!

Now you can use Level of Detail aggregations.  Here are some basic examples:


 

Fixed: calculates the aggregation at the level of detail specified by the list of regardless of any dimensions in the view.

{FIXED [Department] : AVG([Sales])}

The above code calculates the average sales per Department, regardless what other dimensions are in the view.

Include: calculates the aggregation at the level of detail specified by the dimensions in the view and the dimensions included in the code.

{INCLUDE [Item] : AVG([Sales])}

The above code calculates the average sales at the level of detail defined in the view but includes the dimension Item, even if Item is not in the view.

Exclude: calculates the aggregation at the level of detail specified by the dimensions in the view, excluding any listed in the code.

{EXCLUDE [Category] : AVG([Sales])}

The above code calculates the average sales at the level of detail defined in the view but does not include Category the dimension as part of the level of detail, even if Category is in the view.


 

How about a real-life example?

Binning By Aggregate

You may remember that I had previously shared four possible ways of Slicing by Aggregate.  This is when you ask questions of “How many had how many?”

  • How many patients had how many readmissions? (e.g. I want to know that 500 patients were readmitted 1 time, 335 were readmitted 2 times, 23 were readmitted 3 times, etc…)
  • How many students had how many classes?
  • How many products had how many sales?

These types of questions were very difficult (almost impossible) to answer in Tableau without custom scripting or using some crazy options (non-Cartesian visualizations, table calculations, sets) because you have to aggregate the first answer and then use that to slice (or bin) another aggregate.

Let’s say I have a list of students and classes they are taking:

Students and Classes

I want to know how many students took how many classes (e.g. 9 students took 6 classes, 5 students took 5 classes, etc…)

In steps Level of Detail (specifically fixed) and solves my problem.  I simply create a calculation like this:

Classes per student

The field shows up as a Measure by default (but I can actually re-characterize fixed level of detail calcs as dimensions!)  Now I just create a bin (right–click the new field and select Create > Bins)

Create bins

And then I can build the view that answers my question:

Answer

This solution doesn’t have any of the drawbacks of the previous work-arounds.  It has nice UI, plays nice with action filters, and isn’t incredibly complex.

And this just barely scratches the surface of what Level of Detail calculations can do!  I’m sure we’ll be seeing a lot more of them in the future.

Tableau Tip Tuesday: The First Question to Ask

Tableau Tip

If you are using Tableau for data analysis, there is one question you really should answer for any data source before you can do any analysis and be 100% certain you are getting the results you think you are getting.  Here it is:

What is this number?
Number of Records

Or, more specifically, what is one record out of the 9,426 records in the data?  When you connect to a new data source in Tableau, before you do anything else, build the view above (simply drag and drop Number of Records – Tableau’s internally generated field that gives you the number of underlying records – onto the view) and say to yourself: “I have 9,426 records.  I have one record for every _____”  and fill in the blank.

I have one record for every:

  • Customer
  • Customer for every month
  • Website visit
  • Website visitor
  • Web page view
  • Product
  • Order of a product
  • Line item for every order of a product
  • Etc…

 

If you can’t answer the question:

  • Ask the team who developed the data model
  • Look for documentation of the source
  • Use Tableau to do some data discovery and figure it out for yourself

 

 

Why you need to answer the question:

  • So you will know if you have the right data to answer the questions you’d like to ask or if you need to look for or create other sources, re-structure the data , roll-up the data via extract, etc…
  • So you will know which aggregations, calculations, and filters give the right answers:
  1. Have a record for every unique customer and want to know the overall number of unique customers? It’s Number of Records or COUNT(Customer ID)
  2. Have a record for every unique customer per month and want to know the overall number of unique customers? It’s COUNTD(Customer ID)
  3. Have a record for every unique customer per month per store where the same customer might have different records (and different IDs) for different stores and want to know the overall number of unique customers? It’s COUNTD(Customer SSN) – or whatever uniquely identifies an individual.
  4. Have a record for every apartment available to rent and want to know square feet per city?  It’s SUM(SqFt) sliced by city.
  5. Have a record for every apartment available to rent for each month and want to know square feet per city? It’s AVG(SqFt) sliced by city or MIN(SqFt) sliced by city or MAX(SqFt) sliced by city, or SUM(SqFt) sliced by city and filtered to the current month, or use a different data source – it all depends on the exact question.
  • So you will understand when data blending is possible/necessary, when it isn’t, and how it will work with your data sources.
  • So you will know at what level row-level calculations are operating and that will keep you from unnecessarily complicating things by jumping to aggregate or table calculations.

 

That’s it.  That’s the first question.  It’s answered when you know the level of detail for your data source!  You’ll never be sorry to take a moment to answer it.

 

Check back each Tuesday for a new #Tableau Tip!

Tableau Tip Tuesday: Show What Isn’t in the Data

Tableau Tip

I recently came across this post that highlights the importance of seeing what isn’t in the data: http://blogs.hbr.org/2014/05/visualizing-zero-how-to-show-something-with-nothing/

In Tableau, rows, columns and marks are defined by records of data.  That means that most of the time, you are limited to showing what is in the data.  But there are a few options for showing what isn’t.

  • Show missing rows or columns.  From the menu select Advanced > Table LayoutShow Missing Rows / Columns.  What this will do is show headers for the domain of values present for the discrete field defining the header.  This is context sensitive, so you can limit the range with a context filter.
With "Show Empty Rows" on, every category is shown even though only a few have values based on filtering to a single department for a single city
With “Show Empty Rows” on, every category is shown even though only a few have values based on filtering to a single department for a single city

 

When the Department filter is added to the context, only categories within that department are shown.  However, they are shown even if other filters would normally exclude them.
When the Department filter is added to the context, only categories within that department are shown. However, they are shown even if other filters would normally exclude them.

 

  • Turn on “Show Missing Values” for dates and bins.  (Right click or use the drop down menu on the field in the view and select Show Missing Values). Since Tableau knows the min/max values and increments for a date or bin that defines a Row/Column header, it can fill in missing values and thus show you what’s missing.
Without the "Show Missing Values" option, only three dates are shown.
Without the “Show Missing Values” option, only three dates are shown.

 

With "Show Missing Values"  turned on, we see the gaps in time between sales of the item.
With “Show Missing Values” turned on, we see the gaps in time between sales of the item.
  • Pad the data on the back-end.  You can supply missing values by joining or unioning to give you the complete domain of a dimension.
Padding in Query
Here we want to ensure that “Dallas” and “Seattle” always exist in the data. We can combine this with “Show Empty Rows / Columns” to always see a row / column for Dallas and Seattle.
  • Pad the data in Tableau.  This one is harder as it requires an understanding of how different table calculations interact with dimensions on various shelves.  But sometimes, it’s your only option (e.g. you can’t touch your data source because it’s cloud based or locked down).  If you are interested in knowing more, do a search for Tableau along with the key words “Data Densification”, “Domain Padding” or “Domain Completion”.  Also, check out this great video featuring Joe Mako: http://vimeo.com/107354333

 

Check back each Tuesday for another Tableau Tip

Tableau Christmas Card Mail Merge

Christmas Cards

I love getting Christmas cards! But sending them can be a chore1. Fortunately, there is Tableau, a sophisticated-yet-intuitive-to-use rapid data discovery/analysis/visualization/story-telling platform which I will now use for mail merge2.  That’s right, just get a list of contacts, a stack of envelopes and a printer, and fire up Tableau.  Your days of laboriously hand-writing addresses are just about over3.

Step 1: Collect the data

This step is easy.4 The ultimate goal is a simple list of names and addresses like this5

Data

Step 2: Create the view in Tableau

This step is also easy.

View

I’ve placed every address field on Text.  Spacer is a calculated field which is hard coded to a bunch of spaces.  Return Address is a calculated field that allows me to use a return address entered via parameter.  There are several other key things to notice about the view:

  • I removed row and column dividers (because who wants to see that printed on the envelope?). To do this, I simply selected Format à Borders from the menu and then selected None for Row and Column dividers.
  • Name has been placed on the Pages shelf. Pages is one of the most under-used aspects of Tableau.  It can be used to create amazing motion charts.  Here, I’m using it to define a printed page.  A “page” defined by the Pages shelf is not always equivalent to a single printed page.  For example, a long text table may have a single “page” that spans multiple printed pages.  But the “page” defined by the Pages shelf gives a page break.  Here, my view is not large enough to worry that it will take more than 1 printed page to print a “page”.
  • I edited the text (click on the Text shelf). You’ll notice the Spacer calculated filed which gives consistent spaces (I could have just entered them manually) and the Return Address calculated field (which I could have also entered manually):

03 - Text

Step 3: Setup the Page and Print

From the menu select File à Page Setup.  The page setup is specific to a single view and allows you to set various options such as what elements of a view are included in the print, the layout, and print scaling.  The key here is to include only the view and make sure the option for printing all pages from the Pages Shelf is set.  On the Print Scaling tab, set the scaling to no more than 1 page down and 1 across and the page orientation to landscape.

04 - Page Layout

You may have to fight a bit with your printer – but at least the Tableau part was easy!

05 - Envelopes

 






—————————–
References:

  1. Actually, my wonderful wife does most of the work. I cannot even begin to express my gratitude!
  2. I recently asked on Twitter if there was anything that shouldn’t be done with Tableau. I got back some great responses. Look for a blog post soon with my answer – but in the meantime, no one specifically said, “Don’t use it for mail merge.”
  3. I know. It was really over in 2004 when everyone started sending e-cards.
  4. Do it however you like – use Alteryx, export to CSV from your CRM, do some web-scraping, type it in by hand, etc…
  5. The names are real, but the addresses are clearly fake. I use the real ones every year but haven’t gotten any responses. If you have any updated contact information, please let me know.  But I’m really hopeful that since I’m using Tableau this year that Data will respond.  Data always responds to Tableau.

Dynamic Annotations (Part 3)

Often, there is no “right” way to accomplish something in Tableau. There are often many ways to approach a solution.  Sometimes there are “better” ways.

I’ve been thinking for a while about a couple of my earliest posts (Dynamic Annotations part 1 and part 2).  It occurred to me that I had very much over-complicated the solution.  The more I understand how Tableau works, the simpler some things become.  It can be fun to struggle and come up with unique and creative solutions but it can be equally satisfying to understand why things work and have clarity on how to achieve certain results.

Dynamic Annotations is one such case.  Here is the easy way:

 

I had previously realized that a mark annotation was associated with a mark and would only show if the mark was shown.  My solution to turn annotations on or off was to show or hide certain marks using various calculated measures and dual axes.  Whoa!  Way too complicated…

What defines a mark?  When you see a bar or a circle or shape in Tableau or when you see the text “17 marks” in the lower left status bar, what is it that define those marks?

Tableau draws a mark for every intersection (tuple) of dimension values.

17 Marks based on combinations present in the data.
17 Marks based on combinations present in the data.

 

Various other factors can come into play.

Data densification can complete domains of dimensions and cause more marks to be drawn:

51 Marks based on  intersection of completed domains of dimension values
51 Marks based on intersection of completed domains of dimension values

 

Multiple axes can duplicate marks (once for each axis):

34 Marks based on intersection of existing values, (17 marks per axis)

Mark annotations are applied on a mark.  But what defines whether a specific mark annotation is shown is not just the intersection of dimension values.  There are two major factors:

  • The placement and order of any fields on the Rows or Columns shelf (the fields that define the X and Y position of the mark)
  • Any field used as a dimension on the Marks card or Pages shelf (fields that add additional values to the dimensional intersection / tuple).

The distinct combination of values for those fields defines a unique mark annotation which will only be shown for that specific set of values.

All of that may seem rather complex — but it leads to a very easy way to create dynamic annotations in Tableau.  You can have sets of annotations controlled by parameters or include fields as dimensions on the marks card that will change values based on action filters in a dashboard.  The possibilities are endless for dynamically controlling which annotations are shown.

Here’s how I created the sets of annotations shown above:  First, I created a parameter that allows the user to decide which set of annotations to see.  You’ll notice I used a list of integers with “Display As” text.

Parameter with 4 options
Parameter with 4 options

Then, I created a calculated field that simply returns the selected value of the parameter.

Calculated field that returns selected value of parameter
Calculated field that returns selected value of parameter

Then, I just placed that calculated field on the Marks card as a dimension (remember, that is one of the key factors).  Then, as I cycle through the parameter options I can annotate marks with whatever annotations I want to have displayed for that particular selection of the parameter.

Final

 

Wow!  That was far easier.

Honored and Humbled – Becoming a Tableau Zen Master

First, I need to say I have the best co-workers.  They are colleagues and friends.  This was on my desk when I returned to work after conference:

Zen-Garden

 

If you’re looking for a place to grow, learn, teach, and serve others — you can’t do better than Teknion Data Solutions.  Thank you to everyone there for all the support and opportunities!

When you listen to the response of newly inducted Zen Masters two words stand out: “honored” and “humbled.”

“Honored” makes sense when you first hear it.  It is indeed a high honor to be named Zen Master by Tableau.

But what about “humbled”?  Why is that the other term that Zen Masters are likely to use to describe themselves?  I’ll speak for myself.  Here are some reasons I feel humbled:

  • I’ve been named among many who I consider to be my mentors.
  • I work with and meet many people who teach me new things every day.
  • I stand on the shoulders of giants.  What I know is made up of what I’ve learned from others.
  • There’s a lot I still don’t know.  Zen Master doesn’t mean “mastery” in the sense that I know everything.  Far from it.  I’m still learning how much I don’t know.
  • There’s a lot I will never know.  And that’s okay.  I once naively thought that one really could know all there was to know about Tableau.  But even if you could master every last menu option consider all the areas Tableau touches: databases, data structure, analysis, statistics, R, GIS, writing code, visualization, design, server administration, and more!  Who can master it all?  No one.  Should we despair? No!  First, Tableau is so incredibly intuitive that anyone can start using it to create beautiful and useful things without a steep learning curve.  Second, I am incredibly grateful for a vibrant and dynamic community that freely shares their specific knowledge and helps each other.

Humility is a lesson I’ve learned and I’m still learning it.  A  couple of years ago, while attending my first Tableau conference, I was introduced to one of the original Zen Masters.  I was introduced as a “jedi” and I thought I was.  I had been using Tableau just long enough that I knew a fair amount but didn’t yet realize how much I didn’t know.  Sensing my know-it-all attitude, this individual patiently showed me a few things I had never considered and then kindly invited me to get in touch if I wanted to understand it more.

It ignited a passion in me to learn more, deepen my understanding, and help others with the knowledge I gained.  Along the way, I’ve continually been humbled as others have patiently and kindly corrected my “correct answers” on the online forums,  challenged my assumptions, or confirmed my understanding.  I am incredibly grateful.

Honored? Yes.  Humbled? Definitely.

 

Blackjack! (Tableau deals)

Can’t wait for the Tableau conference next week year? How about playing a game of blackjack against Tableau in the meantime? No, you won’t win real money. But you can sharpen your skills in case a future since the next conference is in Vegas.

By the way, this dashboard uses a little known fact that secondary data sources are many times refreshed only from the cache even when the primary sends a new query to the data source. Want to discuss? Ask me questions, provide feedback and critique in the Tableau Community Viz Talk

Just remember to press F5 (refresh the browser) after you play — or the cards won’t be shuffled (of course, you could use that to your advantage by just pressing the revert button – though sometimes the dealer still likes to shuffle).

The Story of the Second Punic War

I love history, but I find it difficult to remember all the details. That’s mostly the way it’s taught and presented. Lectures, lots of reading, lists of names and dates, facts and figures.
But all of that can be told visually as a data story! And that makes it easier to remember and understand.

So, here’s my contribution for Storytelling month at Tableau Public. I’ve always been fascinated by how close Hannibal came to conquering the Roman Republic. I’ve thought repeatedly about crating a dashboard to explore the battle of Cannae (ever since reading Cannae by Adrian Goldsworthy.) With Tableau Stories, telling the story just got a lot easier.

By the way, did you know that Charles Minard plotted Hannibal’s invasion of Italy?

Minard

 

Of course you did.  But I didn’t, until after I had created the dashboard.  Of course, I was inspired by his more famous visualization of Napoleon’s March.  And his representations are much more precise than mine.  I went for accuracy, but was a little loose with precision (especially latitudes and longitudes — in other words don’t use my numbers for your doctoral dissertation).

And here it is:

(I’ll come back to Tic Tac Toe, I promise!)