Do you know what your Calculated Fields are?

or, Is my 'Profit' the same as your 'Profit'?

It's helpful to be able to identify what fields are being calculated in Tableau. From an enterprise governance perspective it's essential.

Unfortunately Tableau doesn't provide this information except through manual inspection of the fields' calculations in the calculation dialog. This is a serious deficiency, one that hinders Tableau's adoption in the Enterprise, where senior management is reluctant to let Tableau loose "in the wild" for fear of having dueling metrics without transparency.

Fortunately, there's an easy solution. This post presents a simple Ruby script I whipped up that examines Tableau workbooks and creates a CSV file containing the relevant information about the calculated fields. This CSV file is then usable with Tableau to provide the transparency necessary to tell whether or not two metrics are in fact the same.

Example: the Tableau Sample Workbooks' Calculated Fields

As an illustration of somewhat real world calculated fields, here are those found in the Tableau v8.2 sample workbooks. They range from simple to reasonably complex.

Learn About Tableau

Introducing TTC_CalculatedFields.rb

Here's how it works in the simplest case: you copy/paste the code below into file named oh, say "TTC_CalculatedFields.rb" — it's then a bona fide Ruby script. Then from a command prompt run it in from the directory containing the TBWs you want the calculated fields from.

The usual way to run it is with

"{pathtoRuby}\Ruby" TTC_CalculatedFields.rb

It will then find all of the TWBs in the directory, and all the sub-directories, and record all of their calculated fields.

The usual caveats apply.

• You need to have Ruby and the nokogiri and open-uri gems installed for it to work. This is very simple and straightforward, and most organizations have someone who's already using Ruby to help if you need it.
• It only examines TWBs, not packaged workbooks. It's mechanically not difficult to add the functionality but it introduces complexities in handling name collisions and I wanted to keep it simple but useful.
• There are technical properties of calculated fields that are meaningful to deeper analysis but don't mean much on the surface.
• Groups are a specialized calculated field, but their internal structure is very different from the other types, making it difficult to add them to the properties/fields of the other classes. I decided not to force them so they show without formulae - this is a fairly common problem in analyzing Tableau workbooks and other hierarchical and network data structures, and Tableau isn't designed to support analysis of data like this.
• It carries no warranty or guarantees that it'll work.

On the other hand, it should be pretty easy to address any shortcomings it has. Ruby is an elegant language that makes fixing and improving things generally simple and straightforward.

---

# TTC_CalculatedFields.rb - this Ruby script Copyright 2013, 2014 Christopher Gerrard

require 'nokogiri'
require 'open-uri'

$recNum = 0

$CSVHeader = <<CSVHEADER.gsub("\n",'')
Record Number,
Workbook,
Workbook Dir,
Data Source Name,
Data Source Caption,
Data Source Name (tech),
Name,
Caption,
Name (tech),
Data Type,
Role,
Type,
Class,
Scope Isolation,
Formula Code,
Formula,
Formula Comments
CSVHEADER

def init
    $f = File.open("TTC_CalculatedFields.csv",'w')
    $f.puts $CSVHeader unless $f.nil?
end

def parseCalculatedFields twbWithDir
  twb     = File.basename(twbWithDir)
  twbDir  = File.dirname(File.expand_path(twbWithDir))
  puts "Found '#{twb}' in #{twbDir}"
  doc = Nokogiri::XML(open(twb))
  calculationNodes = doc.xpath("//workbook/datasources/datasource/column/calculation").to_a
  calculationNodes.each do |d|

     #-- Data Source --
     dsTechName      = d.xpath('../../@name').text
     dsCaption       = d.xpath('../../@caption').text
     dsName          = if dsCaption == '' then dsTechName else dsCaption end

     #-- Field       --
     caption,        = d.xpath('../@caption').text
     techName        = d.xpath('../@name').text
     name            = if caption == '' then getName(techName) else caption end
     dataType        = d.xpath('../@datatype').text
     role            = d.xpath('../@role').text
     type            = d.xpath('../@type').text

     #-- Formula     --
     formulaCode     = d.xpath('./@formula').text.gsub(/\r\n/, ' ')
     formulaLines    = d.xpath('./@formula').text.split(/\r\n/)
     formula         = getFormula(  formulaLines )
     comments        = getComments( formulaLines )
     fClass          = d.xpath('./@class').text
     scopeIsolation  = d.xpath('./@scope-isolation').text

     #-- emit CSV
     putCSV(twb,
            twbDir,
            dsName,
            dsCaption,
            dsTechName,
            name,
            caption,
            techName,
            dataType,
            role,
            type,
            fClass,
            scopeIsolation,
            formulaCode,
            formula,
            comments
           )
  end
end

def getName techName
    techName.gsub(/^\[/,'').gsub(/\]$/,'')
end

def getFormula lines
  formula = ''
  lines.each do |line|
      line.strip
      formula += ' ' + line unless line =~ /[ ]*\/\//
  end
  return formula.strip
end

def getComments lines
  comments = ''
  lines.each do |line|
      line.strip
      comments += ' ' + line.strip if line.start_with?('//')
  end
  return comments.strip
end

def putCSV(twb,
           twbDir,
           dsName,
           dsCaption,
           dsTechName,
           name,
           caption,
           techName,
           dataType,
           role,
           type,
           fClass,
           scopeIsolation,
           formulaCode,
           formula,
           comments
          )
  $recNum += 1
  csvLine = <<CSV.gsub("\n",'')
"#{$recNum}",
"#{twb.gsub('"','""')}",
"#{twbDir.gsub('"','""')}",
"#{dsName.gsub('"','""')}",
"#{dsCaption.gsub('"','""')}",
"#{dsTechName.gsub('"','""')}",
"#{name.gsub('"','""')}",
"#{caption.gsub('"','""')}",
"#{techName.gsub('"','""')}",
"#{dataType.gsub('"','""')}",
"#{role.gsub('"','""')}",
"#{type.gsub('"','""')}",
"#{fClass.gsub('"','""')}",
"#{scopeIsolation.gsub('"','""')}",
"#{formulaCode.gsub('"','""')}",
"#{formula.gsub('"','""')}",
"#{comments.gsub('"','""')}"
CSV

  $f.puts csvLine unless $f.nil?

end

init

Dir.glob("**/*.twb") {|twb| parseCalculatedFields twb }

$f.close unless $f.nil?

---

An interesting exercise would be to round up the various workbooks that have been published about Table Calculations to see what that population looks like. Maybe something to do during the conference this week.

---

I hope you find this little utility useful. If so, please leave a comment. If it doesn't work, please leave a comment — I may or may not be able to help (time, other demands upon, etc.). If you extend it in some useful way, please leave a comment.

Or just leave a comment.