Friday, June 5, 2015

This changes everything - Autodocumenting Workbooks

Your Workbooks can document themselves, now that programmatically creating and injecting Dashboards that document Workbooks into them is a reality. And it's free.

Consider the Regional Sample Workbook. It has six separate vizzes, but one easily can't tell whether any of them is a Dashboard without visually inspecting it. Nor can we see which Worksheets a Dashboard includes without opening it, and there's no way to tell how all the Dashboards relate to all the Worksheets, or the Worksheets to the Data Sources they access.

Imagine, if you please, what it would look like if there was a way to have the Workbook -> Dashboards -> Worksheets -> Data Sources relationships teased out of the Workbook, rendered graphically, and then added back into the Workbook as a self-documenting Dashboard.

Would you like that? Would it be handy? Useful? Maybe? Until now, this sort of thing has been difficult, awkward, and manually intensive. If it was doable at all.

Not any longer. It's now simple and straightforward, with a minimum of fuss, for one workbook, or a whole boatload of them.

Here it is — the Regional Workbook, autodocumented,
with the map of its Dashboards, Worksheets, and Data Sources automatically generated and injected into it.

Adding documentation to Workbooks, where it really matters, has always been a manual, laborious process, with so much friction that it hasn't been feasible at scale. No more. Tableau Tools' now has the ability to inject dashboards into existing dashboards automatically. Coupled with its existing abilities to generate useful content about Workbooks, this opens entire new horizons for enriching Workbooks with surprisingly little effort.

How the magic happens.

The simple version: run a simple Ruby script in a directory containing Workbooks to be documented, and presto! the Workbook(s) are documented with the desired content; in this case the D->W->DS maps for each.

Run this code in a directory containing Workbooks.
# Copyright (C) 2014, 2015 Chris Gerrard # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program. If not, see . require 'twb' puts "\n\n\t Documenting Workbooks with Tableau Tools" puts "\n\t Adding Dashboard -> Worksheet -> Data Source graphs" puts "\n\t https://github.com/ChrisGerrard/Tableau-Tools" puts "\n" path = if ARGV.empty? then '*.twb' else ARGV[0] end puts "\n\t Files matching: '#{path}'" Dir.glob(path) do |twb| puts "\t -- #{twb}" twb = Twb::Workbook.new(twb) dotBuilder = Twb::Util::TwbDashSheetDataDotBuilder.new(twb) dotFile = dotBuilder.dotFileName renderer = Twb::Util::DotFileRenderer.new imageFile = renderer.render(dotFile,'png') dash = Twb::DocDashboardImageVert.new dash.image=(imageFile) dash.title=('Dashboards, Worksheets, and Data Sources') twb.addDocDashboard(dash) twb.writeAppend('dot') end

The code is available on GitHub here.
Not much to it, is there?
Tableau Tools does the work, needing only a little glue code to compose the specific functionality.

Here's the code running in a directory containing Tableau Sample Workbooks.

As the code runs it picks up all the *.twb files in the directory and processes each in turn.


  Dir.glob(path) do |twb|
  puts "\t -- #{twb}"
  twb        = Twb::Workbook.new(twb)

Each file has its D->W->DS map built with:


  dotBuilder = Twb::Util::TwbDashSheetDataDotBuilder.new(twb)
  dotFile    = dotBuilder.dotFileName
  renderer   = Twb::Util::DotFileRenderer.new

The D->W->DS map image file is added to a Dashboard which is then injected into the Workbook with:


  dash       = Twb::DocDashboardImageVert.new
  dash.image=(imageFile)
  dash.title=('Dashboards, Worksheets, and Data Sources')
  twb.addDocDashboard(dash)

The Workbook is written out, as a copy with '.dot' added to its name with:


  twb.writeAppend('dot')

There's no technical reason to create a new copy of the Workbook, and in a production environment the Workbooks are mostly documented in place, i.e. keeping their own names.

Dependencies

Ruby 1.9.3
is preferred, almost exclusively because the Nokogiri gem that's required under the covers works seamlessly with 1.9.3 (on Windows), and not so seamlessly with Ruby 2.x. See the Nokogiri section below for more information.


The Twb gem
is required, and is declared as such via "require 'twb'", the first executable (non-comment) line.
The gem is installable via "...> gem install twb", which assumes that "gem " is installed, which should have happened when Ruby was installed.


 ... Tableau Sample Workbooks> gem install twb
 Successfully installed twb-0.3.2
 1 gem installed
 Installing ri documentation for twb-0.3.2...
 Installing RDoc documentation for twb-0.3.2...

 ... Tableau Sample Workbooks> gem list twb

 *** LOCAL GEMS ***

 twb (0.3.2)

 ... Tableau Sample Workbooks> 

(post-publishing) note:
The correct gem version is 0.3.2 (as of this writing) – there was a glitch in the gem publishing that I didn't catch at the time of the original post resulting in an obsolete version of it being installed.
Thanks to Philip, Vishwanath, and Matthew for reporting this.


The Nokogiri gem
Nokogiri is an XML and HTML parsing gem that's used by the Twb gem. If it's not installed the 'require' statement in Twb will fail.
Nokogiri is installable thus:


 ... Tableau Sample Workbooks> gem install nokogiri
 Fetching: mini_portile-0.6.2.gem (100%)
 Fetching: nokogiri-1.6.6.2-x86-mingw32.gem (100%)
 Nokogiri is built with the packaged libraries: libxml2-2.9.2, libxslt-1.1.28, zlib-1.2.8, libiconv-1.14.
 Successfully installed mini_portile-0.6.2
 Successfully installed nokogiri-1.6.6.2-x86-mingw32
 2 gems installed
 Installing ri documentation for mini_portile-0.6.2...
 Installing ri documentation for nokogiri-1.6.6.2-x86-mingw32...
 Installing RDoc documentation for mini_portile-0.6.2...
 Installing RDoc documentation for nokogiri-1.6.6.2-x86-mingw32...

 ... Tableau Sample Workbooks> 

note:
Nokogiri is sensitive to the Ruby version being used.
I'm using Ruby 1.9.3, mostly because: it's stable; suitable for my purposes; inertia; and Nokogiri didn't work with 2.x Ruby in the limited testing I did.
There's some documentation online about Nokogiri and Ruby 2.x but I've not had the time to puzzle out how to get them to play nice together.


Graphviz
The diagrams are created using Graphviz - open source graphing software, which can be downloaded and installed from here.

Graphviz location In order for the graphs to be rendered, Graphviz needs to be available for use by the Twb Twb::Util::DotFileRenderer object, which assumes the default Graphviz installation: 'C:\tech\graphviz\Graphviz2.38\bin\dot.exe'.
'dot.exe' is the Graphviz program that renders this particular graph type—there are many other types, each with their specific rendering program.

If Graphviz is installed into another directory, it can be communicated to the Twb rendered by adding the following line to the code (new line bold):


  renderer   = Twb::Util::DotFileRenderer.new
  renderer.gvDotLocation=('dot.exe location') 


To infinity, and beyond.

With the Workbook injecting nut cracked, there's no end to the things that can be done. Whatever can be thought up can be created and added to your Workbooks.

But wait, there's more.

Workbook autodocumenting relies upon Tableau Tools' ability to modify and write Workbooks. This core feature makes it possible to compose functionality to accomplish pretty much anything you could want to do with and to your Workbooks. The door is open, the future beckons, and it going to be a fun ride.

About Tableau Tools

Tableau Tools is open source software for interrogating and manipulating Tableau Workbooks and other artifacts. It has two main parts as of this writing:

  • The TWB Ruby gem, available on Github at: https://github.com/ChrisGerrard/TWB, is the core element. It models Workbooks and their major components, allowing for easy access to and control of them. Based on Nokogiri, it provides the opportunity to employ Nokogiri when access and manipulation of deeper components for specific uses is needed.
  • The Tableau Tools project, also available on GitHub: https://github.com/ChrisGerrard/Tableau-Tools contains tools for Tableau assessment and manipulation, including all manner of useful tools that can be composed into richly functional suites with minimal fuss and bother.

12 comments:

  1. Chris- this looks great, but when I ran the code I got this error:
    mymac:tabtoolstest mymac$ ruby TwbDoc-DashSheetDataGraph.rb


    Documenting Workbooks with Tableau Tools

    Adding Dashboard -> Worksheet -> Data Source graphs

    https://github.com/ChrisGerrard/Tableau-Tools


    Files matching: '*.twb'
    -- myWorkbook.twb
    TwbDoc-DashSheetDataGraph.rb:29:in `block in ': uninitialized constant Twb::Util (NameError)
    from TwbDoc-DashSheetDataGraph.rb:26:in `glob'
    from TwbDoc-DashSheetDataGraph.rb:26:in `'

    I am completely new to ruby but would really like to do this for my workbooks. I installed the twb gem using 'gem install twb' and then ran the script in a directory containing 1 workbook.
    Thanks!

    ReplyDelete
    Replies
    1. Hi Philip, the problem you encountered is that the published Twb gem was out of sync with my local code. I've republished the gem and it should be OK now. You'll need to re-install it with 'gem install twb' after which everything should work.

      I've also updated the post to call out the dependencies, particularly on GraphViz, which need to be in installed, and the location of its 'dot.exe' known to the code, by default it's: 'C:\tech\graphviz\Graphviz2.38\bin\dot.exe'. If it's installed there all should be well, otherwise please let me know.

      Delete

    2. Hi Chris

      Thanks for the wonderful work , I am newbie to ruby , I have installed Ruby, RubyGem and Twb Gem , I am using windows machine. I am getting below error.

      C:\Users\pendyv\Downloads\Tableau Sample>gem install twb
      Successfully installed twb-0.1.1
      Parsing documentation for twb-0.1.1
      Done installing documentation for twb after 0 seconds
      1 gem installed

      C:\Users\pendyv\Downloads\Tableau Sample>cd C:\Users\pendyv\Downloads\Tableau Sa
      mple

      C:\Users\pendyv\Downloads\Tableau Sample>ruby TwbDoc-DashSheetDataGraph.rb
      C:/Ruby22/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `re
      quire': cannot load such file -- nokogiri (LoadError)
      from C:/Ruby22/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require
      .rb:54:in `require'
      from C:/Ruby22/lib/ruby/gems/2.2.0/gems/twb-0.1.1/lib/twb/dashboard.rb:1
      6:in `'
      from C:/Ruby22/lib/ruby/gems/2.2.0/gems/twb-0.1.1/lib/twb.rb:16:in `requ
      ire_relative'
      from C:/Ruby22/lib/ruby/gems/2.2.0/gems/twb-0.1.1/lib/twb.rb:16:in `'
      from C:/Ruby22/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require
      .rb:128:in `require'
      from C:/Ruby22/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require
      .rb:128:in `rescue in require'
      from C:/Ruby22/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require
      .rb:39:in `require'
      from TwbDoc-DashSheetDataGraph.rb:16:in `'

      Thanks for your help. If you have any documentation for a newbie like me ,It would helpful

      Thanks
      Vishwa

      Delete
  2. Hi Chris

    I was able to overcome my previous error by installing Ruby 2.1 and I have downloaded and installed twb gem today , But still I get the error
    C:\Users\pendyv\Downloads\TableauSample>ruby TwbDoc-DashSheetDataGraph.rb


    Documenting Workbooks with Tableau Tools

    Adding Dashboard -> Worksheet -> Data Source graphs

    https://github.com/ChrisGerrard/Tableau-Tools


    Files matching: '*.twb'
    -- Q3 Tableau.twb
    TwbDoc-DashSheetDataGraph.rb:28:in `block in ': uninitialized constant Twb
    ::Util (NameError)
    from TwbDoc-DashSheetDataGraph.rb:25:in `glob'
    from TwbDoc-DashSheetDataGraph.rb:25:in `'

    C:\Users\pendyv\Downloads\TableauSample>

    Graphviz was installed as the same you mentioned.

    Thanks
    Vishwa

    ReplyDelete
  3. Hey Chris,
    I'm getting the same error after 'gem install twb'. 'gem list' is showing twb (0.1.1) Is this the correct version?

    ReplyDelete
  4. Hi All, Thanks for giving it a go. I'll check into the errors this evening.

    ReplyDelete
  5. I've made the adjustments to correct the issues reported in the comments, will appreciate any observations, thoughts, opinions, or problems encountered.

    Thanks for trying it out.

    ReplyDelete
  6. Chris,
    I just had a couple free hours to try out the changes. I kept getting an undefined local variable error for 'rendered' in the added line : rendered.gvDotLocation=('dot.exe location')

    Changing rendered to renderer fixed everything!! I've ran the script on a few different workbooks and its working great!! Thanks for building this!

    ReplyDelete
    Replies
    1. Good spot, Matthew. Thanks for pointing it out - I've fixed the post.

      I'm happy you find it useful, and am interested in any ideas about what else would be good to implement. There's almost nothing that can't be done; I have some dynamic HTML/JavaScript/SVG stuff that works great but doesn't transport well because Tableau doesn't include local HTML files in packaged workbooks like it does other supporting files. But at least they're now supporting SVG in HTML files.

      Delete
  7. Chris,
    First of all thank you! This is amazing. I was able to get it working with no prior ruby knowledge. As a follow up, can you show how to get a lot of this same information into a csv?

    ReplyDelete
    Replies
    1. As I see it there are two basic levels to answering your question, Stephen:
      - how do I get -any- information into CSV?
      - how to I get information about the relationships between Workbooks, Data Sources, and Worksheets into CSV?

      Fortunately, there's a published Tableau Tool that covers both, available at: https://github.com/ChrisGerrard/Tableau-Tools/blob/master/Ruby/ListWorksheetsForDataSource.rb

      Like all the Tableau Tools it should be easy to extend for your purposes.
      Observing the niceties and protocols of the Tableau Tools open source license, of course, and hopefully sharing it back to the community.

      Delete
  8. Chris,
    Thank you for this! I was able to do it without any prior exp with Ruby. As a follow up, can you show how you could pull a lot of this information into a csv file?

    ReplyDelete