Blending data sources in Tableau - Deep Dive

Overview - Blending data sources

What is it?

Blending data sources in Tableau means combining data from different places into one view. It lets you see information together even if the data lives in separate files or databases. This helps you analyze and compare data without moving or copying it all into one place. It works by linking common fields between the sources to join their data visually.

Why it matters

Without blending, you would have to manually combine data before analysis, which is slow and error-prone. Blending saves time and lets you explore relationships between different data sets quickly. It helps businesses make better decisions by seeing the full picture from multiple data sources at once. Without it, insights could be missed or delayed.

Where it fits

Before learning blending, you should understand basic Tableau concepts like connecting to a single data source and creating simple visualizations. After mastering blending, you can learn about data joins, data prep tools, and advanced calculations that work across multiple sources.

Mental Model

Core Idea

Blending data sources is like linking two puzzle pieces by their matching edges to see a bigger picture without forcing them into one piece.

Think of it like...

Imagine you have two photo albums from different trips. Instead of gluing all photos into one album, you place them side by side and connect matching dates or places with sticky notes. This way, you can compare memories without mixing the albums.

┌───────────────┐     ┌───────────────┐
│ Data Source A │────▶│ Common Field  │
└───────────────┘     └───────────────┘
         │                     │
         ▼                     ▼
┌───────────────────────────────┐
│      Blended Visualization     │
└───────────────────────────────┘
         ▲                     ▲
┌───────────────┐     ┌───────────────┐
│ Data Source B │────▶│ Common Field  │
└───────────────┘     └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding data sources in Tableau

Concept: Learn what a data source is and how Tableau connects to one at a time.

In Tableau, a data source is where your data lives, like an Excel file or a database. You start by connecting Tableau to one data source to create charts and tables. This is the first step before combining multiple sources.

Result

You can create visualizations from a single data source.

Knowing how Tableau connects to one data source is essential before trying to combine multiple sources.

2

Foundation

Identifying common fields for linking

Concept: Learn that blending requires a shared field to link data from different sources.

To blend data, Tableau needs a field that exists in both sources, like 'Customer ID' or 'Date'. This field acts like a bridge to connect the data without merging the sources physically.

Result

You understand which fields can link data sources for blending.

Recognizing common fields is key to successful data blending.

3

Intermediate

How Tableau blends data sources

🤔Before reading on: do you think Tableau physically merges data tables when blending, or does it keep them separate? Commit to your answer.

Concept: Tableau keeps data sources separate and combines data only in the view using common fields.

When you blend data, Tableau treats one source as primary and the other as secondary. It fetches data from the secondary source only for matching values in the primary source. This happens dynamically when you build your visualization.

Result

You see combined data in your view without physically joining tables.

Understanding that blending is a visual join helps avoid confusion about data duplication or performance.

4

Intermediate

Setting up a blend in Tableau

🤔Before reading on: do you think you must manually tell Tableau which fields to blend, or does it guess automatically? Commit to your answer.

Concept: Tableau automatically detects common fields but lets you control which fields link the data.

After connecting to multiple data sources, Tableau shows a chain link icon next to fields it thinks can blend. You can click to activate or change the linking fields. This controls how data from different sources relate in your view.

Result

You can control and customize how data sources blend.

Knowing how to manage linking fields prevents incorrect or incomplete blends.

5

Intermediate

Limitations of data blending

🤔Before reading on: do you think blending supports all types of joins like inner, left, right, and full? Commit to your answer.

Concept: Blending supports only a left join-like behavior from primary to secondary source and has some restrictions.

Blending only brings in matching data from the secondary source for values in the primary source. It cannot do full outer joins or join on multiple fields easily. Also, aggregated data from secondary sources can cause unexpected results.

Result

You understand when blending might not fit your needs.

Knowing blending limits helps you choose the right method for combining data.

6

Advanced

Using blending with calculations and filters

🤔Before reading on: do you think filters on secondary data sources affect the primary source data in a blend? Commit to your answer.

Concept: Filters and calculations behave differently on primary and secondary sources in blending.

Filters on the primary source affect the whole view, but filters on the secondary source only affect its data after blending. Calculations using secondary data must be aggregated. This affects how you design your dashboard and calculations.

Result

You can create accurate blended views with correct filters and calculations.

Understanding filter and calculation scope prevents common blending errors.

7

Expert

Performance and best practices in blending

🤔Before reading on: do you think blending always improves performance compared to joins? Commit to your answer.

Concept: Blending can impact performance and requires careful design to avoid slow dashboards.

Because Tableau queries multiple sources separately, blending can slow down dashboards if data is large or linking fields are not indexed. Best practices include minimizing secondary data, using extracts, and preferring joins when possible.

Result

You build efficient blended dashboards that scale well.

Knowing blending's performance tradeoffs helps you design faster, more reliable reports.

Under the Hood

Tableau creates separate queries for each data source. The primary source query runs first, returning a set of keys from the linking field. Then Tableau queries the secondary source, filtering on those keys to fetch matching data. Finally, Tableau combines the results in memory to display the blended view.

Why designed this way?

Blending was designed to let users combine data from different systems without complex ETL or database joins. It trades off some flexibility and performance for ease of use and quick insights. Alternatives like data joins require data to be in one source, which is not always possible.

┌───────────────┐       ┌───────────────┐
│ Primary Query │──────▶│ Primary Data  │
│ (Data Source) │       └───────────────┘
└───────────────┘              │
                               ▼
                      ┌─────────────────┐
                      │ Linking Keys Set │
                      └─────────────────┘
                               │
                               ▼
┌───────────────┐       ┌───────────────┐
│ Secondary     │◀──────│ Secondary     │
│ Query (filter │       │ Data Source   │
│ on keys)      │       └───────────────┘
└───────────────┘              │
                               ▼
                      ┌─────────────────┐
                      │ Blended Results  │
                      └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does blending combine all rows from both sources like a full join? Commit to yes or no.

Common Belief: Blending merges all data rows from both sources into one big table.

Reality: Blending only brings in matching rows from the secondary source for keys present in the primary source, similar to a left join.

Why it matters: Expecting a full merge can cause missing data and incorrect analysis.

Quick: Can you blend data on multiple fields at once in Tableau? Commit to yes or no.

Common Belief: You can blend data on several fields simultaneously to get precise matches.

Reality: Tableau supports blending on one or a few fields, but complex multi-field blends are limited and tricky.

Why it matters: Trying to blend on many fields can lead to wrong results or force workarounds.

Quick: Do filters on secondary data sources affect the entire blended view? Commit to yes or no.

Common Belief: Filters on any data source affect all data in the blended visualization equally.

Reality: Filters on secondary sources only filter their data after blending; they don't filter the primary source data.

Why it matters: Misunderstanding filter scope can cause confusing dashboard behavior.

Quick: Does blending always improve dashboard speed compared to joins? Commit to yes or no.

Common Belief: Blending is faster than joining because it queries sources separately.

Reality: Blending can be slower due to multiple queries and data transfer, especially with large data sets.

Why it matters: Assuming blending is always faster can lead to poor performance and user frustration.

Expert Zone

1

Blending aggregates secondary data before joining, so detail-level data from secondary sources may not behave as expected.

2

The order of data sources matters: the primary source drives the keys and filters, affecting the blend outcome.

3

Blending does not support all join types or complex relationships, so sometimes data prep or joins are better.

When NOT to use

Avoid blending when you need full outer joins, complex multi-field joins, or when performance is critical. Instead, use data joins within Tableau or prepare combined data in a database or ETL tool.

Production Patterns

Professionals use blending for quick ad hoc analysis across systems like Salesforce and Excel. For dashboards with heavy interaction or large data, they prefer data extracts and joins. Blending is also used to combine live data with static reference data.

Connections

Database Joins

Blending is a visual, partial join alternative to database joins.

Understanding blending clarifies when to use database joins versus visual combinations, improving data modeling choices.

ETL (Extract, Transform, Load)

Blending reduces the need for ETL by combining data at analysis time.

Knowing blending helps decide when to prepare data beforehand or blend on the fly for faster insights.

Supply Chain Management

Blending data sources is like linking suppliers and shipments data to see overall flow without merging systems.

Seeing blending as connecting separate but related systems helps understand complex data relationships in business.

Common Pitfalls

#1 Using blending expecting all data from secondary source to appear.

Wrong approach:

Dragging fields from secondary source without linking fields or ignoring missing matches, expecting full data.

Correct approach:

Ensure linking fields exist and understand only matching rows from secondary source appear in the blend.

Root cause:

Misunderstanding that blending behaves like a left join, not a full merge.

#2 Applying filters on secondary source expecting to filter primary data.

Wrong approach:

Adding filters on secondary source fields and expecting primary source data to reduce accordingly.

Correct approach:

Apply filters on primary source to affect whole view; use secondary filters knowing they only affect secondary data after blend.

Root cause:

Confusing filter scope between primary and secondary sources in blending.

#3 Blending large data sets without performance considerations.

Wrong approach:

Blending multiple large live sources without extracts or indexing, causing slow dashboards.

Correct approach:

Use extracts, limit secondary data, and prefer joins when possible for better performance.

Root cause:

Not realizing blending runs separate queries and can be slower with big data.

Key Takeaways

Blending data sources in Tableau lets you combine data from different places visually without merging tables physically.

It requires a common field to link data and behaves like a left join from primary to secondary source.

Filters and calculations behave differently on primary and secondary sources, so understanding scope is crucial.

Blending has limits in join types and performance, so sometimes data joins or ETL are better choices.

Knowing when and how to blend data helps create accurate, efficient, and insightful dashboards.