1. Managing Metadata Repositories for Oracle Analytics Server
  2. Build Your Metadata Repository
  3. Work with Logical Tables, Joins, and Columns
  4. Create and Manage Logical Tables

Create and Manage Logical Tables

Logical tables exist in the Business Model and Mapping layer.

The logical schema defined in each business model must contain at least two logical tables, and you must define relationships between them.

Each logical table is associated with one or more logical columns and one or more logical table sources. You can add a new logical table source, edit or delete an existing table source, create or change mappings to the table source, or define when to use logical tables sources. See Create Logical Table Sources.

You can change the logical table name, reorder the logical table sources, and configure the logical keys, both primary and foreign

This section contains the following topics:

Create Logical Tables

Dragging and dropping physical tables from the Physical layer to the Business Model and Mapping layer is the recommended method for creating logical tables. If a table doesn't exist in your physical schema, you can create the logical table manually.

If you drag and drop physical tables from the Physical layer to the Business Model and Mapping layer, the columns in the table are also to the logical table along with key and foreign key relationships. Logical keys and joins are created that mirror the keys and joins in the Physical layer.

After creating a logical table using the menu option method, you must create all keys and joins manually.

After adding objects to the Business Model and Mapping layer, you can modify the objects in the logical table without affecting the objects in the Physical layer.

If you create new tables or drag additional tables from the Physical layer to the Business Model and Mapping layer, you must create the logical mappings between the new or newly dragged tables and the previously dragged tables.

See Define Logical Joins with the Joins Manager and Define Logical Joins with the Business Model Diagram.

A lookup table stores multilingual data corresponding to rows in the base tables. See Localize Oracle Analytics Server in Administering Oracle Analytics Server.
  1. In the Model Administration Tool, to create a logical table, do one of the following:
    • (Recommended method) Select one or more table objects in the Physical layer, then drag and drop the table objects to a business model in the Business Model and Mapping layer.
    • (Manual method) In the Business Model and Mapping layer, right-click the business model, select New Object , and then select Logical Table.
  2. For manually created tables, right-click the table, and select Properties.
  3. For manually created tables, in the Logical Table General tab, in Name, type a name for the logical table.
  4. Optional: Select Lookup table when you intend to use the table as a lookup table.
  5. Optional: In Description, type an explanation of the table’s use.
  6. Click OK.

Enable Data Driven Fragment Selection in Logical Table Sources

You can improve the performance of fragmented logical table sources by enabling the data driven fragment selection option in the

Data driven fragment selection is disabled by default.

  1. In the Model Administration Tool, from the Business Model and Mapping column, right-click a model that uses fragmented logical table sources and select Query Related Objects , and then select Logical Table Source.
  2. In Query Related Objects, select a logical table source, and click Edit.
  3. In Logical Table Sources, in the Content tab, click Enable Data Driven Fragment Selection, and click OK.

Specify a Primary Key in a Logical Table

After creating tables in the Business Model and Mapping layer, you specify a primary key for each dimension table.

Logical dimension tables must have a logical primary key. Logical keys can be composed of one or more logical columns.

Note:

Oracle recommends that you don't specify logical keys for logical fact tables.

  1. In the Business Model and Mapping layer of the Model Administration Tool, double-click a table.
  2. In the Logical Table dialog, select the Keys tab and then click New.
  3. In the Logical Key dialog, type a name for the key and select the column that defines the key of the logical table.
  4. Click OK.

Review Foreign Keys for a Logical Table

Oracle recommends that you don't use foreign key joins in logical tables.

You must enable the Allow logical foreign key join creation option in the Options dialog to create joins with foreign keys.

See Create Logical Foreign Key Joins with the Joins Manager.