Tableau Tips & Tricks, Data Blending Revisited, Workflows, Architecture and More

Readers:

As I mentioned in one of my previous blog posts, I hosted Tableau training last week at work. The second class, taught on Thursday and Friday, was steeped with information and tips & tricks related to Tableau.

Since I never had any formal training with Tableau, I had been sitting in the class last week too. It is never too late to teach an old dog new tricks (or at least fill in the holes in my knowledge).

Below are a few tips & tricks I wanted to share from the two-day Tableau Desktop Intermediate class. Also, I have expanded a bit on a previous blog post I did in 2014 about Tableau Data Blending.

Thanks again to Evan Alini and Celeste Luna for the excellent training they provided us last week.

Best regards,

Michael

An Overview of the Tableau Platform and Products [6]

Tableau Visual Intelligence Platform v0.2

The Tableau Desktop Architecture Workflow

I used a variety of sources to build, what I feel, is a better depiction of how a visualization is creating using Tableau Desktop. [1][2][3][5][6]

Tableau Desktop Architecture Workflow

Tip: Some Extra Help in the Lower Left Corner

Evan showed us a tip I was not aware of. In the lower left corner of the Tableau worksheet is the following information.

Mark Information Snippet

This is a nice feature to help with your validation of the results in your workbook. If you look at the associated screenshot below, I have 17 marks (horizontal bars), 1 measure column (each horizontal bar represents Sales by Category, Sub-Category), and the total of all sales for the chart is $12,642,502 (if you removed the dimensions, the Sales measure would be the only field displayed).

Mark Information

Measure Values and Measure Names

Measure values and measure names are Tableau-generated fields that serve as containers for more than one measure. You can see the Measure Names field at the bottom of the list of Dimensions and the Measure Values field at the bottom of the Measures list in the Data pane. [1][2]

When you create a combined axis (measure using the same unit of measure such as Sales or Profits) or dual axis view (combine two different mark types including a Stacked Bar Chart), these fields appear in the view automatically, as does a Measure Values card that shows which fields are included. [2]

Measure Names and Values

Tip: Label versus Text on the Mark Card

When you have a picture (visual), you will see “Label” in the upper right of the Mark Card. [2]

Mark Card - Label

When there is not a picture (visual), you will see “Text” in the upper right of the Mark Card.

Mark Card - Text

Tip: Dashboard Actions

The Hover action occurs when you rest the pointer over a mark in the view to run the action. This option works best for highlight actions within a dashboard. [2]

Hover Action

Tip: Enlarge Text in the Calculated Field Editor

Evan showed us a really neat trick. When you are editing in the Calculated Field Editor, if you do a CTRL + Scroll Wheel on your mouse, the text will enlarge or reduce in size depending on the direction you are moving the scroll wheel. [2]

Calculated Field Mouse Scroll Wheel

Tip: Gauges

In class last week, there was a brief discussion about how to create gauges in Tableau. I know there are people out there that have found many creative ways to create these in Tableau, but this fits in the same category where Tableau let pie charts slip into their toolset. I will repeat what Stephen Few said about gauges when I originally took training from him over a decade ago (I am paraphrasing here).

“No!”

I concur. Leave gauges out of your data visualizations and out of Tableau. Nuff said.

Gauges

How Does Data Blending Work? [1][2][4]

Note: Check out my multi-part series on Data Blending in Tableau I blogged about in 2014 by clicking here.

First, to help us in discussing data blending, let’s look at a visual explaining the kinds of joins available.

sql-joins

Data blending is an alternative to joining, depending on factors like the type of data and its granularity. Data blending simulates a traditional left join. The main difference between the two is when the join is performed with respect to aggregation. [2][4]

Left Join (not cross-database)

Left Join

A single query is sent to the database where the join is performed. The results of the join are then sent back to Tableau for aggregation.

Data Blend

Blend

A query is sent to the database for each data source that is used on the sheet. The results of the queries are sent back to Tableau, and then combined. The view uses all rows from the primary data source, the left table, and the aggregated rows from the secondary data source, the right table, based on the dimension of the linking fields.

NOTE: Dimension values are aggregated using the ATTR aggregate function, which means the aggregation returns a single value for all rows in the secondary data source. If there are multiple values for the rows, an asterisk (*) is shown. Measure values are aggregated based on how the field is aggregated in the view. [2][4]

When to Use Data Blending [1][2][4]

Data blending should be considered in the following situations:

You want to combine data from different databases that are not supported by cross-database joins.

First, let’s define a Tableau cross-database join.

When related data is stored in tables across different databases, you can use a cross-database join to combine the tables.

To create a cross-database, you must create a multi-connection Tableau data source. You do so by adding and then connecting to each of the different databases (including Excel and text files) before you join. [7]

Cross-database joins do not support connections to cubes (for example, Oracle Essbase) or to some extract-only connections (for example, Salesforce). In this case, set up individual data sources for the data you want to analyze, and then use data blending to combine the data sources on a single sheet.

Data is at different levels of detail.

Sometimes one data set captures data using greater or lesser granularity than the other data set.

For example, suppose you are analyzing transactional data and quota data. Transactional data might capture all transactions. However, quota data might aggregate transactions at the quarter level. Because the transactional values are captured at different levels of detail in each data set, you should use data blending to combine the data.

Use data blending instead of joins under the following conditions:

Data needs cleaning.

If your tables do not match up with each other correctly after a join, set up data sources for each table, make any necessary customizations (that is, rename columns, change column data types, create groups, use calculations, etc.), and then use data blending to combine the data.

  • Joins cause duplicate data.Duplicate data after a join is a symptom of data at different levels of detail. If you notice duplicate data, instead of creating a join, use data blending to blend on a common dimension instead.
  • You have lots of data.Typically joins are recommended for combining data from the same database. Joins are handled by the database, which allows joins to leverage some of the database’s native capabilities. However, if you’re working with large sets of data, joins can put a strain on the database and significantly affect performance. In this case, data blending might help. Because Tableau handles combining the data after the data is aggregated, there is less data to combine. When there is less data to combine, generally, performance improves.

    Note: When you blend on a field with a high level of granularity, for example, date instead of year, queries can be slow.

Prerequisites for data blending

Your data must meet the following requirements in order for you to use data blending.

Primary and secondary data sources

Data blending requires a primary data source and at least one secondary data source. When you designate a primary data source, it functions as the main table or main data source. Any subsequent data sources that you use on the sheet are treated as a secondary data source. Only columns from the secondary data source that have corresponding matches in the primary data source appear in the view.

Using the same example from above, you designate the transactional data as the primary data source and the quota data as the secondary data source.

Note: Cube (multidimensional) data sources must be used as the primary data source. Cube data sources cannot be used as a secondary data source.

Defined relationship between the primary and secondary data sources

After designating primary and secondary data sources, you must define the common dimension or dimensions between the two data sources. This common dimension is called the linking field.

Continuing the example from above, when you blend transactional and quota data, the date field might be the linking field between the primary and secondary data sources.

  • If the date field in the primary and secondary data sources have the same name, Tableau creates the relationship between the two fields and shows a link icon () next to the date field in the secondary data source when the field is in the view.
  • If the two dimensions don’t have the same name, you can define a relationship that creates the correct mapping between the date fields in the primary and secondary data sources.

Data blending limitations

There are some data blending limitations around non-additive aggregates, such as COUNTD, MEDIAN, and RAWSQLAGG.

Tip: Blue Versus Orange Data Sources [2]

Blue versus Orange Data Sources

I hope this helps you better understand data blending in Tableau and gain an appreciate for the great knowledge you can gain from attending the Tableau Desktop II: Intermediate class.

I have a few more important Tableau topics to discuss, but I want to save them for another day so I can properly discuss and explain them.

Sources:

[1] Milligan, Joshua N., Learning Tableau 10 – Second Edition, Packt Publishing, 2016.

[2] Tableau Software, Tableau Classroom Training – Desktop II: Intermediate v10.2, Glendale, Arizona, June 14-15, 2017.

[3] Tableau Software, Tableau Desktop and Server Architecture, Tableau Software, Seattle, WA, January, 2013.

[4] Tableau Software, Blend Your Data, Tableau Help->Connect to and Prepare Data->Set Up Data Sources, http://onlinehelp.tableau.com/current/pro/desktop/en-us/multiple_connections.html.

[5] Pabba, Ramesh, Data Visualization with Tableau, Knowledgebee Trainings, November, 2015.

[6] Marc Rueter, Tableau Visual Intelligence Platform: Rapid Fire Analytics for Everyone Everywhere, Bloor Group, January, 2012.

[7] Tableau Software, Quick Start: Combine Tables Using Cross-Database Joins, Tableau Help, Tableau Software, Seattle, WA, 2017, https://onlinehelp.tableau.com/current/pro/desktop/en-us/qs_data_integration.html.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s