New Features == Better Solutions

Some time back I used an example of plotting great circle routes on a map as a way to demonstrate data densification (https://blog.databender.net/2014/06/27/curved-lines-in-tableau-through-data-densification/).

062714_0107_CurvedLines4.png

To do data densification for the interpolated points we need two records for each route – one representing the start of the line and the other representing the end. In the original solution I used custom SQL to generate this scaffolding – running the query twice, each time returning a different number for the PointID, then unioning the two result sets together:

SELECT [Routes+Airports].[airline] AS [airline],  
[Routes+Airports].[from_code] AS [from_code],  
[Routes+Airports].[from_lat] AS [from_lat],  
[Routes+Airports].[from_long] AS [from_long],  
[Routes+Airports].[num_flights] AS [num_flights],  
[Routes+Airports].[to_code] AS [to_code],  
[Routes+Airports].[to_lat] AS [to_lat],  
[Routes+Airports].[to_long] AS [to_long],  
1 as [PointID]
FROM [Routes+Airports] 

UNION 

SELECT [Routes+Airports].[airline] AS [airline],  
[Routes+Airports].[from_code] AS [from_code],  
[Routes+Airports].[from_lat] AS [from_lat],  
[Routes+Airports].[from_long] AS [from_long],  
[Routes+Airports].[num_flights] AS [num_flights],  
[Routes+Airports].[to_code] AS [to_code],  
[Routes+Airports].[to_lat] AS [to_lat],  
[Routes+Airports].[to_long] AS [to_long],  
<Parameters.NumPoints> as [PointID]
FROM [Routes+Airports]

Clearly this is not an optimal solution as it a) requires a custom query which is inflexible and requires the user to know how to write SQL, and b) has to query the data source twice which is inefficient. However, at the time it was the only way to generate 2 result-set records per source record. I was messing about with this workbook yesterday and it occurred to me that we now have a better way to solve this problem. In Tableau 9 we introduced a new feature for data preparation – the PIVOT feature. We can now use this to generate the required scaffolding without custom SQL and multiple passes of the source data.

Here’s our input data:

1

We can pivot this on the from/to airport codes:

2

And the result is two records per route:

3

We can now create a calculated field to assign starting and ending PointIDs:

4

From there, the densification technique is much the same as the original. However, this time around I wanted to avoid using R to calculate the interpolated values as it is not something all users have set up. Dusting off my Google-fu, I found http://williams.best.vwh.net/avform.htm#Int which had the formula I needed to do the calculation natively in Tableau:

5

The result is a much better performing workbook – partly because we are reading the input data more efficiently and partly because we are using native calculations.

6

You can download the TWBX for this workbook here.

Enjoy!

Posted in Uncategorized | 3 Comments

Generating high resolution images of Tableau views

[This is one of those posts where I am going to show you something cool and useful, and then advise you that this is something you shouldn’t do. The URL in this post is undocumented and unsupported (by Tableau and by me) so if you are not comfortable with that, go no further. Here, there be dragons…]

Below is a conversation I have semi-regularly with customers:

Customer:  Hey Alan! We love our dashboards and we’d like to use them in some printed materials we produce. Can we do that?

Alan:   Of course you can. You just copy or export an image file and you have a nice PNG, JPG or BMP to work with. Whack that in there and away you go!

Customer:  Yeah, we tried that. But the image file formats are produced at screen resolution and become fuzzy when printed as part of a document. Can we get something better?

Alan:  OK – then try using a vector file format. You can export to EMF or PDF which produce nice scalable output?

Customer:  Again, we tried that. It’s pretty good, but it doesn’t work well for embedded bitmap content like map backgrounds, and we can’t export to EMF from Tableau Server so we can’t automate the process very well. What else can we try?

Alan:  Hmm… let me have a think about it.

[Walks over to the marketing team…]

Alan:  Hey guys! How do you produce the nice looking images you use in your handouts and printed materials?

Marketing:  We open them in Tableau Desktop on a Mac and take a screenshot to get a retina-resolution image.

Alan:  Hmm…

So I started looking for a way to have Tableau produce a high resolution image in a way that could be automated, and an idea came to mind. With the new Tableau mobile applications on iOS we have offline snapshots – these are high resolution images of the dashboards/views/stories/etc. They are rendered at retina resolution so is there any way we could use these?

I started digging around and eventually I found that these applications are using a special URL to request these images:

http://<server>/vizql/sheetimage/<view_id>?:pixelratio=2

The view ID is the internal Tableau ID from the repository. You can query it from there, or you can find it by browsing to the view in Tableau Server and then searching the HTML of the page for “current_view_id”. In Chrome, just open the developer tools (F12 or CTRL-SHIFT-I) and then CTRL-F to search:

current_view_id

By adjusting the :pixelratio parameter we can generate higher resolution images:

:pixelratio=1
This is the standard resolution for most devices.

:pixelratio=2
This is the resolution we generate automatically for retina devices.

:pixelratio=4
But we can take this higher…

:pixelratio=10
Much, much higher.

This allows us to create high-resolution images of our dashboards. Playing around with this I’ve discovered a few traps for young players:

  • You need to be logged in to a Tableau Server session to hit this URL. If you aren’t then you get the following error message:

{“result”:{“errors”:[{“code”:46}]}}

  • If your dashboard contains maps, using a :pixelratio greater than 2 won’t help as the maps are generated at :pixelratio=1 and blown up so they become grainy. Using :pixelratio=2 tells Tableau to use the hi-dpi map tiles like we would on a retina device.

The final step was to find a way to automate the production of the images via script. Because this is an internal URL it isn’t part of the traditionally scriptable interfaces such as TABCMD or the REST API. However using cURL, a command-line URL transfer tool, and trusted authentication we can create a simple script to emulate a user logging in and then fetching an image from this URL:

REM – Obtain a trusted authentication ticket and load it into an environment variable.
curl http://<server>/trusted -d "username=alan" > ticket.txt
set /P ticket=<ticket.txt

REM – Use the ticket to log in using any workable URL. The session cookie is persisted to a file.
curl "http://<server>/trusted/%ticket%/workbook.xml" -L -c cookie.txt

REM – Fetch the image file from the new URL using session data from the cookie file.
curl "http://<server>/vizql/sheetimage/1?:pixelratio=2" -b cookie.txt -o image.png

Note that this requires trusted authentication to be configured with unrestricted tickets enabled.

Enjoy – but remember this is unsupported and the URL structure could change at any time. Make hay while the sun shines, friends!

Posted in Uncategorized | 15 Comments

Even Hexier than Before…

[This blog post is a result of working with Sarah Battersby (Spatial Overlord and all-around Crazy Map Lady) from our Seattle office. We worked together for a TC15 presentation “Go Deep: Interpreting Dense Data with Tableau” where she presented the technique for creating uniformly spaced hexbins. Thanks for doing all the hard work, Sarah! 🙂 ]

At TC15 I had the pleasure of presenting a session with Sarah Battersby, a research scientist from the Seattle Tableau office who is a specialist in hex. We talked about dealing with dense data and in her session she covered heat maps which naturally lead to a discussion around hexbins.

Back in March I wrote a couple of posts that showed how to create polygons that wrapped around the center points of hexbins calculated using the HEXBINX() and HEXBINY() functions. Here’s the output:

Distorted

If you look closely, you can see that the shape of the polygons is gradually distorting as we move away from the Equator. By the time we reach Tasmania they are visibly taller than they are wide. This is due to the distortion effects of the Web Mercator projection we use in Tableau.

For some customers, it may be preferable to have polygons that are uniformly sized no matter where they are on the map. I’m not going to go into the pros/cons of doing this and how you might be distorting your data as the hexbins will not actually cover the same amount of area of the Earth’s surface – I’ll leave the details of that for Sarah if/when she ever finds this post. In any case, Sarah presented an elegant solution to this problem:

  • convert your lat/lon data into Web Mercator projection coordinates
  • create the hexbins in this coordinate system
  • calculate the vertex coordinates in WM coordinates
  • convert back to lat/lon and draw on the Tableau map

She outlines the maths for this in her slides:

Go Deep: Interpreting Dense Data with Tableau

You can see the results here – notice the hexagons are now uniformly sized no matter how far south we travel:

Tessellated

You can download the workbook from here to see the solution in action. Enjoy!

Posted in Uncategorized | 15 Comments

Great community post on Tableau and R integration

I just got pointed to this great post on the Tableau forums. Jonathan Drummey has compiled a great technical resource on how Tableau integrates with R via the SCRIPT_*() functions. A recommended read!

http://community.tableau.com/docs/DOC-5313

Posted in Uncategorized | 6 Comments

Designing Efficient Workbooks – the V9.0 edition!

Update 16 June 2016 – this has been superseded by the V10.0 edition, available here. Unless you are specifically looking for V9 advice, you should use the new document. Even if you aren’t the new version is probably a better resource with updated structure and content.


Well, it’s finally done.

Designing Efficient Workbooks V9

Thanks to all the people whose hard work has contributed to this document, and to all the reviewers whose input has helped make this approachable.

This will be available via the Tableau blog soon, but this is for the early adopters. Enjoy!

Posted in Uncategorized | 14 Comments

I’m a Hex Machine…

My last post showed how to create hexagonal polygons that fit around the points created using the new HEXBINX() and HEXBINY() functions in Tableau 9. This post is to show how you might use this technique to create an interactive heatmap dashboard that allows you to drill in from a high level to increasing detail.

(Click the image to view the interactive version on Tableau Public.)

2015-03-07_17-02-30

Enjoy!

Posted in Uncategorized | 3 Comments

I’m Too Hexy For This Viz…

I meant to have an early night tonight but after reading Kristopher Erickson’s wonderful blog Native Hexbin support in Tableau 9 my head was filled with thoughts of hex!

As Kristopher writes, there are now two new formulae in Tableau 9 that enable hexbin support:

  • HEXBINX(x,y)
  • HEXBINY(x,y)

He has done a great job coming up with a way to scale the hexbin count. By default the hexbins are created with a spacing of 1.5° Longitude and 0.866° Latitude but with Kristopher’s scaling functions you can make as many as you want…

  • Hex Lat = HEXBINY([Longitude]*[Sizing], [Latitude]*[Sizing])/[Sizing]
  • Hex Lon = HEXBINX([Longitude]*[Sizing], [Latitude]*[Sizing])/[Sizing]

This is great as you can increase the density of the hexbins as you zoom in, but if you are using a shape marker it won’t zoom in conjunction with your map so you can end up with something that looks like this:

030515_1412_ImTooHexyFo1.png

Or like this:

030515_1412_ImTooHexyFo2.png

It got me wondering if we could come up with an adaptive solution using two of my favourite techniques… polygons and data densification. And the answer is (after some pain with the trigonometry), yes we can! Here is the result:

Hexbin Density 1:

030515_1412_ImTooHexyFo3.png

Hexbin Density 2:

030515_1412_ImTooHexyFo4.png

Hexbin Density 10 (zoomed in on Sydney):

030515_1412_ImTooHexyFo5.png

As explained in a previous post, we need to make two passes over the data so we can have two points to interpolate between. In this case we need 6 points to plot the vertices around the hexbin, so my query looks like this:

030515_1412_ImTooHexyFo6.png

I create a bin over the PointID field to drive the domain padding. And I have three table calculations (which I run over the bin field to make them densify):

  • Angle = (1.047198 * INDEX())
    • The constant value is 60° expressed in radians – i.e. π/3
  • Plot Lat = WINDOW_AVG(AVG([Hex Lat])) + (1/[Hexbin Density])*SIN([Angle])
  • Plot Lon = WINDOW_AVG(AVG([Hex Lon])) + (1/[Hexbin Density])*COS([Angle])

Here’s what the viz looks like – in the end I was surprised with how elegant and simple it turned out:

030515_1412_ImTooHexyFo7.png

You can download the workbook from here. Now go forth, and be hexy!

Posted in Uncategorized | 13 Comments

Using Shape Files for Boundaries in Tableau

[With the release of Tableau 10.2 we now natively support shapefiles as a data source. This approach is no longer required. If you are using a version prior to 10.2 I strongly recommend you upgrade as the native support is much faster and easier to work with than this approach.]

[Once again, I’d like to recognise another person for helping make great content for this blog. This time it’s Susan Day, consultant extraordinaire at MIP (a wonderful partner here in Australia) who took my original workflow and converted it into the very useful macro format. I use this almost every day and it saves me mountains of time so thanks, Susan!]

I’ve posted several articles in the past about using polygons in Tableau to display custom regions on maps. A common format for the source data in many of these cases is a shape file – either an ESRI .SHP file or a MapInfo .MIF file. Neither of these files can be read natively by Tableau (give the idea a big +1 on our forums) so we need to extract the boundary information into a format we can use.

I have been using Alteryx to do this for a long time – one of the key reasons is this:

020515_0156_UsingShapeF1.png

Alteryx has a great tool for generalising spatial data – i.e. it decrease the number of nodes that make up a polygon or polyline, making a simpler rendition of the original spatial object. That’s great as it gives control over the number of vertices we create (making the resulting TDE file smaller and faster) but the check box I’ve highlighted is the real magic. When checked, line segments that are shared by multiple objects are generalised together, ensuring no gaps or overlaps occur between those objects as otherwise can happen when you simplify:

020515_0156_UsingShapeF2.png

So here is the workflow I’ve been using for a while. It has two paths – one to generate the boundary vertices and one to generate the polygon centroids. The reason to generate both and how to use them is outlined here and here.

020515_0156_UsingShapeF3.png

The problem with this is it is complicated to rebuild every time I need to convert a shape file. Enter the Alteryx macro, where this logic can be wrapped up in a single, reusable object. Download the macro .YXMC file from here and put it into your C:\Program Files\Alteryx\bin\RuntimeData\Macros directory. Now all the complexity is hidden behind a single macro icon:

020515_0156_UsingShapeF4.png

And our workflow now looks like this:

020515_0156_UsingShapeF5.png

Enjoy!

Posted in Uncategorized | 7 Comments

MGRS Coordinates in Tableau

I had an interesting question from a customer today – is it possible to use Tableau to show data that is tagged with Military Grid Reference System (MGRS) coordinates? For those unfamiliar with this system MGRS is based on the Universal Transverse Mercator (UTM) system which divides the Earth into 60 zones that are 6° of longitude wide. MGRS further divides the Earth into 20 latitude bands that are 8° high. Things get funky when you get really close to the poles (and I’m ignoring this for the purpose of this post) so the system starts with band C at 80°S and finishes at band X at 84°N. Oh – and just when you think this is a nice, neat mathematical model, they go and make some of the zones around Norway and Svalbard non-uniform so we can’t easily do this using calculations…

Using MGRS we can now reference an area of the world using the Grid Zone Designation (GZD) which is the longitude zone number followed by the latitude band letter – for example, Melbourne is in zone 55H.

020415_1259_MGRSCoordin1.png

These zones then subdivide further into grid squares that are 100 Km2 in size (i.e. 10Km on each side). These are identified by a two letter designation according to some complex rules but by combining the GZD with the 100 Km2 identifier we can now say that the Melbourne central business district is in 55HCU.

020415_1259_MGRSCoordin2.png

The zones drill down into increasingly smaller grid squares but this level will be enough for this post. Clearly, the coordinate identifiers in this model are describing an area and in Tableau we would want to use a polygon mark to represent this.

After a quick round of Google-fu I found a source of ShapeFiles for both the GZD boundaries and the 100 Km2 boundaries. The latter are very granular so I only downloaded the files required to cover Australia. As I’ve mentioned before, I use Alteryx for my quick ShapeFile -> Tableau Data Extract conversions and I have a nifty workflow (big shout out to Susan Day @ MIP who converted it into a macro for me – yay!) that outputs both the boundary vertices and the centroids so I can label the polygons if I want. The output of this process looks like this:

020415_1259_MGRSCoordin3.png

This gives me the data required to draw the MGRS grid. Now if I have quantitative data I want to plot (e.g. as a choropleth) I can use blending to bring the two datasets together. It’s the same technique I use in this blog post – watch the video to learn how:

020415_1259_MGRSCoordin4.png

You can download the TDE file for the worldwide GZD boundaries here, and the 100 Km2 boundaries for Australia here. Enjoy!

Posted in Maps | Tagged , | 6 Comments

LOD Rework 1 – Relative Date Filter

The other day I read a great post from Andy Kreibel (Tableau Tip Tuesday: Using a Set to Create a Relative Date Filter) where he showed a clever technique for simulating a relative date filter. His solution is based on sets and produces a filter that is anchored off the last observed date rather than the current system date. Very cool.

I’m sooo in love with LOD expressions at the moment, to the point where there is danger of me becoming “the LOD guy” as opposed to my previous reputation of “the guy who drinks too much at company parties”. Consequently I looked at this problem through my Tableau 9 glasses and realised this was a case where LOD expressions make the solution much simpler.

Here is the dashboard I created where the user can select the range of data they want to see:

013015_0659_LODRework11.png

And here is the underlying solution – with Tableau 9 we can express the filter logic in a single calculation:

Img2

Check out the last part of the calculation (click to see a bigger view). Notice the { } around the max() function?

{max([Order Date])}

This make it a LOD expression – but a special case of the FIXED type. Wrapping an expression in curly braces without setting a scope means calculate it for the entire dataset, ignoring all dimension and measure filters. So in this example the expression returns the last observed date in the entire data set.

Neat, huh?

I think this is a great example of how LOD expressions will help Tableau users remain focused on the question they are asking rather than having to break flow and think about how to manipulate Tableau’s mechanisms to achieve an outcome. The filter is now direct and explicit – return only those records where the Order Date is greater than the last observed Order Date, minus the required number of intervals.

And that – right there – is why I’m in love.

Posted in Uncategorized | 3 Comments