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.

Practice

1. What is the main purpose of blending data sources in Tableau?

easy

A. To create a single table by merging all data sources permanently

B. To combine data from different sources using common fields without merging tables

C. To replace the primary data source with a secondary one

D. To export data from Tableau to external databases

Solution

1. Step 1: Understand blending concept

   Blending connects data from different sources using common fields without merging them permanently.

2. Step 2: Compare options

   To combine data from different sources using common fields without merging tables correctly describes blending. Options A, C, and D describe other unrelated actions.

3. Final Answer:

   To combine data from different sources using common fields without merging tables -> Option B

4. Quick Check:

   Blending = combine without merging [OK]

Hint: Blending links data without merging tables [OK]

Common Mistakes:

• Confusing blending with joining or merging tables
• Thinking blending replaces the primary source
• Assuming blending exports data

2. Which of the following is the correct way to identify the linking field in Tableau data blending?

easy

A. The field with a red cross in the primary data source

B. The field with a blue checkmark in the secondary data source

C. The field with a green plus sign in the primary data source

D. The field with an orange chain link icon in the secondary data source

Solution

1. Step 1: Recognize linking field icon

   In Tableau, linking fields in the secondary data source show an orange chain link icon.

2. Step 2: Match options to icons

   The field with an orange chain link icon in the secondary data source correctly identifies the orange chain link icon as the linking field. Other options describe incorrect icons or colors.

3. Final Answer:

   The field with an orange chain link icon in the secondary data source -> Option D

4. Quick Check:

   Linking field = orange chain link icon [OK]

Hint: Look for orange chain link icon for linking fields [OK]

Common Mistakes:

• Confusing linking field icon with checkmarks or plus signs
• Looking for icons in the primary instead of secondary source
• Ignoring icon colors

3. Given two data sources: Primary with fields OrderID, Sales and Secondary with fields OrderID, CustomerName. If you blend on OrderID and create a view showing Sales and CustomerName, what will happen if an OrderID exists only in the secondary source?

medium

A. The view will exclude that OrderID because it is missing in the primary source

B. The view will show Sales as null and CustomerName for that OrderID

C. The view will show Sales and CustomerName as null

D. The view will cause an error and not display

Solution

1. Step 1: Understand primary source control

   In blending, the primary source controls the rows shown. If an OrderID is missing in primary, it won't appear.

2. Step 2: Analyze missing OrderID in primary

   Since the OrderID exists only in secondary, it will be excluded from the view.

3. Final Answer:

   The view will exclude that OrderID because it is missing in the primary source -> Option A

4. Quick Check:

   Primary source controls rows = missing keys excluded [OK]

Hint: Primary source controls rows shown in blending [OK]

Common Mistakes:

• Expecting secondary-only keys to appear in the view
• Thinking null values appear for missing primary keys
• Assuming blending causes errors on missing keys

4. You blended two data sources on CustomerID. However, the secondary data source fields show Null values in your view. What is the most likely cause?

medium

A. You forgot to refresh the data sources

B. The primary data source is missing the CustomerID field

C. The linking field CustomerID has mismatched data types between sources

D. The secondary data source is set as primary by mistake

Solution

1. Step 1: Check linking field compatibility

   Blending requires linking fields to have matching data types to join correctly.

2. Step 2: Identify cause of nulls

   If data types differ, Tableau cannot match keys, causing nulls in secondary fields.

3. Final Answer:

   The linking field CustomerID has mismatched data types between sources -> Option C

4. Quick Check:

   Matching data types needed for linking fields [OK]

Hint: Check data types of linking fields if nulls appear [OK]

Common Mistakes:

• Assuming missing fields cause nulls instead of mismatched types
• Confusing primary and secondary source roles
• Ignoring need to refresh data

5. You have two data sources: SalesData (Primary) with OrderID, SalesAmount and CustomerData (Secondary) with CustomerID, OrderID, Region. You want to create a view showing total sales by region. How should you blend and aggregate the data correctly?

hard

A. Blend on OrderID, use Region from secondary, and create a calculated field summing SalesAmount grouped by Region

B. Blend on CustomerID, use Region from secondary, and sum SalesAmount without grouping

C. Join the two sources on OrderID instead of blending, then sum SalesAmount by Region

D. Blend on Region, then sum SalesAmount grouped by OrderID

Solution

1. Step 1: Identify correct linking field

   OrderID exists in both sources and links sales to customer region.

2. Step 2: Blend on OrderID and aggregate

   Blend on OrderID, use Region from secondary, then sum SalesAmount grouped by Region for total sales by region.

3. Final Answer:

   Blend on OrderID, use Region from secondary, and create a calculated field summing SalesAmount grouped by Region -> Option A

4. Quick Check:

   Blend on OrderID + sum Sales by Region [OK]

Hint: Blend on common key, aggregate sales by region [OK]

Common Mistakes:

• Blending on wrong field like CustomerID or Region
• Not grouping sales by region
• Confusing blending with joining