Sign in or start a free trial to avail of this feature.
8. Blending Tables Across Data Sources
If you want to join tables across different data sources, data blending may be necessary. In this lesson, we will learn how to create relationships between a SQL database and a separate CSV file.
To explore more Kubicle data literacy subjects, please refer to our full library.
- Database joins are easy to create across tables within the same data source
- However, certain data sources do not support joins to external data tables
- In this case, we need to use data blending, and create relationships across columns
In a previous lesson, I joined two tables together from the same database before performing my analysis. Now, what happens if I want to join two tables from different sources? Well, it depends. Historically, you could not perform a join across two different data sources. You had to blend data by creating a relationship. However, in later versions of Tableau, a new feature called cross database joins were introduced so that you could join tables from two different sources under certain circumstances. To create a cross database join you simply click Add and select the new data source that you wish to create the join from.
Unfortunately, cross database joins are still not available for all types of data sources. And on occasion, you will need to use data blending to join tables from different data sources. And this is what we're going to do in this lesson. Currently, I have a database hosted on Amazon Web Services but my Pharma data included in it. In this database, I want to create a relationship between the current table that I've created and a CSV file.
The CSV file simply groups the different drug types under broader categories so that I can create the following chart that shows the amount prescribed for each drug type. But the drug types are all color-coded by their broader category. And so I can also filter by selecting a specific category and then I can see the amount prescribed for each drug type. To create this connection between the CSV file and the database, I'm going to use data blending. So let's get started. From my sheet, I go to data and add new data source and I'll select a text file.
And here is the text file.
And as you can see in the text file which is called Chapters at CSV, I have an ID and I have the name of the various categories in which I want to place each drug type. Now let's great relationship between this data source and my database. I'll go to data and edit relationships.
And in custom, I'm going to add my relationship which is going to be Chapter ID and ID.
And then I'll simply delete the remaining blends which are not relevant here and I have entered by Tableau automatically.
There we go. And we now have our single relationship. I'll create this relationship and now generate my chart.
So I'll start by taking the amount prescribed which is this column and then I'll add the name of each drug type. Unfortunately, I still have my data source filter in place. So only diuretics is showing. Let's go back to the data source and remove this filter.
As you can see, I know of all of the remaining drug types showing.
I'll change my chart type to pact bubbles. And now I simply need to color code this chart. So what the relationship in place that's simply color code with name and as you can see, I have my finished chart which shows me the amount prescribed for each drug type, color-coded by category. We can also add a filter and I'll show this filter on my chart as well and make sure that it's a single value drop down which I think looks a little better. And now my chart is complete.
As I mentioned at the beginning of this lesson, it's possible to use joins from many different data sources. However, it's always good to know how to fall back on using blending and relationships if the need arises. As I've shown you in this lesson.