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 = File.open("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 = File.open(twb,'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.
