Wednesday, October 31, 2012

Inconsistent Chart/Table Formatting

I'm frequently asked to provide an auxiliary table containing the specific numbers for the marks in a specific chart. And it's quite often a good analytic information design choice, better than simply displaying the chart's mark labels.

Creating the table should be an easy, even trivial chore: duplicate the sheet, drag the measure from the Rows or Columns shelf onto the Marks' Text shelf, and Presto! we have a table matching the chart with numbers in the cells instead of marks.

Only it doesn't quite work out the way I intended.

True, the cells do now contain the measure's numeric values, which is the primary point.

But the overall table formatting is very dissimilar to the original chart formatting, and getting them all polished up and in sync in order to present a Dashboard showing a coherent, consistent design requires a fair bit of fiddling about.

Notable elements that need to be changed include:

  • Name alignment – right-aligned in the chart, left-aligned in the table. Either one may be suitable in a given situation, but they need to be the same.
  • Row shading in the table – the default row shading is one of my little itches, and its presence here is particularly irksome.
  • Column dividers – present in the chart, absent in the table. Again, they should be the same.

All in all, adjusting the table formatting to match the chart isn't that big a deal, but when one is building out a fair number of dashboards the amount of persnickety work can really interfere with the creative flow.

Possible fixes

  • Duplicate a sheet's formatting along with the sheet itself.
  • Provide a mechanism for establishing and managing the default formatting for the various via types. Tableau already keeps and knows how to apply these, it would be a relatively simple matter to surface the ability. (of course, we;ve been waiting years for the surfacing of similar internal mechanisms, e.g. data source management, including global filters)
  • Provide the ability to copy and paste the formatting from one viz to another – this goes hand in hand with the previous format management point. (to be honest I have a hint of a suspicion this might already exist, but I don't know how to do it)

Monday, October 29, 2012

Tableau: Know Thyself. And Please Tell Me.

Is there any way to get a list of all the Worksheets and Dashboards in a Workbook as an output from Tableau?

This question was posed on the LinkedIn Tableau Software Fans and Friends discussion group recently. It echoes one of the fundamental needs that arises once Tableau's been in use for a while - knowing what's in your Workbooks, and where it is.

The simple answer to the question is: No.

Or at least not directly. But you you can find out. If you want to get right to the get-the-answer part, jump down to the answer.

Tableau Desktop has no self-analysis abilities, no way to let you know what's in the Workbook you're looking at other than by browsing through it with the UI. One can simply walk through the list of Dashboards and open the Worksheets in each, recording their names as you go, but that's a boring, mechanical, error-prone process that's maddening in its ratio of effort to information achieved.

Tableau exists to make it easy to understand data. And the information about the relationships between Workbooks, Dashboards, and Worksheets is easily rendered as data, so it seems a natural fit for Tableau to be the vehicle for understanding the relationships. The fly in the ointment is that Tableau doesn't provide the data.


Five or so years ago I was faced with the problem of determining what Dashboards were in the Tableau Workbooks my client had. Some of them I'd built, some of them were other people's creations. With a little poking around, and some connecting with people in the Tableau forums I found out Tableau Workbooks are XML files, and that other people were looking into how to tease this information out of them. Andy Cotgreave was exploring XSLT, which I'd started with but had to abandon when the cross- and back-references got too complicated for my limited skills. I built the Tableau Workbook Inventory System with Java, and over time bundled all sorts of functionality into it. Andy continued on and created his Access-based TWB Auditor.

TWIS and the TWB auditor both do the job, and lots more. But they might be too much tool for the many people who simply want the simple facts about what's where.

Simple and Easy - No Muss, No Fuss, Just the Basics.

I've been using Ruby quite a lot lately for data gathering and munging. Ruby makes it easy to browse the WWW and pull out lots of data that's not otherwise directly addressable by Tableau—e.g. HTML tables with embedded tags that Tableau sees as data delimiters.

It was pretty straightforward to put together a Ruby script to go through a set of TWBs and pull out their dashboards, worksheets, and the relationships between them.

The Answer: 37 lines of Ruby code.

Give Tableau some Ruby slippers and watch it dance.

The code immediately below is a Ruby script that will open every TWB in the current directory and pull out the Dashboards and Worksheets, and their relationships, into a CSV file named "TCC_BDW.csv". A description of how to use it and what it produces follows the code.

If you would like a ready-made script, along with a Workbook containing a starter Dashboard showing the Workbooks, Dashboards, and Worksheets, you can download it from here.

# TCC_BDW.rb # This Ruby script Copyright 2012, Christopher Gerrard require 'nokogiri' require 'open-uri' $recNum = 0 def init $f ="TCC_BDW.csv",'w') $f.puts 'Record #,Workbook,Dashboard,Worksheet' unless $f.nil? end def pullSheets twb doc = Nokogiri::XML(open(twb)) sheetNodes = doc.xpath("//workbook/worksheets/worksheet").to_a sheetNames = sheetNodes.to_a.collect! { |s| s.xpath("./@name").text} dashNodes = doc.xpath("//workbook/dashboards/dashboard") dashNames = dashNodes.to_a.collect! { |d| d.xpath("./@name").text} dashNodes.each do |d| dashName = d.xpath('./@name').text zones = d.xpath('.//zone[@name]') dashNames.delete(dashName) unless zones.empty? zones.each do |z| zName = z.xpath('@name').text putCSV twb, dashName, zName sheetNames.delete(zName) end end dashNames.each do |d| putCSV twb, d, 'z...' end sheetNames.each do |s| putCSV twb, 'z...', s end end def putCSV(workbook, dashboard, worksheet) $recNum += 1 $f.puts "#{$recNum},\"#{workbook.gsub('"','""')}\",\"#{dashboard.gsub('"','""')}\",\"#{worksheet.gsub('"','""')}\"" unless $f.nil? end init Dir.glob("*.twb") {|x| pullSheets x } $f.close unless $f.nil?

How to use TCC_BDW.rb

  • Prerequisites
    • Minimal technical skills.
    • Have Ruby installed and ready to run.
    • Have the Nokogiri Ruby gem installed—it's used in the XML parsing.
    • Have TCC_BDW.rb in place—it doesn't matter where, or what name you use, as long as you know where it is.
      You can:
      • Copy the code above and paste it into your favourite text editor, or
      • Download the existing one, along with the Dashboard/Worksheet Tableau workbook, from here.
  • Running TCC_BDW.rb
    • Open a command prompt.
      (you can run it otherwise, but this is simple and straightforward)
    • CD to the directory containing the Workbooks you're interested in.
    • Run it: "[path to]\ruby    [path to]\TCC_BDW.rb"
    • If you downloaded, unzip it and copy its TCC_BDW.twb to the current directory.
  • Open TCC_BDW.twb and see your Workbooks, Dashboards, and Worksheets all nicely organized.

What's in TCC_BDW.csv?

–or– what ARE the relationships between Dashboards and Workbooks?

The standard idea is that Dashboards contain Worksheets. This is true, but it's not complete. Some Dashboards contain no Worksheets, these are childless. Some Worksheets are contained in no Dashboards, these are naked.

In collecting the TWB information, TCC_BDW accounts for childless Dashboards and naked Worksheets thus:

  • For every childless Dashboard, its record in TCC_BDW.csv contains the value "z..." for the Worksheet field. "z..." was chosen because Tableau's data sorting places after the other alphanumeric and special characters, and therefore it gets put at the bottom of Tableau's natural listings.
  • Similarly, each naked Worksheet gets recorded with "z..." in the CSV Dashboard field.
Here are TCC_BDW.csv's first dozen lines when run against the Tableau v7 Sample Workbooks:
Record #,Workbook,Dashboard,Worksheet 1,"Finance.twb","Economic Indicators","sp1" 2,"Finance.twb","Economic Indicators","sp2" 3,"Finance.twb","Economic Indicators","sp3" 4,"Finance.twb","Economic Indicators","sp4" 5,"Finance.twb","Economic Indicators","sp1" 6,"Finance.twb","Investing in DJIA","Investment Growth" 7,"Finance.twb","Investing in DJIA","Investment Growth" 8,"Finance.twb","Investing in DJIA","Investment Growth" 9,"Finance.twb","Tale of 100 Entrepreneurs","Top IPOs 1" 10,"Finance.twb","Tale of 100 Entrepreneurs","Top IPOs 2" 11,"Finance.twb","Tale of 100 Entrepreneurs","Top IPOs 1"

Here's a screen shot of TCC_BDW.twb showing how their Workbooks, Dashboards, and Worksheets are related:

Other Mysteries –or– what else can we get Tableau to tell us?

There are plenty of other things that Tableau can tell us, e.g. where and how data sources are used, field calculations–useful for ensuring consistency in metrics, where parameters are used. The list is long.

The list is a very long one, and as long as Tableau won't tell us on its own there will be ways to figure things out for ourselves.

Saturday, October 27, 2012

Disappearing Input - Data Extract Dialog "Top" Value

I meant what I said, and I said what I meant.

Does Tableau forget what I just told it? Or does it decide that I didn't really mean it? Either way, I meant what I said, and I said what I meant, so Tableau Should remember and not make me say it again.

What I want

In this case, I'm trying to extract a percentage of the source data, and I want to pull out 10% of it, so I invoke the Extract Data dialog and enter "10" into the "Top" input area, as shown on the left in the image below. However, I want 10 percent of the data, not 10 rows, so I pull down the pulldown, as shown by the arrow pointing to the pulldown.

Tableau forgets

As shown on the right side, once "percent" is selected from the pulldown, Tableau forgets that I entered "10", and the input area is cleared.
(Or worse, Tableau decides that i didn't really want 10 percent, so it makes me enter the value I really wanted—this would be very not good and I choose not to think it's the case)

General Principle

The User should not have to repeat themself—Tableau should remember and not need to be told again.

Monday, October 22, 2012

Miscellaneous Mapping Vexations

Tableau doesn't recognize the following as legitimate geographic entities:

  • England
  • Wales
  • Scotland
  • Ireland
  • Northern Ireland
  • Antilles
  • Ascension Island
  • Azores
  • Canary Islands
  • Herzegovina
  • Northern Marianas

It's really hard to accept that England, Wales, Scotland, Ireland, and Northern Ireland aren't geographically legitimate.

While we're here, the UI for providing lat/long values is pretty weak.

  • It doesn't accept vales in the form "13°10′N 61°14′W", or at least it didn't work for me. Tableau should be smart enough to parse this form.
  • And even when the lat/long are available in the form "51.481581,-3.17909" you can't simply paste them from the clipboard into the up front field, you have to go through the bother of entering them individually into their separate lat and long text areas as a secondary action.

Tableau has made great progress in its mapping abilities in the past few releases, but there's still plenty of lubricating to be done.

Where's my viz? When Desktop hides the current viz's tab.

It's pretty frustrating to create a new viz—worksheet or dashboard, and have Tableau decide that it's not going to make the viz's tab visible in the tab bar.

This happens pretty frequently, particularly when the workbook has a fair number of dashboards and worksheets in it, and is most annoying when building a new viz and need to change its name from the default.

Similar to the previous post about the need to show the active element in the table list, this is a violation of the usability principle of providing the affordances with which the User can manipulate the objects in their current context.

Friday, October 19, 2012

Umm, what's that data source?

It should be easier to find out what data is being accessed.

Here's the Tableau Data Connection right-click menu with its options to see and configure the data connection:

Selecting the "Edit Connection..." option, you see this:

It would be better to see this:

In the improved version on the right the current table is shown in the list when the dialog is presented.

This might seem like a little thing, but when working with real business databases there may be many, many tables in the list and navigating to the currently selected one becomes an exercise in frustration.

General Principle

Whenever possible, present the current state rather than make the User hunt for it.

Monday, October 1, 2012

Text Editing Idiosyncracies

This is going to be a very live post - I've not written this stuff out before because the individual bits seem too insignificant to note separately, but then they never get recorded.

Editing viz names in thumbnail workbook view doesn't observe normal navigation keyboard shortcuts, e.g. ctrl-<right> & ctrl-<left> don't work but ctrl-<home> & ctrl-<end> do.

Title editors - don't accurately copy/paste text contents, sometimes adds an extra line.

The Annotation Editor:

  • Text selection: select all text, set font size, close/save editor, open editor, select all text (<ctrl>-a, text is selected but previously set font size is not honored.