Course: Tableau Training | Topic: Understanding Primary and Secondary Data Sources | Level: Beginner to Intermediate
Learning Objectives: Understand primary and secondary data sources, learn how Tableau identifies each source, configure linking fields, create a blended visualization, understand aggregation and missing values, troubleshoot common issues, and choose the correct data-combination method.
In Tableau, a data source is a connection between Tableau and data stored in a file, database, server, or other supported system. When analyzing data from separate sources in a single worksheet, Tableau can use a primary data source and one or more secondary data sources through a feature called data blending.
The primary data source is the source used first to build the view. The secondary data source supplies additional fields and measures that Tableau combines with the primary source's results through linking fields.
For example, a company might store sales transactions in an Excel workbook and sales targets in a separate database. Tableau can use Sales as the primary source and Targets as the secondary source to compare actual sales with targets by region.
Official reference: Tableau - Blend Your Data
A primary data source is the data source that establishes the main structure of a worksheet when you begin creating a visualization. Tableau uses its fields to organize the view and determine which dimension members are represented.
Suppose the primary Sales source contains the following data:
| Region | Sales |
|---|---|
| East | 500000 |
| West | 380000 |
| North | 420000 |
If Region from this source is placed on Rows, Tableau builds the view around East, West, and North. When another source is blended into the worksheet, its matching information can supplement these regional results.
A secondary data source is an additional source used alongside the primary source in a blended worksheet. It provides information that is not available in the primary source, or that is maintained separately.
Suppose the Targets source contains:
| Region | Target |
|---|---|
| East | 450000 |
| West | 400000 |
| North | 400000 |
| South | 430000 |
If Sales is primary and Targets is secondary, Tableau can display Sales and Target for the matching regions. South may not appear as an independent region because it is absent from the primary source's Region values.
| Feature | Primary Data Source | Secondary Data Source |
|---|---|---|
| Purpose | Establishes the main view. | Supplies additional information. |
| Selection | Usually the first source used in the worksheet. | Another source used in the same worksheet. |
| Indicator | Blue check mark. | Orange check mark. |
| View structure | Often determines the main dimension members shown. | Supplements the existing view with matching data. |
| Data processing | Queried and aggregated according to the visualization. | Queried and aggregated separately, then combined with the primary results. |
| Linking fields | Contains the primary side of the link. | Contains the corresponding secondary fields. |
| Missing matches | Its dimension members may remain visible even when secondary data is missing. | Unmatched values may result in NULL or may not appear as separate members. |
| Example | Actual sales. | Sales targets. |
Tableau uses visual indicators to help users identify which source is primary and which is secondary in a blended worksheet.
Interface details may vary slightly by Tableau version. A secondary source must have an active linking field for the blend to work correctly.
The first data source used to build the worksheet generally becomes the primary data source. Tableau then treats other sources used in the same view as secondary sources for blending.
Important: The source order matters. Rebuilding a worksheet with a different source first can change which dimension members appear and therefore change the results.
Linking fields are corresponding dimensions used to match records or aggregated results between the primary and secondary data sources. A linking field must represent the same business concept in both sources, and the values and data types must be compatible.
| Primary Source Field | Secondary Source Field | Purpose |
|---|---|---|
| Region | Region | Compare regional sales and targets. |
| Order Month | Target Month | Compare monthly performance. |
| Product ID | Item Code | Match product-related information. |
| Customer Segment | Segment Name | Compare segment-level performance. |
| Department | Department Name | Compare departmental costs and budgets. |
Automatic linking: Tableau may identify common fields automatically when corresponding dimensions have matching names.
Manual linking: If the fields have different names, you can define the relationship manually through the blend relationship settings.
For example, the primary source might use Region, while the secondary source uses Territory. If both fields contain the same type of regional values, they can be mapped as corresponding fields.
A retail company wants to compare actual sales with targets for each region. Actual sales are stored in Sales.xlsx, while targets are stored in Targets.xlsx.
| Region | Sales |
|---|---|
| East | 500000 |
| West | 380000 |
| North | 420000 |
| Region | Target |
|---|---|
| East | 450000 |
| West | 400000 |
| North | 400000 |
| South | 430000 |
| Region | Sales | Target | Difference | Status |
|---|---|---|---|---|
| East | ₹5,00,000 | ₹4,50,000 | ₹50,000 | Above target |
| West | ₹3,80,000 | ₹4,00,000 | -₹20,000 | Below target |
| North | ₹4,20,000 | ₹4,00,000 | ₹20,000 | Above target |
Note: South is present in the secondary Targets source but absent from the primary Sales source. It may not appear as a separate region in this blended view. This illustrates how primary-source selection affects the visible dimension members.
To calculate the difference between actual sales and target, create a calculated field:
SUM([Sales]) - SUM([Target])A positive value means sales exceeded the target, while a negative value means sales fell short. Ensure the fields are available in the blended worksheet and that the calculation follows Tableau's aggregation requirements.
SUM([Sales]) / SUM([Target])Format the result as a percentage. For East, the result is approximately 111.11%, meaning actual sales are around 11.11% above target.
IF SUM([Sales]) >= SUM([Target]) THEN "Target Achieved"
ELSE "Target Not Achieved"
ENDThis calculation classifies each region based on its sales performance. If Tableau displays an aggregation error, check the aggregation of each field and the restrictions that apply to secondary-source calculations.
Changing the primary data source can change the members displayed in a blended visualization because the primary source defines the main view.
Suppose the Sales source contains East, West, and North, while the Targets source contains East, West, North, and South.
| Primary Source | Likely Region Members in the View | Explanation |
|---|---|---|
| Sales | East, West, North | South has no corresponding member in the primary source. |
| Targets | East, West, North, South | Targets supplies the main region members; Sales may be missing for South. |
To show all regions, consider rebuilding the worksheet using the source that contains the complete set of required members as primary, or evaluate whether relationships or another data-modeling approach better fits the analysis.
| Problem | Possible Reason | Solution |
|---|---|---|
| Target values show NULL | No matching region exists in the secondary source. | Check the source values and linking fields. |
| Secondary source cannot be used | No active link exists. | Activate a link or configure the blend relationship. |
| Some regions are missing | The primary source does not contain those regions. | Review the primary source or use another data-combination method. |
| Linking fields are not recognized | Field names differ or the fields are not mapped. | Define a custom field mapping. |
| Data does not match correctly | Different data types, capitalization, spaces, or date granularity. | Standardize values and verify compatible data types. |
| An asterisk (*) appears | Multiple secondary values correspond to a mark in the primary view. | Review the level of detail and ensure the blend matches the intended granularity. |
| Totals seem incorrect | Aggregation or grain differences affect the combined result. | Validate source totals independently and reconsider whether blending is appropriate. |
| Filters do not affect both sources as expected | Filtering has limitations with secondary data sources. | Review Tableau's cross-data-source filtering options or consider relationships or joins. |
Official troubleshooting reference: Tableau - Troubleshoot Data Blending
| Method | How It Works | Typical Use |
|---|---|---|
| Data Blending | Queries separate sources and combines aggregated results in a worksheet. | Worksheet-specific combinations and certain separate-source scenarios. |
| Join | Combines tables into a fixed row-level result using a join clause. | When a physical row-level combination is required. |
| Relationship | Defines how tables are related and allows Tableau to determine how to query them during analysis. | A flexible data model, including tables with different levels of detail. |
Best practice: Relationships are Tableau's default approach for combining tables in a data model and are often the best starting point. Use joins when you need a fixed row-level result, and blending when worksheet-level source combination or a supported separate-source scenario makes it suitable.
Official reference: Tableau - Plan the Data Source
Answer: It is the source used first to create a worksheet and establishes the main structure of a blended visualization.
Answer: It is an additional source that supplies dimensions or measures to the primary view through linking fields.
Answer: Tableau generally displays a blue check mark for the primary source and an orange check mark for a secondary source in a blended worksheet.
Answer: The first source used to build the worksheet generally becomes primary. Other sources used in the same view act as secondary sources for blending.
Answer: It is a common or mapped field used to match corresponding values between sources, such as Region in Sales and Targets.
Answer: Yes. Corresponding fields can be mapped manually when their names differ, provided they represent compatible information.
Answer: The values might not match, the linking fields may have incompatible data types, or the secondary source may not contain the required member.
Answer: The dimension members displayed in the view can change because the primary source establishes the main view structure.
Answer: No. Blending combines independently queried and aggregated results in a view, while a join creates a combined row-level table.
Answer: Relationships are often preferred when combining tables within a data model, particularly when tables have different levels of detail.
Understanding primary and secondary data sources is essential for using Tableau data blending correctly. The primary source establishes the view, while secondary sources add related information through linking fields. By configuring the links properly, checking the level of detail, and understanding how missing matches and aggregations behave, analysts can create more accurate reports and make better business decisions.
Explore the following resources to learn more about Tableau, data blending, dashboards, and business analytics:
