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 Data Warehouse or Oracle 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.


    Oracle Analytics didn't find matching columns between the SALES and CHANNELS_VIEW tables.

    Description of drag_n_drop_joins.png follows
    Description of the illustration drag_n_drop_joins.png
  6. If necessary, select CHANNELS_VIEW and drop it onto the SALES table.


    When there's a table in the dataset that isn't joined, you need to implement a solution or remove the table to save the dataset.

    Description of sincomplete_joins_in_dataset.png follows
    Description of the illustration incomplete_joins_in_dataset.png

Review Table Columns

This section reviews the columns in the tables with the incomplete join.

  1. In the Join Diagram, click the incomplete join Incomplete join icon.
  2. In the Join dialog, click Select a column List icon under the SALES table name.


    In the SALES table, the CHANNEL_ID column is a measure.

    Description of channel_id.png follows
    Description of the illustration channel_id.png
  3. In the Join dialog, click Select a column List icon under the CHANNELS_VIEW table name.


    In this example, the CHANNELS_VIEW table uses CHANNEL_NUM as its primary key. CHANNEL_NUM doesn't match the SALES table that uses CHANNEL_ID.

    Description of channel_num.png follows
    Description of the illustration channel_num.png

Transform Data to Create a Join

This section shows one way to change the data definition of a column to complete the required join.

This example splits the CHANNEL_NUM column in the CHANNELS_VIEW table to remove ID- from the CHANNEL_NUM column, and creates a column that is renamed to match the CHANNEL_ID column in the SALES table.

  1. Click the CHANNELS_VIEW tab.


    Description of channels_table.png follows
    Description of the illustration channels_view.png
  2. Right-click the CHANNELS_NUM column, select Options, and then select Split.
  3. In Split Column, select Dash (-) in the Delimiter field. In Number of parts to create, enter 2. In New column 2 name, enter a CHANNEL_ID.


    Description of split_column_dialog.png follows
    Description of the illustration split_column_dialog.png
  4. Click Add Step.


    Description of split_column.png follows
    Description of the illustration split_column.png
  5. Click the Join Diagram. Click the incomplete join Incomplete join icon between the SALES and CHANNELS_VIEW tables. In the Join dialog under SALES, click Select a column, and then select CHANNEL_ID.
  6. In the Join dialog under CHANNELS_VIEW, click Select a column, and then select CHANNEL_ID.


    The CHANNEL_ID in the SALES table is a measure. You need to change CHANNEL_ID column's Treat As property to an attribute in the SALES table.

    Description of join_dialog_channel_id.png follows
    Description of the illustration join_dialog_channel_id.png
  7. Click the SALES table tab.


    In the Join dialog, the CHANNEL_ID in the SALES table is treated as a measure. You can convert the CHANNEL_ID column to text to use it in the join with the CHANNELS_VIEW table.

  8. In the SALES CHANNEL_ID column, select Options, and then click Convert to Text.


    Description of sales_convert_to_text.png follows
    Description of the illustration sales_convert_to_text.png
  9. Click the Join Diagram. Click the incomplete join Incomplete join icon between the SALES and the CHANNELS_VIEW table.
  10. In the Join dialog, select CHANNEL_ID in the under SALES table. Under CHANNELS_VIEW, select CHANNEL_ID.


    You can now save the dataset.

    Description of /all_joins.png follows
    Description of the illustration all_joins.png

Learn More