Monday, September 10, 2018

Documenting Dashboards and their Worksheets

Objective

Document the Dashboards, and the Worksheets they contain, in one or more Workbooks, making it possible to use Tableau to see things like this:

Tableau Tools make it simple and easy to accomplish this. All of your Workbook's Dashboards can be documented so that it's easy to see and understand where they are and the Worksheets they contain.

Tableau Tool – Recipe

  1. Prepare the ingredients – make sure that:
    1. Ruby is installed
    2. The Twb gem is installed
    3. The Ruby script analyzeDashboardSheets.rb is available
  2. Open a console / command / terminal session
    > _  
  3. Navigate to the directory or folder containing the Workbooks you want to document
    > cd {path to Workbooks}  
  4. Run analyzeDashboardSheets.rb
    > ruby '{path to Workbooks}\analyzeDashboardSheets.rb'  
  5. Examine the CSV file containing the information, normally
    > ./ttdoc/TwbDashboardSheets.csv  
    —preferably with Tableau

Why This Tool

Problem Addressed

One of the challenges faced with Tableau once it's been in use for a while is in understanding where things are and how they're related.

Information Requirements

Individual and organizational interests have a common set of information requirements that can be framed as questions about how Tableau's being used. Being able to get answers to these questions is essential to being able to understand what's been created with Tableau. The questions include:
  • Where's that Dashboard, in which Workbook or Workbooks?
  • How many versions are there of it?
  • What Worksheets does it contain?
  • Are the Worksheets the same in the different versions?
  • Which Dashboards does this Worksheet appear in?
Individuals and an Organization's Managers have concerns and responsibilities regarding the effective use of Tableau, covering the spectrum from individuals' dynamic data analysis to construction, delivery, and consumption of informative analytics via Dashboards. (recognizing that Dashboards aren't the only information delivery mechanism, this post is specific to them)
A normal working Tableau person can be involved with many, many Workbooks. It's not unusual for there to be hundreds, even into the thousands of Workbooks to be present in an Organization where Tableau's been in use for any length of time.
Individual Tableau User
Broadly speaking, there are two main uses of Tableau. Some people use it primarily for their own data analysis, some use it to create analytics for others to consume, with many or most people with some mixture of the two. As an individual, once you've created a number of Workbooks it can become difficult to remember which Workbook a Dashboard is in, or Workbooks if there are versions and copies of it.
Organization
Organizations have multiple people with interests that are well served by being able to identify the content of Tableau Workbooks, including those responsible for:
  • Intellectual Property Guardianship
  • Data & Analytics Governance
  • Analytics Management

Tableau Doesn't Help (Much)

Tableau provides very little assistance in helping answer these and related questions. If the Workbooks are published to Tableau Server or Tableau Online there are some views that provide partial information, but it's a very limited perspective lacking real analytical flexibility.

Solution: Documenting the Dashboards and Worksheets

This Tableau Tool will examine a collection of Workbooks and record the information about their Dashboards, and the Dashboards' Worksheets. By default the Workbooks will include all the normal and packaged Workbooks in the current directory/folder—this can be easily configured to include as many or as few Workbooks as desired.
The information is captured in a CSV file, making it analytically useful with Tableau (or other data analysis tool).

analyzeDashboardSheets.rb

is a Ruby script that accesses Workbooks, locates the Dashboards, their Worksheets, and records the information in a CSV file. It can be run as-is, and is available as a Gist from GitHub here.

analyzeDashboardSheets.rb – How to Use

Prerequisites

Ruby is installed

Ruby is available for all of the platforms that Tableau runs on. It's available by default on Macs, and is easily installed on Windows.

The twb gem is installed

normally via:
  > gem install twb

analyzeDashboardSheets.rb is available

at {path}\analyzeDashboardSheets.rb

The Workbooks to document are available

Normally and most conveniently collected into a single directory, they can be located in multiple directories identified with a set of file naming patterns details below
The directory contains the Workbooks to analyze — we're using the Tableau Sample Workbooks here

  > cd '{directory/folder containing the Workbooks}'
  > ...
  > dir *.t*
   Volume in drive C is Windows7_OS
   Volume Serial Number is F861-CE43

   Directory of {...}\Tableau Sample Workbooks

  09/06/2018  04:28 PM           627,531 Regional.twb
  09/05/2018  09:34 PM           605,080 Regional.twbx
  09/05/2018  09:34 PM         1,091,332 Superstore.twbx
  09/05/2018  09:34 PM           533,181 World Indicators.twbx
                 4 File(s)      2,857,124 bytes
                 0 Dir(s)             ... bytes free
  
Note: Regional.twb is a copy of the sample Regional.twbx Workbook that's been edited and saved as a normal .twb file.

run 'analyzeDashboardSheets.rb'

most commonly, from the terminal command line like so:

 > ...
 > ruby '{path}\analyzeDashboardSheets.rb'
 
As it runs analyzeDashboardSheets.rb provides information about its operation:

  
  Analyze Dashboard Sheets from Tableau Workbooks.
  
  Processing Workbooks matching: '["*.twb", "*.twbx"]'
  
       - Regional.twb
       - Regional.twbx
       - Superstore.twbx
       - World Indicators.twbx
  
  Analysis complete, found: 4 Workbooks
  
  For documentation and generated data see the following:
  - ./ttdoc/TwbDashboardSheets.csv    Workbooks, Dashboards, and their Worksheets
  
  
  That's all, folks.
  

The CSV file TwbDashboardSheets.csv can now be used to identify and analyze the Dashboards and Worksheets.

TwbDashboardWorksheets.twb

connects to the CSV file and has a number of starter Worksheets that provide basic information, including those shown above. It can be downloaded from GitHub as 'TwbDashboardWorksheets.twb.zip' from here.
Workbook notes:
  • Zipped: the Workbook is zipped simply have GitHub provide a download link to it instead of presenting it as an XML file. Unzip it to use it.
  • CSV file: The Workbook is configured to pick up the CSV file from the current directory.
    This is normally the ./ttdoc subdirectory of the directory from which analyzeDashboardSheets.rb was run.
    If the workbook is opened from this directory it will automatically find the CSV file, otherwise you'll need to point Tableau to it.
    When the Workbook is saved it will remember this location, so if you want to use the Workbook with another CSV file you will need to edit the data connection to identify the new file's location.

Configuration:

By default, analyzeDashboardSheets.rb analyzes all of the normal and packaged Workbooks in the current directory/folder.
It can be told to analyze any Workbooks of interest by providing their names on the command line, either as specific names or as one or more patterns that analyzeDashboardSheets.rb will use to locate matching Workbooks.

Identifying Workbooks by Names & Patterns

The analysis is conducted on Workbooks identified by name, which can be literal names, e.g. 'Regional.twbx'; or by patterns, e.g. '*.twb', which may contain replaceable parts of file names—wild card characters.
The default pattern has two parts: .twb,.twbx . This is equivalent to
ruby '{path}\analyzeDashboardSheets.rb' '.twb,.twbx' and is interpreted as two individual patterns:
  • *.twb     identifies files ending in '.twb' – normal Workbooks
  • *.twbx   identifies files ending in '.twbx' – packaged Workbooks
note: there's a comma separating the individual patterns, with no spces between them.
Other patterns can be provided to identify any Workbooks of interest. Some examples:
  • '*.twb'   – only the normal Workbooks in the current directory
  • '/*.twb,/*.twbx'   – all the Workbooks in the current and all subdirectories
  • '{path}/*.twb'   – all the normal Workbooks in the directories named by {path}; where {path} can be relative or absolute
note: it's not necessary to put the patterns in quotes; they'll be handled either way, but it may make things clearer in some circumstances.

Data notes:

The CSV file contains this header record:
Workbook,Workbook Dir,Modified,Dashboard,Worksheet,Hidden,Visible
identifying these fields:
Field Description
Workbook the Workbook's name
Workbook Dir the directory/folder the Workbook is in – useful when processing multiple directories that may contain Workbooks with the same name
Modified the Workbook's modification date
Dashboard the Dashboard's name
Worksheet the name of a Worksheet contained in the Dashboard
Hidden whether or not the Worksheet is hidden
Visible whether or not the Worksheet's visible (opposite of 'Hidden'

Getting Started

It's simpler and easier to get started than you may think. If you're using Windows getting Ruby installed is straightforward, Google "Ruby on Windows". Ruby is included on standard Mac installations, and Google is again your friend.
Installing the twb gem, grabbing and running analyzeDashboardSheets.rb takes only a few minutes.
Download the starter Workbook into the 'ttdoc' subdirectory, open it up and start exploring your Dashboards and their Worksheets.

No comments:

Post a Comment