Before You Begin

This tutorial shows you how to define dataset filters to limit data for all users of the dataset in Oracle Analytics. When you create a dataset filter, Oracle Analytics applies the filter when the data is retrieved such as when a workbook is created using the dataset.

This tutorial shows you how to create a filter on a dataset that contains multiple tables.

Background

You can create dataset filters for single table and multiple table datasets.

What Do You Need?

  • Access to Oracle Analytics
  • Ability to connect a relational data source such as Oracle Autonomous AI Lakehouse or Oracle AI 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 from an Oracle AI Database.

  1. Sign in to Oracle Analytics. On your home page, click Create Dataset.
  2. In Create Dataset, click your Oracle AI Database connection with the SH schema.
  3. In the Connections Connections pane pane, expand Schemas, and then expand the SH schema.
  4. Under the SH schema, select the SALES table and drag it to the Join diagram.
  5. In the SH schema, hold down the Ctrl key, select CHANNELS, CUSTOMERS, and PRODUCTS, and then drag them to the Join Diagram.

    Oracle Analytics automatically creates the joins between the tables.

  6. In the Join Diagram, right-click SALES and select Preserve Grain.


    Description of multi_table_dataset.png follows
    Description of the illustration multi_table_dataset.png
  7. Click Save Save icon. In Save Dataset As, enter a Name and click OK.

Create a Dataset Filter

In this section, you create an expression dataset filter that uses columns from three tables.

  1. In the Join Diagram, click Dataset Filter Dataset filter icon to show the dataset filter bar.
  2. Click Add Filter Add filter icon.
  3. In Expression Filter, enter Electronics Sales in Label.
  4. In Description, enter Electronics sales over $100 by men.
  5. In the expression field, enter PROD_ and select PROD_CATEGORY. Click Show available functions Functions icon.
  6. In Available functions, expand Operators and double-click the equals symbol (=). To complete the expression:
    • Enter 'Electronics'
    • Enter AND
    • Enter AMOUNT_ and select AMOUNT_SOLD
    • In Available functions, double-click the greater than symbol (>), and then enter 100
    • Enter AND, and enter CUST_, select CUST_GENDER
    • In Available functions, double-click the equals symbol (=), and then enter 'M'
  7. Click Validate.


    Description of dataset_filter_exp.png follows
    Description of the illustration dataset_filter_exp.png
  8. Click Apply. Click Save Save icon.

Test the Filter

In this section, you create a workbook and add a visualization to test the dataset filter.

  1. Click Create Workbook.
  2. In the Data Data pane pane, expand SALES, PRODUCTS, and CUSTOMERS, then hold down the Ctrl key, and select PROD_CATEGORY, CUST_GENDER, and AMOUNT_SOLD.
  3. Right-click, select Pick Visualization, and then select Table table visualization icon.


    Description of table_vis.png follows
    Description of the illustration table_vis.png

Learn More