How to change code in Tableau Server data sources
- select the contributor at the end of the page -
Today, is kind of big day for Tableau. If you’re into that sort of thing, you know they recently announced the new Tableau Zen Masters--and we’re pretty pumped that one of Pluralsight’s very own, Mr. Mike Roberts, our Director of Data Analytics, keeper of Pluralsight’s GitHub code and all-around Tableau guru, was dubbed as one of 21 Zen Masters for 2015-2016.
And if you’re not super familiar with what it takes to be a Tableau Zen Master, allow us to break it down for you: You become a Zen Master based on your contribution to the bigger Tableau community, which spans the Desktop and/or Server product. Mike became a Zen Master because of his serious contributions to their server product. (He helped author “Tableau Your Data,” contributed to a server white paper and worked for Facebook and other Fortune 500 companies.) Basically, Mike is your go-to guy to ensure your Tableau Server is running smoothly and maintained well--and luckily for you, he’s here to explain how to change code in Tableau Server data sources. So get your Tableau Zen on, and take a lesson from this master.
Changing code in Tableau Server data sources with Tableau Zen Master Mike Roberts
It’s common to have Tableau Server data sources on various sites across your Tableau infrastructure. What’s also frequently asked about (and needed) is the ability to both see what’s within those data sources (fields, data types, etc.) as well as the table(s) or SQL used in the source. For the metadata information, we’ve already released the script here. It will get a server admin visibility into the fields, data types, formulas in calculated fields and tables used in the data source, not to mention the custom SQL being leveraged.
For the updating source tables/SQL, however, we’ve realized it’s often a challenging aspect for some enterprises and it usually goes like this: You’ve created a shared data source for the team and connected a few workbooks and now it needs to be changed and updated. How does a busy server admin do this for multiple data sources in multiple environments? One wouldn’t want to:
- Connect to the source
- Create a local copy
- Edit the connection
- Paste new SQL and validate
- Refresh
- Republish
Instead, let’s make this automatic. For example, you might have updated SQL for the connection (see image above) that needs to be changed. You can either pull this from a config file or even a GitHub repo. All you need is the updated SQL for the data source. Here’s a preview of the code (image below) to do just this and here is where it’s available.
What this does is exactly what is listed in the bullet points above. Here, however, we’re just plugging a few variables (site, server, username, etc.) and running this script.
What if you don’t want to keep plugging in variables every time you have new updated SQL? Well, that’s the perfect use case for using Windows events and triggers. As long as the file is updated, the data source can be updated. See another example of how we did this with Tableau Extract triggers here. Basically, once the file is updated, a few moments later (depending on the polling interval you set), the script above would run.
Some other use cases for this automatic updating of data sources include, but are not limited to:
- Changing tables
- Changing csv files
- Updating formulas
- Server names
One final note to push you in the realm of complete automation (well, not totally complete) is the idea of config or master file. Let’s say you have a list of workbooks that need to be changed and these workbooks might have a different primary key or ID of some sort. Further, let’s add the fact that each workbook must have a different user level filter so the owner can only see what he/she was meant to see and, bonus, you need these published and operated on daily. How do you do it? Make sure you come see our Tableau Conference session to get the full details.
In the meantime, get more Zen Master tips from Mike here.
