So far we have just looked at single dataset but you live in the real world data from different time periods, or at different levels or data sources have to be used in the same workbook.  You know how to do that with Vlookups and Appends but how would you do that with Tableau?   We’ll start with a simple example where the Sales $ come in on one file but the Quantities are on another:

The Sales file looks like this:

And these are the Quantities:

Right! It is the same data that we have been using – just in separate data sets.  Now we need to connect the 2 data sets.  Four years ago I recommended using a join but there have been improvements since then and now the best practice is to use the Relationship model

Bring the 2 files into Tableau and Drag the Sales and then the Quantities to the canvas – as you drag the second file a flexible orange noodle will appear to let you know you are creating a relationship.    Then you need to set the relationship link – we need 2 – on Product and the other on Customer

And when we go to the worksheet – there are 2 data sources each with their own dimensions and measures.  The relationship links on Product and Customer and then the Sales $ or Quantity from their respective sources

The same simple ASP calculation   Sum(Sales $) / Sum(Quantity)  now returns identical values as before – but the data are coming from 2 different data files – 

Congrats! – you’ve re-created a Vlookup in Tableau – pretty easy

Now let's see how we add the next month's sales and quantity data to the existing dataset – The Append command from Excel is done by creating a Union between the data sets

Here we will build upon the previous example by adding a second month of data in separate Sales and Quantity files –  We will need to create Unions between the monthly files to append the new month values and then use a relationship to connect the resulting files

After loading the second month's data – you can open the individual sales and quantity files and select “New Union”  – then add the new files (Note: you can also use a wildcard union – see  Link to Union KB  for instructions) 

For our example the relationship now needs to include the month of the date – You need to create a new link and Custom Calculation – 

Here "Month(date)" will do in each file

The results will bring in the new month values – note the new customer Steve in the second month and the same ASP calculation will work at the total and subtotal levels

Okay made it through Relationships and Unions – what's left – Blending – 

Full disclosure – Blending is my least favorite and connection type of last resort –  That said there are times when you must use a blend –

Like this one where data is coming from 2 different data sources.  We have been using an Excel data source so far and will continue to use that for Sales and Quantities

There are also sales goals but they are kept in an Access database.  They look like this:

When we try to upload both into Tableau we get this error

The data sets are from different data sources and can not be used in a relationship, combined with a union or a join – they must be blended.

 

Load the first data source as usual then you need to create a New Data Source for the second.  Then from the Data Source Tab select from the top ribbon

 

 From the drop-down I selected Access as the source and placed the data source on the canvas – you will only see the one data source at this point.

Open a worksheet and from the Sales and Quantity data set drag any dimension to the rows or columns – That will establish Sales and Quantity as the “Primary” data source – Drag any other dimensions and measures to the viz

Then open the Sales Target sheet – and you will see that Tableau has set a link between the Product dimensions in the 2 databases – The link is closed (active) which is fine – Tableau will establish links between any Dimensions with common names – You can open (break ) the link or establish additional links if needed.

Bring the Targets from the Secondary Source to the canvas – what happened?   The data in the secondary source is annual data so it is at a different level than the monthly data – It also is aggregated as it comes to the canvas – the default is sum() but even when changed to Min() as I have done will result with the annual data for each month

At this point the options are limited – we can write a calculation to convert the annual target to equal monthly values :

 SUM([sales Targets (example connections)].[Sales Goal])/12

And then the Percent to the month goal – 

Sum([Sales $])/[annualized goals]   

but not much more –

Blending also limits filtering – filters can only be applied across the data bases at the level of the link and LOD are not possible with data from blended data sources (more when we discuss the Order of Operation next) 

So that covers the connections basics – but you say Wait! What about Joins – 

The mechanics for creating joins mirrors that for relationships – they are just applied at the Physical Layer – not the Logical Layer like Relationships – see https://help.tableau.com/current/pro/desktop/en-us/datasource_datamodel.htm  for details)