UNION your way to a faster & easier Tableau report

On By bitipsblogIn Tableau

In this post I describe why using a UNION can be of great benefit when time is of the essence. Using a UNION can be a handy way to create Tableau reports which are easier to build, faster to load and provide more dynamic capabilities.

UNION multiple results

Using a UNION in a custom SQL allows you to combine data that you would otherwise get by data blending. Data blending is usually more cumbersome and when dealing with large amounts of data it can become a big problem. Therefore if possible, I suggest to combine data by using a UNION. The end result will be a report which is easier to build, faster to load and provides easy to use dynamic capabilities.

Below is a simple example of using UNION to combine different KPIs from two different aggregate tables and also combine input from an Excel file.

  1. Excel file example
    1
  2. Import file into Oracle database
    2
    3
    4
  3. Use a Custom SQL with UNION ALL in order to combine results from both aggregate tables & Excel file (UNION ALL is used as the results are distinct between the sources and it will be faster than UNION).
    5
    –Please note that the structure of a UNION must remain consistent, therefore I hard-coded ‘0’ for missing KPIs. If you are missing dimensions in parts of your query, I would suggest to hard-code them with ‘Not available’ or NULL.

UNION different levels of aggregation

It is quite common to have in reports multiple data sources due to different levels of aggregation. For example having one data source for daily information and one for monthly. When dealing with large data sources this type of data blending can cause the report to be quite slow.

Instead using a UNION will allow to create a much faster report. In addition I suggest to create a simple and handy dynamic filter to switch between levels of aggregation.

Below is a simple example of combining Day & Month aggregate data by using a UNION ALL and my suggested  “Overview” filter.  This simple solution is handy if you want to display information on a daily level for a recent period, but also want to provide ability to view more history on a monthly level.

  1. Custom SQL
    6
  2.  Preview result
    7
  3. The end result is a report which contains a dynamic filter which switches between the Monthly & Daily overview.
    • Overview = ‘Daily’
      8
    • Overview = ‘Monthly’
      9

UNION with ‘Source’ indication

My last tip in regards to using UNION, is to add a source indication for the different sources. This detail is handy if someone wants to know the source of the information or if you want to create special conditions in your report based on the sources used.

Below is a very simple example of a source indication used with a UNION

  1.  Custom SQL
    10
  2. Preview results
    11
  3. End result
    12

UNION in summary

Using UNION is not always possible and it is a practice that might be frown upon by some users. However if you have some SQL knowledge, it is a handy tool in order to create a report within minutes that is fast and already integrates dynamic capabilities such as switching between Day/Week/Month or different data sources.

Leave a comment