Using Wildcard Pattern-Based UNION in Tableau 10.1 instead of WDC

Tableau has had UNION for Excel and text files for several versions now, but Tableau 10.1 delivers a huge expansion of the feature with Wildcard Pattern-Based UNION. Now, you can set up a match pattern to bring in all the files you have into the UNION automatically.

To see how it works, check out the ever excellent Tableau Online Help.

Not only is this an immensely helpful feature for saving time the first time you are doing analysis, it also can save a ton of development time and effort when you are dealing with data sources that aren’t already in a database.


Assigning Colors via XML Modification

In Tableau, colors are assigned to a given dimension across the workbook (as opposed to just per sheet). This assignment actually happens in the XML, which means you can substitute in different color assignments if you are publishing from templates. Even better, in Tableau 10.0, if you assign colors from one of the included color palettes, new items will automatically be assigned a color from that palette.


Complex Fiscal Calendars in Tableau

Tableau has the option to specify the month that a fiscal calendar starts on, and the day of the week that a calendar starts on. This adjust the Year and Quarter that a given date belongs to in the date hierarchy. If your fiscal calendar is more complex,  you’ll need to do some adjustments to your dates to make everything work smoothly in Tableau.

Sometimes I put myself a note to write up a topic and lo and behold! there is already great content out there.

The standard solution, much like the currency conversion example, is to have a fiscal calendar mapping table, which you JOIN to your data. In essence, you have a row for each “real” date in the calendar,  with the information about where that day falls in the fiscal calendar (year, day, month, etc.) in additional columns. You’d base your time filters in Tableau on the fiscal date columns. There’s already a great example of this method on the Tableau Community. The example uses an Excel table, but obviously you would want that table in your database if you are bringing data from a database. Cross-database JOINing in Tableau 10 may let you keep the two separate.

The toughest trick with a complex fiscal calendar is how much it can diverge from the “real” calendar (although — is time really real, man?) Particularly for continuous date filters, the fiscal date might be really far off. If FY 2016 Month 1 Day 1 is actually July 15th, 2015, you don’t really want to show the “real” date, even if you want a continuous filter. I recommend creating a continuous integer that reflects the fiscal calendar, but not converting it into a date field: 20160101 will still be understandable and work efficiently for continuous filtering, but it won’t be confused for the “real” date.

InterWorks also has some great write-ups of solutions using calculations, that don’t require the mapping table:

Creating Dynamic Fiscal Calendars in Tableau

Creating Custom Fiscal Year Calendars in Tableau

One last thing to note, when publishing templates where the fiscal calendar start month or week start day varies from customer to customer: The “Date Properties…” menu in Desktop simply adds the following XML to the very end of the datasource XML, where it can easily be modified:

<date-options fiscal-year-start='august' start-of-week='friday' />


Currency Conversion and Exchange Rates in Tableau

Currency conversion comes up relatively frequently in conversations about Tableau, but there’s not a ton written about it specifically for Tableau, because the solution is actually a generic solution for currency conversion with relational databases.

The main thing to remember with exchange rates is that they change over time, and therefore need to be stored with a Date field. Any join to the fact data you have must JOIN on the originating currency and the date of the transaction. Some organizations may have specific rules on the exchange rates they use over a specific date range — I’ll be showing a daily example, but obviously if you calculate your exchange rates monthly for reporting purposes, the same concepts will work.


Adding Web Fonts to Tableau Server

Note: this is unsupported and adds things to the Tableau Server files, which also means you need to have a process to put it back in place with every upgrade. See CSS magic tricks with Embedded Tableau Server views and Disabling Browser and Apache Caching to See Your Changes to Tableau Server  which outline the necessary steps to make these types of CSS edits work.

You’ve got great design standards and awesome fonts, but you publish a beautiful viz to your Tableau Server and your end users end up seeing the default sans-serif or serif fonts that you’ve worked so hard to overcome. Tableau Server doesn’t have any in-built functionality for Web Fonts, but since it is just standard Apache serving up regular HTML5, JavaScript and CSS, you can easily add in anything you  want your users to have access to.

The secret is appending your web font CSS to vqlweb.css. I’ve already discussed how to edit the vqlweb.css file in the previous post mentioned in the warning at the top, and all the same rules apply. To get your font to display, you just need to add the appropriate Web Font css at the end. For example, Jeremy here built a kicking-rad viz with ‘Outrun future’.

Here’s the CSS I added to vqlweb.css:

@font-face {font-family: 'Outrun future';src: url("Outrun future.otf") format("opentype");}

The only other thing I had to do was copy the font file ‘Outrun future.otf’ to the /css/ directory (the same place where vqlweb.css lives).

Now go and add all the Web Fonts your heart desires (but make sure you can replicate your setup on each upgrade!)

Tableau and Behold our new guest blogger, Matt Miller!

Matt Miller has been an integral part of the sales engineer team at Tableau for several years now and he will be gracing us with an excellent series on the best ways of implementing Row Level Security when using Tableau Data Extracts.

Reminder that nothing here is official or supported by Tableau Software’s support team, regardless of who authors it, so please write things in the comments section and we’ll try our best to get back to you.

Defusing Row Level Security in Tableau Data Extracts (Before They Blow Up) Part 1

This documentation is for versions of Tableau prior to 2018.3. What are you doing here? You’ll want to implement Row Level Security using Multi-Table Extracts and the “Standard” Method of Row Level Security. Read all about them at:

How to set up your Database for Row Level Security in Tableau

Quick Explanation of Row Level Security in Tableau

If you are on a version prior to 2018.3, please turn around and plan an upgrade path

Matt Miller works at Tableau as a sales engineer along with Bryant. He’s guest posting this excellent set of articles, with hopefully more to come.

Note: This and Part 2 document some older techniques for achieving RLS in a much more limited version of Tableau that existed in the past. If you made it past the red text above, should follow their instructions. If you are just here for fun, skip right to Part 2, then come follow the instructions above in red.

A wise man who runs a well-respected Tableau blog once wrote:tdebomb

To be secure and perform well, you must use a Live Connection to your database. Extracts will be too large if you join a security table that is one-to-many to your data table (we say they “blow up” to dissuade people).

What happens when a live connection is less than desirable for a use case, or downright impossible? Are we to let our Tableau Server users swim in a sea of insecure extracts (‘Do my columns look wide in this?’) ? Certainly not. The methods used to secure extracts differ from most currently published Row Level Security setups or walk-throughs, as the design is centered around minimizing duplication of rows to ensure performance in both creating and using them.

Since my education in uncountable infinite sets ended in undergrad, this blog won’t try to cover every niche business logic and data security use case, but instead provide ideas and tools to take back you your own environment and mold to your needs.


Programmatically Changing Filters on Tableau Extracts

When you have a lot of Tableau extracts (TDEs), you want them to be as small as possible for both generation and performance in a workbook. If you are deploying extracts to a lot of different sites, you may have built an initial extract that is filtered down for Customer A on Site A, but want to publish it to Site B and have it filtered for Customer B. It’s not too difficult, although is does require working with the XML a bit.


Limiting Concurrent Tableau Sessions

The concept of selling for “concurrent users” comes up with relative frequency at Tableau. I’m not in any position to speak about licensing models, but if you are doing single sign-on to your Tableau Server, it’s very possible to implement a concurrency limiter.

The Tableau Server Repository has a view called _sessions, which only shows current active sessions. Prior to your SSO process, you can build a process that checks the _sessions view to see if there are already more active sessions than you want to allow.

My suggested query is the following:

SELECT s.site_id, t.url_namespace, COUNT(s.user_id)
FROM _sessions s
JOIN _sites t ON s.site_id = t.id
GROUP BY s.site_id, t.url_namespace


url_namespace from the _sites view gives you the identifier for a given Site. If you are using Sites, you need this to log in any way (This is called “site_content_url” in the REST API). All of the useful views in the Repository are listed in the excellent Data Dictionary.

Now, because concurrency in Tableau is really more about interactivity than time sitting, you’ll want to drastically shorten the session timeout from the default of 240 minutes. I’d suggest something like 5 so you aren’t locking people out when there is plenty of server capacity.

Also, the _sessions view contains the session_id field which can be used to programmatically end any session using the REST API’s sign out method. Per Russell Christopher: If you pass the session_id in the header like “X-Tableau-Auth: session_id“, it will end that session.