Understand Dataset Table Joins

A dataset can have multiple tables. The table joins you add indicate the relationships between the dataset's tables.

Tutorial

By default, when you drag and drop tables to the Join diagram to create a multiple table dataset, any joins that are defined in the data source are automatically created between tables in the dataset. Oracle Analytics also automatically creates joins base on the column name matches it finds between the tables. When defining your dataset, be sure to add the most detailed table first. This is usually the primary table for your analysis. For example, the Sales table. Then add the remaining tables like Products, Customers, Channels, and so on that provide context for your analysis. If you're adding tables from a dataset that is dimensionally modeled or a transactional table, then the most detailed table is possibly a fact table with measures.

In some cases you might want to build a dataset's joins manually. For example, if your dataset contains too many matching columns and it's time consuming for you to delete the unnecessary matches. In such cases you can turn off automatic joins for your dataset. See Disable Auto Joins in Datasets.

If you're creating a dataset based on facts and dimensions and if joins already exist in the source tables, then joins are automatically created in the dataset. If you're creating a dataset from multiple connections and schemas, then you manually define the joins between tables. To do this, drag and drop a dimension table onto a fact table and specify the type of join to use and the join columns. You can create a join on more than one column. The columns that you're joining must be attribute columns and have the same data format.

Oracle EPM Cloud, Oracle Essbase, or Google Analytics aren't available for you to add and join to in a dataset that contains multiple tables. When you're working in a workbook and include data from different datasets, for example a dataset containing multiple tables from Oracle Autonomous Data Warehouse and a dataset containing data from Oracle EPM Cloud, then you use blending to match the columns. See Blend Datasets.

Note:

To analyze data based on two star schemas, create a dataset for each star schema, add the datasets to a workbook, and then blend the datasets.

The Join Diagram displays join icons representing the type of joins used between tables and if any joins are incomplete. Join icons are Venn diagram thumbnails that represent the join type.

Icon	Description
	Indicates an inner join. When this icon is selected, it's displayed in blue. An inner join returns rows from the left table that match with the rows from the right table.
	Indicates a left join. When this icon is selected, it's displayed in blue. The left join returns all rows from the left table with the matching rows if available from the right table. If there is no matching row found from the right table, then the left join contains null values for the columns of the right table.
	Indicates a right join. When this icon is selected, it's displayed in blue. The right join returns all rows from the right table with the matching rows if available from the left table. If there is no matching row found from the left table, then the right join contains null values for the columns of the left table.
	Indicates a full outer join. When this icon is selected, it's displayed in blue. The full outer join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. If there is no match, then the missing side has nulls.
	The dashed line indicates an incomplete join. When this icon is selected, it's displayed in blue. When this icon isn't selected, it's displayed in yellow. Click the icon to open the Join editor and assign or fix the join.