Tableau - How to Switch Table(s) in Custom SQL Using Parameter(s)

Recently I had come across an interesting Tableau use-case that required myself needing the ability to switch Table(s) inside of my Custom SQL data source. This was needed to create incredibly large PowerPoint decks at scale. These decks are built to tell us what happened, why it happened, and recommended actions that benefit business metrics for our stakeholders.

Now anybody who has utilized the Python Tableau Server Client library to filter their Tableau Dashboard(s) knows about or has encountered an issue stating the “Request-URI Too Long” error message. This happens when passing a large view filter list and was the error that I was receiving on a frequent basis.

Tableau’s documentation recommends keeping the URI less than 2000 characters (limit the view filters used) or to utilize the JavaScript API’s POST request parameter as a possible resolution. However, this did not fit the criteria I needed as a solution.

So, I began thinking of possible alternative solutions to the URI issue that I was encountering. My first thought was to investigate if there was a way to encode the values being passed and make them shorter to circumvent the URI issue (e.g. Atlanta->1; Marietta->2, Kennesaw->3, etc.). This could be done using different numbering functions such as ROW_NUMBER and DENSE_RANK inside of Google BigQuery. Although in some cases this did work, it wasn’t a totally scalable approach.

An ideal solution to this problem would be the ability to dynamically switch tables inside of my Custom SQL data source.  Not only can this increase interactivity within your Tableau Dashboard, but it offers up creative solutions for a whole host of fun and interesting use-cases.

I started troubleshooting by beginning to search different forums, social media posts, and Tableau’s own documentation to determine if this was possible. Based on this Tableau Knowledge Base post I quickly came to the realization that this wasn’t totally possible in the sense of how I needed a solution. But I wasn’t satisfied and kept digging through creative and interesting ways to approach solving this.

The Solution

Queue up some native Google BigQuery functionality to the rescue. BigQuery offers a INFORMATION_SCHEMA.TABLES view that contains metadata about tables inside of a dataset. What we need specifically is to return the appropriate table name(s) as part of our custom Tableau Parameter list. The values populated in this list will be the different table(s) you want the ability to change your custom SQL data source too.

SELECT TABLE_NAME
FROM 
    `project.dataset.INFORMATION_SCHEMA.TABLES`
WHERE 1=1
    AND TABLE_NAME LIKE ('%INSERT_TABLE_NAME_HERE%')        

The next step is to return to your primary data source and modify it so that your custom parameter list can be used. In the query below you will see Wildcard tables being used. This enables users to query multiple tables at once. The TABLE_SUFFIX function ensures that you only scan the specified set of table(s) in-scope.

SELECT A.*
FROM 
    `project.dataset.*` A
WHERE 1=1
    AND _TABLE_SUFFIX = (

            SELECT TABLE_NAME
            FROM 
                `project.dataset.INFORMATION_SCHEMA.TABLES`
            WHERE 1=1
              AND TABLE_NAME =<Parameters.PARAM_GBQ_DYNAMIC_TABLE_PARAMETER>

)        

And that's it! You now have the ability to dynamically switch your Tableau Custom SQL data source(s).

______________________________________________________________________________________

Cover Image: Image by our-team on Freepik