How to Blend Relationships in Tableau

Combining data from various data sources is fundamental to strong analysis. Tableau supports two powerful processes in accomplishing this: relationships and data blending. This tutorial clarifies the complexity to show you where each method belongs and when to use them effectively.

Understanding the Basics: Relationships vs. Blends

Before jumping into the step-by-step guidance, it's vital to grasp the core differences between relationships and blending. They may seem related, but they operate at very distinct stages of your data handling process and function in fundamentally different ways.

What Are Relationships in Tableau?

Relationships represent Tableau’s newest and preferred method for combining data from multiple tables. Think of a relationship as a flexible contract between two tables. You inform Tableau about how the tables relate to each other on a common field (like Order ID or Customer Email), but you don't physically unify them into one large table.

Instead of merging everything from the start, relationships allow the tables to remain separate but connected. When you create a visualization by pulling in fields from different tables, Tableau intelligently looks at your analytic context and constructs a custom query to fetch just the necessary data, joined according to your specified view. This makes them dynamic and often more performant.

On your Data Source tab in Tableau, links are represented by elegant visual "noodles" connecting your tables. These visually signify the potential connection waiting to be used.

What is Data Blending in Tableau?

Data blending is Tableau's older method for combining data from separate databases. Unlike relationships, which are set up on the Data Source level, data blending occurs at the specific visualization level. It happens after the data has been queried from each data source independently.

With blending, you have a primary data source and one or more secondary data sources. The data from the secondary sources is aggregated based on the combination field of the primary source before it gets displayed in your visualization. Essentially, it performs a type of left join with the summarized results. Any data from the secondary source that doesn't have a match in the primary data source based on the active blend field is dropped. A light orange link icon next to a field in the Data Pane indicates it is being used for blending.

Key Differences at a Glance

Here's a quick breakdown of the distinctions:

• Level of Operation: Relationships define how tables relate at a data source level, while blends join aggregated data at a specific visualization level.
• Location of Setup: Relationships are created in the Data Source pane. Data blending settings are configured in specific visualizations by clicking on "link" icons.
• Data Combining Point: Relationships combine data before any visualization logic is applied ("pre-viz"). Blending combines data after visualizations retrieve and aggregate it ("post-viz").
• Default Preference: Relationships are Tableau's default and recommended method for merging data whenever possible.
• Flexibility: Relationships are more flexible as Tableau adapts the connection types on the fly based on the fields you use. Blending works like a LEFT join on summarized data.
• Visual Indicators: Relationships use "noodles" on the Data Source page. Blending shows orange checkmarks and link icons in the Data pane on a worksheet.

How to Use Tableau Relationships to Unify Data

Relationships should be your go-to option for nearly all data combination cases where data is in the same source or in sources that can be related together. Here's a simple walkthrough.

Let's say we have two Excel files: a table of Order Info and another of Product Information. The orders table contains Product ID, and the items table contains product name and category, linked via the shared Product ID.

Step 1: Connect to Your First Data Source

Open Tableau and connect to your first Excel file, selecting the Order Info sheet. It will appear as a box on the Data Source page.

Step 2: Add Your Second Data Source

Next, click "Add" to add a connection with the second Excel file containing the product information. Instead of creating a second data source, drag the Product Information sheet into the canvas next to the first box. Tableau will automatically create a "noodle" between the two boxes.

Step 3: Explore the Relationships Details

Tableau is generally intelligent enough to detect the common field. Click on the noodle to open the "Edit Relationship" dialog box. Here, you can confirm or change Tableau’s selections. In our case, it will probably correctly recognize that the related field in both tables is Product ID.

In this window, you can also configure performance options like cardinality (one-to-many, many-to-many) to further inform Tableau about the nature of your data, which helps it craft more optimized queries. In most cases, the defaults will work just fine.

Step 4: Build a Visualization

Navigate to a new worksheet. In your Data pane on the left, you will notice the tables organized by their source folder (Orders Data, Product Data). Now you can create visualizations that combine these different tables seamlessly.

For example, you can drag the Category field from the Product Information table onto Rows and the Sales field from the Order Info table onto Columns. Tableau immediately displays a chart showing sales per category, automatically handling and executing the join operations based on the relationships you defined.

When and How to Use Data Blending in Tableau

While relationships are the default, data blending still has its place and is essential for certain scenarios that relationships can't handle directly. Here's how to use it.

When is Data Blending Necessary?

• Different Levels of Detail (Granularity): This is the classic use case for blending. Suppose you have daily sales transaction data in one data source, but your sales goal data from a different source is only available at a quarterly level. You cannot simply relate them by date without creating duplication issues. Blending allows you to bring in the quarterly targets and compare them against the daily sales aggregated at the month level.
• Joining via Published Data Sources: You cannot use relationships to combine tables from a published data source on Tableau Server or Tableau Cloud. You must use data blending for these scenarios.
• Working with Cubed Data Sources: Certain types of sources, known as cube data sources (like Microsoft SQL Server Analysis Services), do not support relationships. Blending is the only option to combine them with other data.

Let's continue with the sales data and quarterly targets scenario to demonstrate how blending works.

Step 1: Connect Your Primary Data Source

Start a new Tableau worksheet. At the top menu, go to the "Data" tab and select "New Data Source." Connect to your primary database containing daily sales transactions. This will be your primary Data Source as it has the most granular (detailed) data.

Once connected, navigate to a new worksheet and drag the date field such as Sales Date into the rows shelf and set to the month view. This establishes Sales Date (Month) as the linking field for this view.

Step 2: Add the Other (Secondary) Data Source

Go back to Data > Add Data Source and connect to your second Excel document containing your quarterly sales goals, which has a Month column and a Sales Goal column.

Navigating back to your worksheet, you will now see both Data Sources in your Data pane. The sales transaction database is the primary source because you connected it first, indicated by a blue checkmark in your menu. The sales goals data source is currently the secondary source, indicated with an orange checkmark next to its fields.

Step 3: Activate the Blend and Build Your View

Tableau will try to find the blending fields automatically. Look at your Data pane under your secondary (Goals) data source. You should see a small link icon next to the Month dimension field. If the link icon is gray and broken, it means the link is inactive. Click it to activate it - it will turn orange. This tells Tableau to use this field to integrate this data from the second source.

Now, drag the Sales Goals field from that secondary goals data source into the rows shelf alongside your sales measure. Tableau will align your total monthly sales next to the corresponding Quarterly Sales Goal for each Month. You have successfully blended data from two different levels of detail.

Practical Tips and Common Pitfalls

Whether you're using relationships or blends, adhering to a few best practices can save you a lot of headaches.

• Relate Before Blending: Always set up your data combinations with relationships first. Only switch to blending when you encounter one of the specific scenarios where it's required, like different granularity issues.
• Understand Your Grain: Knowing the level of detail in each table is fundamental to any data combination operation. Mismatched grains are the number one cause of mangled numbers and confused charts, especially with data blending.
• Watch for the Little Asterisk (*): When blending data, you might sometimes see the asterisk (*) appear as a value from your secondary source. This indicates that there are multiple member values in the secondary source corresponding to the member from the primary source. This is a sign that your grains are not aligned properly, and you need to verify your data structure or the fields in your visualization.
• Clean Linking Fields: Ensure that the fields you use to link or blend your data are consistent in name, type (e.g., string vs. number), and format. A Customer ID that's treated as a number in one table and as text in another will fail to match.

Final Thoughts

Mastering how to combine data sources in Tableau really boils down to understanding the primary differences between relationships and data blending. Relationships offer a flexible, efficient framework perfect for most scenarios, while data blending serves as a specialized tool for handling uneven granularity or connecting certain types of data sources. Choosing the right tool for the job will make your analytical dashboards not only more accurate but also much quicker and easier to manage.

While tools like Tableau provide powerful capabilities to connect your data visually, the initial challenge of identifying the needed sources, understanding the structures, and setting up the relationships accurately can still be a time-consuming process. At Graphed, we streamline this whole journey by utilizing AI. You easily connect your promotional and marketing data sources like Shopify or Google Analytics, and then you can create the necessary dashboards just by describing what you want to see in plain language, allowing our AI to pick connections and link up that data behind the scenes for you.