Tuesday, May 12, 2015

Need to know your Custom SQL? Use the new Tableau Tool - ExtractCustomSQL.rb

ExtractCustomSQL.rb

Will scan your Workbooks and TDS files, searching out, extracting, and recording the Custom SQL they contain.

It's available on GitHub here.

It's Tableau Tools wiki page is here, providing the basic information about it.

There's not much in the way of visuals to show for this tool. The Tableau Sample Workbooks don't use Custom SQL, and most of the Custom SQL in my usual Tableau archive isn't something that can be released into the wild.

Tableau Wasted?

It's not development, dagnabbit.

I've been working with this client for several weeks. They've bought into Tableau as a really good and valuable thing, and are moving ahead like gangbusters. Tableau Desktop licenses are being distributed like Hallowe'en lollipops. Things are happening, people are excited and engaged. We're building out the Tableau Server environments, putting 9.0.1 into production this weekend.

It's a good, robust environment, where people are taking advantage of Tableau to help improve their data sense-making abilities. Everything's good.

Or is it?

This came in an email today from someone who's been involved with Tableau for a while and recently received a Desktop Tableau license:

"Do you need a different license to do Tableau development? Or is the desktop license what you need?"

Whoa, mule!

This is alarming. This little piece of information is highly revealing, it reinforces one of my suspicions; that overall, even though the company is rapidly adopting Tableau as if it was the best and greatest thing to come down the pike (and it is), they really don't have any idea what it's about and are missing the big point, and along with it the opportunity to take advantage of most of Tableau's value.

Here's my reply to the email:

It’s actually more helpful to think of working with Tableau as authoring rather than as development.

In traditional software development the “development” activity is at the tail end of the production process, largely divorced from the real analytical work of coming up with the valuable outcome: analyses that reveal and communicate information. Development is the technical implementation of someone else’s ideas.

Tableau provides the opportunity for one to work at the creative intersection of cognitive, intellectual, and experiential factors that, when working in harmony, can synthesize the information needs of the person seeking to understand the data and the immediacy of direct data analysis. This mode of Tableau use can eliminate the lags and friction involved when there are multiple people between the person who needs to understand the data and the person who creates the vehicle for delivering the information from which insights are gleaned.

It –is– possible to use Tableau as a traditional development tool, one that enables technicians to develop dashboards and other analytics faster than with many other technologies. And this use has a valuable place in the overall data analytical environment, but limiting Tableau to this role, or even employing it predominantly in this way, misses the great bulk of the opportunities Tableau provides to help people understand their data with a minimum of fuss and bother.

When someone works with Tableau to explore and understand their data, or in intimate collaboration with the information-seeker, Tableau use becomes a dynamic, creative, cognitive and intellectual exercise, and the creation of analyses that reveal and communicate valuable information and insights is an act of authorship.

Helping people and organizations take advantage of what Tableau offers is a very satisfying way to earn a living. But it's not without its speed bumps and detours. History and experience are very sticky and persistent. Sometimes helping clients recognize that there are new ways of thinking about the world, of the possibilities that exist, the new ways of doing things, can be a bigger challenge than is obvious at the start of the relationship. Fortunately, most people aren't unwilling to get better results quicker, easier, and with less cost, but it can take time and familiarization for the possibility to be recognized.

I love my work.

Monday, May 11, 2015

New Tableau Tool - Extract and Record Field Comments

It was a dark and lazy evening. Nothing much happening. Then things got Interesting. Inspiration knocked on the door, and without waiting for Invitation walked right in.

Seems that Tableau's been keeping some information to itself: comments. Comments about Fields. Information that could, in the right hands prove very useful, even valuable. Inspiration wanted me to come up with a way to get Tableau to give up its comments, and who can resist Inspiration?

The thing is, Tableau doesn't give up its secrets easily. Sure, you can open up a Workbook, then a Worksheet, pick a Data Source, right-click on a Field, navigate down into its menu items and convince it to fess up, spill the beans, give up the goods, sing. But this isn't efficient, and it makes is hard to get the whole story, the straight dope, the full shenanigan. Looking as isolated comments is no way to build a comprehensive integrated overview of the entire metadata landscape.

It didn't take much coaxing; I always was a sucker for an insistent Inspiration. So I pulled out my trusty Twb gem and whipped up a brand spanking new Tableau Tool—ExtractFieldComments.rb, custom crafted to scan a set of Workbooks and locate and record the comments for all the fields that have them. Didn't take long, I was eager to get back to finding out what Inspiration had in store for me next.

Get it here
at Tableau Tools on GitHub: https://github.com/ChrisGerrard/Tableau-Tools/blob/master/Ruby/ExtractFieldComments.rb

ExtractFieldComments.rb in action.

Here's what it looks like when it's run in a directory containing the Tableau Sample Workbooks from versions 8 and 9:

As we see, ExtractFieldComments.rb walks through the Workbooks. While it doing so it's showing the different Data Sources as they're encountered. It's not showing the Fields as they're examined–doing so would produce too much output with too little information–but it's recording all the Fields that have comments, along with those comments into the CSV file TT-FieldComments.csv.

Being a CSV file, TT-FieldComments.csv can easily be opened and analyzed with Tableau. Which is, after all the point.

Understanding the Comments

This Tableau Public published Workbook provides a sampling of the ways in which the comments for your Workbooks can be collected, organized, analyzed, and understood. And all comments want to be understood. One of my mentors was fond of saying: "There aren't any bad comments, just some that are misunderstood."

What's next?

There are plenty of additional Tableau Tools in the pipeline. Many of them are versions of scripts I've published here reworked to take advantage of the Twb gem. Some of them are brand spanking new. I'd really like to hear any ideas from the community about useful tools. And I'd really like it if anyone else want to take a stab at building your own Tableau Tools — that's what the project's all about.

Monday, May 4, 2015

New Tableau Tool – Reset Field Names

This tool was prompted by this Tableau community forum discussion: In 8.2 - how do I stop Tableau from renaming all my fields?

The person who posted the original discussion was complaining that Tableau was automatically renaming the fields upon first connection to the data, e.g. the field was_this_visit_callable was renamed Was This Visit Callable.

The behavior was introduced in Tableau 8.1 (per Tableau support via Tom W).

It's not unreasonable to think that renaming fields from their technical names to user-friendly names has value, particularly in those environments where the database people don't want to, or can't, give human-oriented names to the database fields. But there are circumstances where it's not appropriate and when done forces people to either live with field names they'd prefer not to have, or exert the time, energy, and effort to undo the changes Tableau automagically made.

Adjusting field names to make them more human-oriented isn't in itself a bad thing, but Tableau's implementation of the feature is lacking in several important ways, including:

  • it happens invisibly—Tableau changes the names without informing the person who connected to the data that it's done so;
  • it's an imposed functionality without an opt-out ability—there's no mechanism whereby the User can choose for this to happen, or not;
  • there's no way to control the adjustments that Tableau makes, which are (at least, and in this order)
    • replacing underscores '_' with blanks '  '
    • upper-casing the first letter of each discrete word

We can't fix Tableau but we can fix the names.

ResetFieldNames.rb
is a Tableau Tool that will find all of the renamed fields in your workbooks and remove the customized names, leaving the database names as the ones the User sees.

About ResetFieldNames.rb:
  • It's written in Ruby and is available from GitHub here.
  • It uses the Twb Ruby gem, available via RubyGems.
  • It's surprisingly small, only 34 functional (not comments) lines.
  • Is run from the command line:
    ruby ResetFieldNames.rb
  • Will reset the field names in selected Workbooks:
    • by default – all of the Workbooks in the current directory;
    • the Workbooks can be identified on the command line, e.g.:
      ruby ResetFieldNames.rb 'some*.twb'
      using file patterns to identify groups of fields.
  • Will not reset Calculated field names; although they have technical and User names, they aren't database fields and their technical names are undecipherable for normal humans.
  • By default writes the reset fields to a copy of the original Workbook with '.reset' appended to the Workbook's name, e.g.
    Science.twb -> Science.reset.twb
    this can be changed so that the Workbook will be replaced when written, or other text can be provided for appending.
  • The reset fields will be recorded in the CSV ResetFields.csv with the fields:
    Workbook,Data Connection,Field Name,Field Caption
    making it easy to determine which fields were changed, in which Workbooks.

Considerations and potential enhancements.

Indiscriminate
ResetFieldNames resets all the fields to their database names. It would be really helpful if the fields could be selectively reset, making it easier to get them configured just right. There are many ways to accomplish this, one easiest and most transparent method would be to use an Excel/CSV file as the input identifying the field names to reset.

Considerations and potential enhancements.

Please add your comments here, or to ResetFieldNames.rb's Tableau Tools wiki page on GitHub


I hope you find this Tableau Tool useful and valuable.