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 = 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.

6 comments:

  1. Excellent! Love finding others hacking Tableau like this out there.

    ReplyDelete
  2. Just a note to mention that Tableau Server has built in capabilities to make bulk changes to connections and credentials for published workbooks. In the administration section, choose the 'Data Connections' link.

    ReplyDelete
    Replies
    1. True. Which brings up the question: if Tableau Server can manage this, why can't Tableau Desktop?
      Many, many, people who use Tableau do so in the absence of Tableau Server, and for them even the basic ability to discover what data sources are being used where in the their workbooks is impossible within Tableau Desktop, except for the manual inspection of individual connections and recording of their details, which is a best a boring, menial, mechanical, time consuming, and error-prone task.

      Or they can use one of the extra-Tableau solutions like TWIS, the Tableau Auditor, or the handy Ruby script I'm planning to publish here very soon.

      Delete
  3. This is a really great hack - thanks Chris!

    Along kind of the same lines - have you ever tried programmatically refreshing a data extract and packaging a workbook?

    ReplyDelete
  4. Hi,

    Can the ruby code be modified to also change the database name?

    ReplyDelete
    Replies
    1. It would be relatively simple to change the code to change the database name, in at least the sense that it's reasonably simple to make the change in the XML - the code in this post is useful as a template for doing it.

      The tricky part is in knowing everything that needs to be changed, how it needs to be changed, and in avoiding changing things that shouldn't be altered. This information is situation-specific to pretty much everything in the workbook, and ranges from very easy to fiendishly difficult to puzzle out.

      I understand that this isn't a "here's how" to do it, leaving the implementation as an exercise for the curious.
      Or, if I get the time I could built it as a Tableau Tool.

      On the other hand, if it's something you need right away my firm offers custom Tableau tools development; email me @ Chris@BetterBI.biz

      Delete