Understanding Level of Detail Expressions – Part 3

A key point users will need to come to terms with is that LOD expressions are not just a new form of table calculations. Technically they operate quite differently and although there is some overlap in the problems they can solve there is generally a clear line in determining which to use. The following table shows how the two types of calculation differ:

Table Calculations LOD Expressions
Table calculations are generated from the query results.

WINDOW_AVG(SUM([Sales]))

LOD expressions are generated as part of the query to the underlying data source. They are expressed as a nested select so are dependent on DBMS performance:

SELECT T1.[State],SUM(T2.[Sales per County, State])
FROM [DB Table] T1 INNER JOIN
(SELECT [State], [County],
AVG([Sales]) AS [Sales per County, State] FROM [DB Table] GROUP BY [State],[County]) T2
ON T1.[State] = T2.[State]
GROUP BY T1.[State]

Table calculations can only produce results that are less granular than the Viz LOD. LOD expressions can produce results that are more granular than, less granular than or totally independent of the Viz LOD.
Dimensions that control the operation of a table calculation are separate from the calculation syntax and specified in the “compute using” or “running along” menus. Dimensions that control the calculation for a LOD expression are embedded in the calculation syntax. For INCLUDE and EXCLUDE expressions these are relative to the Viz LOD, for FIXED expressions these are absolute.
Table calculations are always aggregated measures. Calculated fields with LOD expressions can be measures, aggregated measures or dimensions. INCLUDE/EXCLUDE expressions are always measures or aggregated measures. FIXED expressions are dimensions by default but can also be used as measures.
Table calculations cannot be used in other constructs. LOD expressions can be used in other constructs such as bins, groups, etc.
Filters on table calculations act as a hide – they do not remove records from the result set. Filters on LOD expressions act as an exclude – they remove records from the result set. Depending on whether the expression is FIXED or INCLUDE/EXCLUDE it is evaluated at different stages of the visualisation pipeline.
Posted in Uncategorized | 2 Comments

Understanding Level of Detail Expressions – Part 2

In the last post I explained how the three types of LOD expressions work. Clearly they make working with multiple levels of aggregation much easier, but we could solve these problems in the past using techniques like table calculations and blending. So how do the two approaches compare?

EXCLUDE

EXCLUDE expressions create results that are (more aggregated/less granular) than the viz LOD. Previously we often used table calculations to do the same because they are aggregated from the query results. Note that table calculations can only work “up” from the Viz LOD (i.e. more aggregated/less granular).

The addressing fields are the dimensions that would be excluded in the LOD expression.

The equivalent of an EXCLUDE expression could also be achieved by blending. You blend the data source against a copy of itself and explicitly unlink the dimension to be excluded.

The unlinked fields are the dimensions that would be excluded in the LOD calculation.

The equivalent of an EXCLUDE expression can also be achieved with subtotals and reference lines – however these are purely visual constructs and cannot be used in calculations.

INCLUDE

To achieve the equivalent of an INCLUDE expression you need to change the Viz LOD and make the query results (less aggregated/more granular).

You start by making the Viz LOD (less aggregated/more granular/lower).

You then use table calculations to aggregate back up to the desired LOD for display. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.

Once again, you could also use an unlinked self-blend to roll up from the new Viz LOD to the required display LOD. Same as before, y start by making the Viz LOD (less aggregated/more granular/lower).

The value from the blended data source is then used to show the original Viz LOD. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.

FIXED

What about FIXED expressions, I hear you ask? Well, before LOD expressions creating an aggregation completely independent of the Viz LOD was only possible with custom SQL.

Posted in Uncategorized | 4 Comments

Understanding Level of Detail Expressions – Part 1

[Before I get started with this post I wanted to recognise my colleague, Meredith Dicks, who had the initial idea for these LOD expression diagrams. Credit for the initial concept goes to him… I just refined the pictures.]

At Tableau our goal is to make the act of data analysis a delightful experience. Our users often tell us that when they are deeply engaged in Tableau they stop thinking about the mechanics of using the product and just have fun asking questions of their data. We call this experience flow – a state of joyful immersion in a task.

Sometimes though you encounter a question that is simple to articulate but when you try to answer it in Tableau you find it to be unexpectedly hard. Suddenly the state of flow is broken and you need to start thinking about how to solve the problem rather than focusing on the question and answer. This is jarring and can be frustrating for new users as they may not know how to proceed.

At the heart of all these problems is the need to work with data that has been aggregated to different levels. For example, to compare the average order size across sales regions with the worldwide average order size you need to calculate a value:

  • At the level of the order (i.e. you need to sum the individual line item values);
  • At the level of the region (i.e. take the total order value and average it by region); and
  • At the worldwide level (i.e. take the total order value and average it across all orders).

In Tableau we refer to the “level of detail” (abbreviated to LOD). This is essentially how aggregated or granular the data is and it is determined by the dimensions in the viz. By default, Tableau will aggregate the data LOD to match the Viz LOD:

Prior to Tableau 9 working with data at different levels of detail could be hard. Table calculations were one way to roll data up to a higher level of aggregation and there were other clever techniques using sets and blending, but they only worked for certain problem types and required you to think about the mechanics of the solution rather than keeping in the flow.

In Tableau 9 we are introducing a new feature called “level of detail expressions”. These allow you to directly define the level of detail at which a calculation should be performed which in turn removes the need for you to think about convoluted workarounds. They look like this:

{ FIXED [Order ID] : SUM([Sales]) }

The blue part of the expression should be familiar – it’s just an aggregation function that tells Tableau to calculate the sum of Sales. The red part is the new capability and it tells Tableau what level of detail to use when performing the aggregation. In this case, it tells Tableau to perform the aggregation for each Order ID, irrespective of the dimensions used in the viz.

There are three types of LOD expression – INCLUDE, EXCLUDE and FIXED – and we will explore them in more detail below.

INCLUDE

If you want to calculate an expression at a level that is (less aggregated than/more granular than/below) the Viz LOD, use the “INCLUDE” keyword. This adds the dimension(s) specified to the Viz LOD. As the result is (less aggregated/more granular) than the Viz LOD the value needs to be aggregated for each Viz LOD record (e.g. sum, avg, min, max, …).

EXCLUDE

If you want to calculate a result at a level (more aggregated than/less granular than/above) the Viz LOD, use the “EXCLUDE” keyword. This removes the dimension(s) specified from the Viz LOD. As the result is (more aggregated/less granular) than the Viz LOD the value needs to be duplicated for each Viz LOD record.

FIXED

The “FIXED” keyword allows you to specify the aggregation/granularity independently to the dimensions used in the Viz LOD. If the result is (more aggregated than/less granular than) or unrelated to the Viz LOD then it is duplicated for each Viz LOD record.

If the result is (less aggregated than/more granular than) the Viz LOD then it is aggregated to match.

Nested LOD Expressions

It’s also possible to create calculations that have multiple LOD expressions nested inside each other. For example, you could start with the viz LOD, then have an inner part that uses an INCLUDE expression to produce a more granular result. This is then wrapped in an EXCLUDE expression so the inner result is aggregated to the outer LOD. Finally, the calculation LOD is resolved back to match the LOD of the viz.

In the next post I’ll show how LOD expressions relate to the techniques we used to solve these types of problems in Tableau 8 and earlier.

Posted in Uncategorized | 15 Comments

Australian LGAs updated in 8.3.1

In the 8.3.1 Tableau update the built-in geocoding role for Australian LGAs (they are hidden away under the role title “County”) have been updated to the 2014 specification defined here. Previously they were using an earlier release and we didn’t recognise the “unincorporated” regions. Now we do. So, yay!

LGAs

Also remember that if you have custom geocoding roles loaded you need to remove them to get the new built-in roles to show up. Then you can reload your custom roles and all will be good.

Posted in Uncategorized | Leave a comment

“Tableau Classic” maps now provided from in-house mapping service

In Tableau 8.2 one of the changes we made was how we provide the underlying raster tiles for Tableau maps. Prior to 8.2 our tiles were provided through a 3rd party service provider but with the new release we changed to a platform run internally at Tableau. This allowed us to provide an updated set of tiles with improved styling, support for Hi-DPI content, more detail across the globe and additional data features.

If you wanted to use the old map tiles you were able to change back to the legacy tile service by selecting the Tableau Classic option through the Background Maps menu. The tiles would then be retrieved from the 3rd party service:

2014-12-11_9-42-26

Last week, in a move that should be completely transparent to you, we redirected the classic map service to also be provided from our internal map servers. Both the new and the classic tiles are now delivered from the same infrastructure – just as different “styles”.

While the classic tiles will look the same as before (please let us know if you see any differences) there is one nice benefit from this change – we now have the same level of global coverage with the classic style as we do with the new style. So in areas where we previously had the dreaded red X indicating no available tiles we can now see glorious detail:

Sheet 1

Posted in Uncategorized | Leave a comment

Plotting points with a reference circle of user defined center and size

I was asked by a customer today if it would be possible to create a Tableau viz that showed a set of locations (e.g. customers) on a map along with a user-defined circle to indicate a distance from a nominated point (e.g. a store).

Something like this? I replied…

Map with circle

Clearly it’s possible, but how do you do it?

The first step is to recognise that this is just an instance of plotting points and polygons on the same map – something I have blogged about previously. We can use the same techniques shown there – using custom SQL to get the point and polygon data into a single data source and then using an invisible mark (a 1x1px transparent PNG) to hide the circle on the points layer.

But how to create the data records for the circle? Again, we can leverage a technique previously explored – data densification. When creating our custom SQL all we have to do is append two records – one to be used as the start of the circle and one as the end. We can then use domain padding and densification to interpolate the values for the circle’s circumference.

Here is the full data set for the above visualisation – as you can see I am going to have 36 points around the outside of my circle (that should be enough to make it look smooth):

Identifier Latitude Longitude Order Type
1 Circle
36 Circle
A -37.7089 144.7861 1 Point
B -37.7165 145.025 1 Point
C -37.8294 145.0758 1 Point
D -37.5936 145.0401 1 Point
E -38.0179 145.1404 1 Point

From this we create our new Latitude and Longitude values using some simple high-school trigonometry:

//6.283185 is 2*PI (remember we are working in radians)
//0.009 is an approx scaling factor for Km to deg Latitude

if isNull(attr([Identifier])) 
 then [Center Latitude] + (0.009*[Spread Distance]*
   SIN(6.283185*INDEX()/[Num Points]))
 else attr([Latitude])
end
//6.283185 is 2*PI (remember we are working in radians)
//0.0117 is an approx scaling factor for Km to deg Longitude

if isNull(attr([Identifier])) 
 then [Center Longitude] + (0.0117*[Spread Distance]*
 COS(6.283185*INDEX()/[Num Points]))
 else attr([Longitude])
end

These calculations will densify when we create our final viz and use a Bin to pad out the Order field. Finally to make the workbook more flexible I set the spread distance, circle center lat/long and number of circle vertices as parameters.

You can download a copy of the workbook from here.

Enjoy!

Posted in Uncategorized | 5 Comments

Redirecting map tile requests

In an earlier blog post I explained how it is possible to add more detail to the offline map tile cache. This is very useful when you have a server environment that is not connected to the internet (and therefore cannot fetch tiles from the online service). But the question then arises – how do you get all your workbooks to redirect to the offline cache no matter what tile service they were authored for?

Fortunately this is simple to achieve – the tile services are defined by TMS files which are found in C:\Program Files (x86)\Tableau\Tableau Server\8.2\vizqlserver\mapsources. There are three main files here:

  • online.tms – the pre-8.2 map tiles provided by Urban Mapping
  • tableau.tms – the new 8.2 map tiles hosted by Tableau Software
  • offline.tms – used for fetching tiles from the local offline tile cache

To have all requests redirect simply replace the online.tms and tableau.tms files with copies of the offline.tms file (renaming the files to _old will also work). Now all three point to the same location – the offline tile cache. With some imagination you can see how this same technique could be used to redirect to any new map tile service – e.g. if you have a custom tile provider you are using just replace the standard TMS files with the one for your service.

Note that this modification will need to be redone after every upgrade as it won’t be carried forward.

Posted in Uncategorized | 1 Comment

Australian Local Government Areas (LGAs) now in Tableau 8.2.2

The Tableau product team continues to deliver the goods for Australian users. Another quiet feature release, this time in 8.2.2, allows us to plot LGAs out of the box both as points as well as filled maps. The trick is that the associated geographic role is “County” – hopefully the UI labels will be adjusted in a future release. Simply set your LGA field (either names or codes are recognised) to the County role and then drag and drop to render as a map.

Check out the following example of Socio-Economic Indexes for Areas (SEIFA) data plotted by LGA:

LGA

 If you just want the LGAs as a reference layer we also have (as reported in comments to my last blog post) the LGA boundaries and labels available in the map tile layer. Again it is masquerading as “US County Borders” and “US County Names”. They appear as shown:

LGA tiles

You can download the workbook (which also shows the same data by postcode/postal area) from here. I was going to upload this to Tableau Public but it seems that the LGA data isn’t working up there yet… only the postal areas.

Posted in Uncategorized | 6 Comments

Australian Postcode Filled Maps now in Tableau 8.2.1

My colleague Stephen Ermann in Singapore made an amazing discovery this afternoon – we now can draw filled maps for Australian postcodes directly out of the box!

filled pcodes

This is a huge improvement for users in AUS as this has been one of the most requested mapping features. I’m also advised that we can look forward to further enhancements in this area so stay tuned.

I’m not sure how this one slipped past me but I’m going to have to find some new tricks if I want to look like I’m doing clever stuff with maps. 🙂

 

Posted in Maps | 11 Comments

Australian postcode boundaries now available in Tableau 8.2 maps

I just stumbled across this new capability in Tableau 8.2 – we now have Australian postcode boundaries and labels as a selectable feature on the default maps. Check it out:

AUS postcodes

This is a result of us bringing our map tile service in-house – we can add these new data sets to the tile layer. Now we can all start voting on the feature request to have these as filled map marks. 🙂

Posted in Uncategorized | 2 Comments