There are multiple ways to model your data in Tableau environment. Your modeling method may change based on the amount of data you want to handle, the number of joins and types of joins you are using, the granularity of data sources, data refresh frequency and data source details.
1. Joins in Tableau: You can define join with tables coming from the same data source in the Data Source tab.
You will get options to define join type, columns on which you want to define joins and few data cleansing options like change the data type, rename the columns, split columns etc. You can have different joins with up to 32 tables in Tableau including self-join.
2. Cross-Database joins: You can define joins with different tables from different data sources. This feature is available from Tableau 10.0 version in Data Source Tab.
Just go to Data Source Tab, Click on Add and connect to the data source which you want to use.
Note: Cross-Database join is not supported by all the data sources. i.e Google Analytics, Microsoft Analysis Services, Microsoft PowerPivot, Odata, Oracle Essbase, SharePoint Lists, Teradata OLAP cannot be used in Cross-Database Joins (considering Tableau 2020.1)
In Cross Database joins, Blue color represents Primary Data Source and Orange Colour Represents Secondary Data Source.
Unlike Data Blending, you will have options to change the join connection of Cross-Database Joins.
3. Data Blending: Data blending is a method for combining data from multiple sources without defining any joins between them while connecting. Data blending is useful when you want to bring additional information from a secondary data source to displays it with the primary data source columns.
Unlike joins, data blending keeps the data sources separate and simply displays their information together. This is ideal when the data is at different levels of granularity.
The blending can be defined on common column names by default or can be modified using the Data→Edit Relationship option.
Data blending is performed on a sheet-by-sheet basis and is established when a field from a second data source is used in the view.
When you bring a field from one data source to the sheet—it becomes the primary data source. When you add another field from second data source, it becomes a secondary data source. An orange linking icon will appear in the data pane, indicating which field(s) are being used to blend the data sources.
The default join in Data Blending is left join & there is no way we can change this join condition.
4. Custom SQL: For most databases, you can connect to a specific query rather than the entire data set. You can use custom SQL to union your data across tables, perform complex joins, restructure or reduce the size of your data for analysis by applying where conditions.
5. Tableau Prep: Tableau 2018.1 has a data prep tool that can be used for defining joins, unions, and data cleansing processes. You can define your data model using Tableau Prep tool and the output file can be given as input to Tableau Desktop.
Tableau Prep enables analysts and business users to quickly and easily analyze their data.