Before You Begin

This tutorial shows you how to create joins in a dataset with multiple tables in Oracle Analytics.

Background

When you create a dataset with multiple tables where relationships are defined between the tables, Oracle Analytics automatically creates the joins between the tables in the dataset. When you create a dataset from multiple connections and schemas, you might need to define the joins between tables.

In some cases, the joins aren't created because columns use different data types or different columns names. You might need to transform data in existing columns or create columns to use for joins between tables.

What Do You Need?

  • Access to Oracle Analytics Cloud or Oracle Analytics Desktop.
  • Ability to connect a relational data source such as Oracle Autonomous AI Lakehouse or Oracle AI Database.
  • Access to a sample schema to perform the steps in this tutorial, see Installing Sample Schemas
  • Ability to run a script using SQL Developer or a related tool.

Create a CHANNELS_VIEW Table

  1. Open SQL Developer and connect to a database with SH schema as SH user or connect as a database user with the ability to create views in the SH schema.
  2. Select the SH schema, and then run the following script to create or replace the sh.CHANNELS view:
    CREATE OR REPLACE view sh.CHANNELS_VIEW as (
SELECT
    'ID-'||channel_id as channel_num,
    channel_desc,
    channel_class,
    channel_class_id,
    channel_total,
    channel_total_id
FROM
    sh.channels);

    You use the CHANNELS_VIEW in the dataset in the next sections.

Create a Dataset from a Connection

In this section, you use the drag and drop approach to create joins. The SALES, CUSTOMERS, PRODUCTS, TIMES, and CHANNELS_VIEW tables from the SH schema comprise the dataset.

When creating a dataset with multiple tables, start with the fact or transaction table, and then add other tables.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, select a connection that supports datasets with multiple tables to use as the source.
  4. In the Connections panel, expand the SH schema, and then drag the SALES table to the Join Diagram.


    Description of sales_fact_table.png follows
    Description of the illustration sales_fact_table.png
  5. Hold down the Ctrl key, select the CHANNELS_VIEW, CUSTOMERS, PRODUCTS, and TIMES tables from the SH schema, and then drag them to the SALES table to create the joins.


    Description of sincomplete_joins_in_dataset.png follows
    Description of the illustration incomplete_joins_in_dataset.png
  6. In Join SALES-CHANNELS_VIEW, click Select a column List icon under SALES and click CHANNEL_ID. Under CHANNELS_VIEW, Select a column List icon and click CHANNEL_NUM.


    Oracle Analytics issues a message that the CHANNEL_NUM column is a measure instead of an attribute required for the join.

  7. Click Yes in the message to create the join. Click Save.
  8. In Save Dataset As, enter a Name and click OK.

Learn More