Before You Begin

This 15-minute tutorial shows you how to create datasets that contain multiple tables for use in analyses.

Background

You can create a dataset that uses tables from different connections and schemas. Oracle Analytics uses the relationships defined between these tables to create the joins in your dataset. If you're creating a dataset from multiple connections and schemas, you define the joins between tables in Oracle Analytics. When relationships aren't defined between tables, you can define them in Oracle Analytics.

You can use tables from relational sources including data from relational databases, data sources that are based on ODBC/JDBC connections such as SaaS data sources, and Oracle Service Cloud.

When defining your dataset, add the tables that form the base for your analysis first such as Sales, Customers, and products, and then add other related tables. The most detailed table is possibly a fact table with measures, if you are adding tables from a data source.

This is the first tutorial in the Prepare and Analyze Datasets series in Oracle Analytics. Read the tutorials in the order listed.

What Do You Need?

  • Access to Oracle Analytics
  • Ability to connect a relational data source such as Oracle Autonomous Data Warehouse or Oracle Database
  • Access to the Oracle sample SH schema to perform the steps in this tutorial, see Installing Sample Schemas

Create a Dataset

In this section, you create a dataset using tables from an Oracle Database. You must have an existing Oracle Database or Oracle Autonomous Data Warehouse connection and the details required to create a connection to a supported data source.

This example uses the SH schema from an Oracle Database connection. The steps in this tutorial are all related to using the tables in the SH schema.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, select the Oracle Database connection to use as the source.
  4. In the Connections panel, expand Schemas, and then expand the SH schema.
  5. Under the SH schema, select the SALES table and drag it to the Join diagram.


    A data preview shows the columns and data in the SALES table.

    Description of sh_sales_table.png follows
    Description of the illustration sh_sales_table.png
  6. Under the SH schema, hold down the Ctrl key, select the following tables, and then drag them to the Join Diagram.
    • CHANNELS
    • COUNTRIES
    • CUSTOMERS
    • PRODUCTS
    • PROMOTIONS
    • TIMES


    Oracle Analytics automatically creates the joins between the tables using the relationships defined in the schema.

    Description of sales_joins.png follows
    Description of the illustration sales_joins.png

Apply Changes to Tables in the Dataset

In the Join Diagram, you can select individual tables, apply transformations or enrichments in the selected table's data columns, change table names, and edit the table definition. In the CHANNEL_TOTAL_ID column, you can see that the values are the same for each row. You can remove columns from the tables in the dataset that you don't need for your analyses.

  1. In the Join Diagram, click the CHANNELS tab.
  2. In the Transform Editor, click Measure Measure icon in the CHANNEL_CLASS_ID column, and then click Attribute.


    Description of channel_transform.png follows
    Description of the illustration channel_transform.png
  3. Click Edit Definition.
  4. In Add Dataset, under Selections, click CHANNEL_TOTAL_ID, click Remove Selected, and then click OK.
  5. Click the CUSTOMERS tab. Click Measure Measure icon in the CUST_YEAR_OF_BIRTH column, and then click Attribute.
  6. In the Join Diagram, right-click PROMOTIONS, and then select Edit Definition.
  7. In Table Name, enter CAMPAIGNS to replace PROMOTIONS, and then click OK.
  8. Click Join Diagram Join Diagram icon to see the table name change.


    Description of promo_to_campaigns.png follows
    Description of the illustration promo_to_campaigns.png
  9. Click Save. In Save Dataset As, enter Sales_History in Name, and then click OK.

Visualize the Sales_History Dataset

  1. Click Create Workbook.
  2. In the Data Data panel icon panel, expand the SALES and PRODUCTS folders.
  3. Hold down the Ctrl key, select QUANTITY_SOLD and AMOUNT_SOLD from SALES, and select PROD_SUBCATEGORY from PRODUCTS. Right-click, select Pick Visualization, and then select Pivot Pivot table icon.
  4. Click the Menu Menu icon, select Sort By, and then select Custom.
  5. In Sort Order, select AMOUNT_SOLD from the Sort by list, and then select High to Low from the list. Click OK.


    Description of sales_hist_pivot.png follows
    Description of the illustration sales_hist_pivot.png

Learn More