There are two ways in tableau BI Services by which we can connect two data sources.
- 1. Joins: SQL joins that we use normally in Databases
- 2. Data Blending: The concept of creating relationships between data sources but at worksheet level.
Most of the things that you need can be done by either Joins or Blending in Tableau.
The question that arises in mind is why do I use blending if I can use join? Which scenarios make it more suitable to use blending?
Let’s tackle it one by one.
- 1. When there is no option for you to join your data source. For an instance: If the data sources don’t support cross database joins, and we have to use both the data sources in the visualizations. Now you can use blending to achieve goals.
- 2. When there is a minor requirement for you to connect two data sources and you don’t want to use join as the data size is large.
- 3. When your both data sources are having different levels of aggregations. Like one data source is at Country level and other at city level.
Implementing Data Blending:
- Data blending works with two or more than data sources. One primary data source and the other secondary data source.
- Whichever data source you get the first field into the visualization becomes the primary data source and the other becomes secondary.
- First create relationships by finding the link or the common dimension between your data sources.
Get your both data sources into the workbook. Now go to data sources and click on edit relationships:
1. Now define the fields that you want to use as links from both the data sources.
2. Bring in the field from the data source that you want to consider as primary.
3. This now acts as Left Outer Join for you and Sales is the primary or Left table for this.
4. Bring in the fields from your secondary data source. Now the data is blended and your goal is achieved.
5. As you have seen it's very easy to achieve a Left Outer Join using Data Blending. If you want to achieve Inner Join using Data Blending, then you can just exclude the null values from the secondary data source.
What you cannot achieve using Data Blending?
- You cannot use non-additive aggregates, such as COUNTD, MEDIAN, and RAWSQLAGG.
- A Blended data source acts as two different data sources and need to be published separately online.
- You need to use the aggregated data from the secondary data source.