BI Tool Meta Browser for Tableau

Introduction

I created a tool for Tableau written in C# that reduces the time required to complete a report. It takes the stress out of remembering what calculated fields are used and where across your workbook by giving you an easy to browse Tableau report that can be refreshed anytime you save your work. Find it here! https://github.com/mcdean/BiToolMetaBrowser

There is a .zip of the required runtime files under the release link on the repo.

Background

Late one Friday afternoon, I just had typed the 60^th calculated field inside what was my first real Tableau report and I threw up my hands out of frustration. The long list of hierarchically related fields had clearly surpassed the size of what I was capable of reasoning about. The fields' dependency-tree was multiple levels deep and much of the fields were scattered across the nearly 20 to 30 sheets and dashboards inside of the report. Progressing any further in designing this report was a non-starter. It had become a treacherous memory game that I could not win because of how much Tableau hides the code that you write behind its visual design surface. At that moment, I decided that Tableau needed to have a better IDE-like user-experience which it surely lacked, even if I had to create it myself. That weekend, I set out to create a tool that would fill this user experience gap that we developers have come to expect from traditional IDE's like Visual Studio which would help me clearly understand the dependencies between calculated fields and where they were being used across my report.

The Problems to Solve

For example, a Total Cost calculated field is show below. This can be found in a workbook called Calculated Fields-Table Calculations-Statistics.twbx hosted on the following website: http://www.tableaubook.com/v8/workbooks.asp An alternate version of this workbook with a number of dashboards added is used throughout this wiki. The combination of sheets added to the dashboards is not particularly important, but serves as a decent example for the BMB tool.

The image below illustrates the issues that I had with the Tableau interface.

Notice that there are nine dependencies and they are not even all listed in the tool-tip pop-up. It says "... and 1 more", instead. Also, the ones shown are not actually click-able. Nor, can the user navigate to the definition of the Quantity or Unit Cost calculated fields which are used in the definition of the Total Cost field from within this window.

The full list of calculated fields can be found, of course, on the left-side pane of the user interface. Nonetheless, it is tedious and painful to click through each one to understand the full picture of calculated field usage and their definitions. Furthermore, Tableau only allows one field definition to be opened at a time.

Here is an additional view of the workbook. Many sheets are set to hidden, and one of the dashboards is showing–hiding all sheets is a common setting to use. In this case, Tableau does not show the calculated field list at all.

Using the BI Tool Meta Browser (BMB)

The BMB has two parts:

1. Meta Browser Extractor (BiToolMetaBrowser.exe)
2. Meta Browser Workbook (BiToolMetaBrowser.twb)

The extractor reads your Tableau workbook and creates a set of CSV files that contain calculated field information. The Meta Browser Workbook uses those CSV files as source data for displaying the field information in its dashboards.

How to Run

The extractor executable can be run from a batch file or command line. Here is an example batch file:

The first argument is the path to your Tableau workbook. The second argument is the location of the Meta Browser Workbook or wherever you want to place the CSV files. You have to edit the source location for the CSV files in the browser workbook, of course.

The extractor will pop-up a console window and indicate which file is it extracting and how many times it has extracted. The extractor will listen for when you save the Tableau workbook and perform an extraction each time.

BMB Dashboards

Your Tableau workbook's calculated fields usage and dependencies are displayed across the following set of dashboards inside the Meta Browser Workbook.

WsUsageDetailForDash

This first dashboard called Dashboards shows a summary of dashboards and worksheets and how many calculated fields are used. The Dashboard and Worksheet can be clicked to display which field definitions they use on the right under WsUsageDetailForDash.

The next dashboard called Worksheets is similar, but does not include the Dashboard category.

Calc References

The Calc References dashboard is shown below. It shows what fields call the selected field in the CalcFieldSummary in the CalcFieldCallers list on the top right. It also shows which worksheets contain the selected field in CalcFieldWsUsers and the corresponding containing dashboards in CalcFieldDashUsers. Finally, the selected field's definition is displayed in the bottom right under CalcFieldDetail.

Calc Calls

The Calc Calls dashboard shown below displays the definitions of the fields in CalcFieldUsageByCaller on the top right that are used in the selected field in the CallerSummary on the left. The selected field's definition is also shown in CallerFieldDetail on the bottom right.

Special Note about Differences between Tableau and BMB

Tableau will (try to) show in the tool-tip transitive (or downstream) dependencies. Whereas, BMB only lists direct dependencies, but could be upgraded in the future to include a full list of transitive dependencies. Currently, you can just click through each direct dependency to see them. For example, if looking at Total Cost in the Calc References dashboards, it has a dependency on Retail Total and Profit. If you click on Retail Total, then the following view will show Discounted Retail as a caller field:

Tableau will show Discounted Retail in the dependencies list in the tool-tip to start with.

Implementation

The implementation of the Tableau file extractor spans two files: Program.cs and WorkbookReader.cs.

Program.cs

The Program.cs code includes a standard file watching technique using the FileSystemWatcher except that it also handles a peculiar behavior of Tableau where it saves three times in rapid succession each time the user presses the save button on the user interface. This behavior was observed in Tableau version 2018.1.8 (20181.18.1213.2251). The lock object Locker is used to synchronize access to the variable isExporting for the threads kicked off by the second and third file save actions by Tableau. A wait time of 500 ms is applied to the execution of the first save action thread in order to stretch past the time of the second and third thread occurrences. This works because the user won't be expected to save, edit, then save again in such a short amount of time.

This class is also responsible for writing the log messages to standard output, besides coordinating the extraction and export of the Tableau file.

internal class Program {
        private static int exportCount;
        private static string outputPath = string.Empty;
        private static string tableauFile = string.Empty;
        private static bool isExporting;
        private static readonly object Locker = new object();

        private static void Main(string[] args) {

            tableauFile = args[0];
            outputPath = args[1];

            Console.WriteLine($"Watching tableau file: {tableauFile}");
            Console.WriteLine($"Writing export to path:{outputPath}");

            // Create a new FileSystemWatcher and set its properties.
            var watcher = new FileSystemWatcher {
                Path = Path.GetDirectoryName(tableauFile),
                NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
                                                        | NotifyFilters.FileName 
                                                        | NotifyFilters.DirectoryName,
                Filter = Path.GetFileName(tableauFile)
            };

            watcher.Changed += OnChanged;
            watcher.EnableRaisingEvents = true;

            //export on startup
            Export();
            Console.ReadLine();
        }

        private static bool GetIsExporting() {
            lock (Locker) {
                return isExporting;
            }
        }

        private static void SetIsExporting(bool x) {
            lock (Locker) {
                isExporting = x;
            }
        }

        private static async void OnChanged(object source, FileSystemEventArgs e) {
            //Tableau version 2018.1.8 (20181.18.1213.2251) 
            //saves three times back-to-back when user clicks the save button
            //this ensures that only one save is performed per half-second
            if (GetIsExporting())
                return;

            SetIsExporting(true);
            await Task.Delay(500);
            Export();
            SetIsExporting(false);
        }

        private static void Export() {
            exportCount++;
            Console.WriteLine($"Exporting #{exportCount} File: {tableauFile}");
            var wr = new WorkbookReader(tableauFile);
            wr.ExportToCsv(outputPath);
            Console.WriteLine($"Complete.");
        }
    }
}

WorkbookReader

The WorkbookReader class extracts and exports the contents of the Tableau file. For brevity, here are just descriptions of its important methods.

Method: WorkbookReader (constructor)

The constructor reads and extracts the meta data from the Tableau file into a series of Dictionaries. Here is its signature:

public WorkbookReader(string workbookFile)

Method: ExportToCsv

This method creates a set of CSV files at the location specified by the path argument.

public void ExportToCsv(string path)

Future Improvements

There are a few "TODO" comments in the source code about minor code improvements. The project could be easily ported to .NET Core so that Mac users can use it. Also, there are some other aspects of the Tableau report that could be reported such as Table Calculations, Marks configuration, or Filter usage. And, finally, there might or might not be a good argument to include a full list of transitive dependencies, as its usefulness might not outweigh the added complexity required in the Meta Browser Woorkbook.

History

• 11^th October, 2019: Initial version

License Notes

Copyright 2019 M.C. Dean, Inc.

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.