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

Powered by Tableau

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.