It's pretty handy to be able to be able to see and analyze the Calculated Fields in your Workbooks without the tedious bother of manually inspecting them by opening up the Workbooks and looking at them one at a time. Even writing that sentence was boring.
Imagine how much better it would be to be able to use Tableau to see the Fields, all brought together so that they can be scanned, filtered, sorted, and otherwise freely investigated.
Here's a screen shot of a Tableau Worksheet showing the Calculated Fields from Tableau's Sample Workbooks.
Note: the data window and controls – shelves, filters, etc. have been hidden for clarity.
Tableau Tools makes it simple and easy to analyze the Calculated Fields in your Workbooks.
This post describes the basics of analyzing the Workbooks and accessing the generated CSV data files.
First Step — Parsing the Workbooks
analyzeCalculatedFields.rb
is a Ruby script that accesses Workbooks, locates the Calculated Fields, and records the information in CSV files.
It's self-contained, can be run as-is, and is
available from GitHub here.
Usage
Prerequisites:
- Ruby is installed
- The twb gem is installed - normally via
> gem install twb
- analyzeCalculatedFields.rb is available at {path}
- The directory contains the Workbooks to analyze — we're using the Tableau Sample Workbooks here
> dir *.t*
Volume in drive...
Volume Serial Number is...
Directory of {path}\Tableau Sample Workbooks
06/27/2018 10:32 PM 605,080 Regional.twbx
06/27/2018 10:32 PM 1,091,332 Superstore.twbx
06/27/2018 10:32 PM 533,181 World Indicators.twbx
3 File(s) 2,229,593 bytes
run analyzeCalculatedFields.rb
most commonly, from the terminal command line like so:
> ruby '{path}\analyzeCalculatedFields.rb'
As it runs analyzeCalculatedFields.rb provides information about its operation:
Twb::Analysis::CalculatedFieldsAnalyzer
Analyze Calculated Fields from Tableau Workbooks.
Processing Workbooks matching: '["*.twb", "*.twbx"]'
- Regional.twbx
- Superstore.twbx
- World Indicators.twbx
Analysis complete, identified
# of Workbooks : 3
# of Calculated Fields : 43
# of Referenced Fields : 84
For documentation and generated data see the following:
- ./ttdoc/TwbCalculatedFieldFormulaLines.csv Calculated fields and their formulas' individual lines.
- ./ttdoc/TwbCalculatedFields.csv Calculated fields and their formulas.
- ./ttdoc/TwbCalculatedFieldsReferenced.csv Calculated fields and the fields their formulas reference.
That's all, folks.
The CSV files can now be used to identify the Calculated Fields, their Formulas, the fields they reference, the Workbooks and Data Sources they are from.
The provided Tableau Workbook: 'Calculated Fields - Base Data.twbx' connects to each of the CSV files and has a
starter Worksheet for each,
downloadable from here.
Another view of the Worksheet shown above is here:
Note: the data controls have been restored to show the more familiar Tableau user interface.
Data notes:
- As shown, formulas are available in two forms:
- as single elements, with all lines combined, and
- in their original lines as coded.
- "Formula Line #" is used to order the lines into their correct order, it can be hidden for clarity.
- The TwbCalculatedFieldFormulaLines data source is more generally useful than TwbCalculatedFields, although TwbCalculatedFields contains technical information about the calculated fields that is useful for advanced technical analysis.
Workbook notes:
- The Workbook is configured to pick up the CSV files from the current directory; this will be hard wired to whichever directory the Data Sources are using when the Workbook is saved.
- The Workbook is packaged simply to avoid GitHub from presenting it as XML text by default – this can be confusing to people who aren't aware of or used to seeing Workbooks as XML.
Recommendations
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 analyzeCalculatedFields.rb takes only a few minutes.
Download the starter Workbook into the 'ttdoc' subdirectory, open it up and start exploring your Calculated Fields.
Coming soon.
Future posts will expand upon using Tableau to explore the CSV data prepared so far, showing how to accomplish things such as how to trace where specific fields are used.
We'll also go over the other documentation produced, including maps of the Calculated Fields' relationships to one another and to the Data Sources' database fields.
Further on, other tools will be introduced, expanding the scope of how Tableau things can be related to one another, e.g. identifying which Dashboards contain data from specific databases, which makes it possible to assess the impact of database changes—something that Tableau doesn't natively support.
