Monday, December 17, 2012

Changing the Database Server Name in multiple Workbooks

It's good practice to use Tableau against a non-production database in many circumstances, e.g. when the database is under active development, you're early in the data exploration phase, or access to the production database is restricted.

Tableau workbooks tend to multiply, and their data connections in them multiply apace.

Changing the properties for a data connection or two isn't difficult, nor is it terribly burdensome. But it's a mechanical process, and after making the same change through the UI more than a couple or few times becomes boring, and when there's more than a few Workbooks it's really difficult to keep track of all the connections that need to be changed, those that have been, and those that have not yet been.

Here's a way to change the database server for a whole collection of Workbooks all at once.

Simple and easy, and in keeping with the small-apps approach, I've created a small Ruby app that scans all the Workbooks in a directory, locates those data connections with a server specified by name, and change the servers' names to a new one.

How it works — the "before" picture:

In the top section of the image below is a list of the Workbooks in the current directory. The bottom section shows the server names of the various database connections in these Workbooks.

For this post, we're going to change the "Ora.development.env.server" server names to "Ora.production.env.server"

How it works — making the changes:

In the first section of the image below I've executed the Ruby script: TTC_cds.rb. It first asks if I want to change database server names, and upon getting the answer "yes" asks for the existing (old) server name, and the new name to change it to.

Some important points — TTC_cds.rb will:

  • ONLY change the names for those servers with the one it's scanning for;
  • make a backup of those Workbooks that contains data connections to change to the new names; and
  • keep a record of the Workbooks it has scanned, and the changes it's made, in the CSV file TCC_ChangedDataSources.csv, where it is available for reviewing with Tableau.

The middle and bottom sections below correspond to the image sections above. We see that the server names have been changed according to our specifications.

A Tableau viz of the changed server names

The Magic Sauce

OK, it's just plain old Ruby code, but it works some magic.

# TCC_cds.rb - this Ruby script Copyright 2012, Christopher Gerrard require 'nokogiri' require 'open-uri' require 'fileutils' $oldServer $newServer def getInput work = false print "\n\tChange data sources? " if gets.downcase =~ /y|yes/ then print "\t Old Server Name: " $oldServer = gets.chomp if !$oldServer.nil? print "\t New Server Name: " $newServer = gets.chomp if !$newServer.nil? work = true end end end return work end def init work = getInput if work then $f ="TCC_ChangedDataSources.csv",'w') $f.puts 'Workbook,Server Changed?,Server - old,Server - new' unless $f.nil? end return work end $xmlDoc = nil def oldServer? twb $xmlDoc = Nokogiri::XML(open(twb)) sNodes = $xmlDoc.xpath("/workbook/datasources/datasource/connection[@server=\"#{$oldServer}\"]") if sNodes.empty? return false else return sNodes end end def backupTWB twb original = File.basename(twb) $origCopy = original + '.originalDS' FileUtils .cp(original, $origCopy) end def changeServer twb, serverNodes serverNodes.each do |node| node['server'] = $newServer end nsf =,'w') nsf.puts $xmlDoc nsf.close puts "\t\t changed: #{twb}" puts "\t\t original: #{$origCopy}" end def changeDataSources twb serverNodes = oldServer? twb if serverNodes backupTWB twb changeServer twb, serverNodes $f.puts "#{twb.gsub(/"/,'""')},true,#{$oldServer},#{$newServer}" else $f.puts "#{twb.gsub(/"/,'""')},false" end end if init then puts Dir.glob("*.twb") {|twb| changeDataSources twb } end


No Workbooks are harmed during the database server renaming.

Your workbooks with server name changes are safely backed up.

How to use TTC_cds.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 TTC_cds.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.
  • Running TTC_cds.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]\TTC_cds.rb"
    • Note, unlike some of my other apps,TTC_cds.rb only looks in the current directory.
  • Presto. You now have successfully changed your Workbooks' database server names, where appropriate

The usual caveats.

TTC_cds.rb works fine with the limited testing it's been through, but it's definitely not bulletproof and hardened. It's entirely possible that it won't work on your Workbooks, or somehow mangles them. So use carefully. Always back up your Workbooks, or only run TTC_cds.rb against copies. .

I hope it works for you, but make no guarantees. If you do use it and make improvements I hope that you'll post them back here as comments so I can learn from them, and hopefully other people can benefit from them too.

Saturday, December 15, 2012

Understanding tabcmd with Tableau

Clarifying the tabcmd documentation

The Tableau Public-published workbook below is an example of using Tableau to improve upon something that's not generally considered to be analyzable data: the Tableau Server tabcmd online help documentation.

Although the online doc is reasonably comprehensive it's not readily digestible. The content's presentation space is very large and sparse — it's harder than it could be to get to the particular command you need, even harder to achieve a comprehensive understanding of the scope and details of the things tabcmd can do. If this were a normal Tableau Friction post, or a web content design critique, I'd point out the flaws in the online content. But it's not, and I'm not.

Instead, this post celebrates Tableau's ability to take a bunch of web stuff and render it in a way that actively supports our ability to find what we need, when we need it, and to gain a richer information context.

All in all, this is a pretty handy way to get to the tabcmd command you want, to understand what it does, how it does it, and how to get it to work.

If you find this interesting or valuable, please let me know.

If you find a way to improve it, I would really like hearing about it.

Tuesday, December 4, 2012

Technical-, not Human-Oriented Field Calculations

Tableau's Field Calculations — Sometimes a Mystery

Tableau sometimes does things in a way that makes sense from a particular technical perspective, but leaves non-technical people bewildered, wondering what happened. If they're lucky enough to realize that what did happen wasn't in line with their perfectly reasonable expectations.

This post illustrates a situation where Tableau's Calculated Field evaluation results in values for the calculated field that are in some circumstances not what the User intended or expected.

In this scenario, there are a set of records containing Budget, Released, and Spent values for US and Canadian States and Provinces. The User is interested in determining how much of the budget is unspent, or surplus, which from his/her perspective is exactly what the common meaning of that statement is.

Arithmetically the relationship is Budget – Spent, and in the case where no money has been spent the unspent amount is the full Budget amount. As we see below, when the data is in an Excel spreadsheet and the formula {Budget} - {Spent} is evaluated the result is exactly as intended. However, the arithmetically equivalent formula in a Tableau Calculated Field will produces an erroneous, from the human perspective, result when there is no data for the relevant Spent field.

Tableau's great grace is its human-oriented design that lets the User do the thing most natural to their experience and expectation with the confidence that Tableau will do the right thing.

As this example shows, Tableau sometimes doesn't live up to this promise. But that can, and should, be remedied.