Creating and Managing 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 Creating 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:

Creating 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 does not 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 Defining Logical Joins with the Joins Manager and Defining Logical Joins with the Business Model Diagram.

A lookup table stores multilingual data corresponding to rows in the base tables. See Localizing Business Intelligence in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
  1. In the 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 selectLogical 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 a explanation of the table’s use.
  6. Click OK.

Enabling 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 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.

Specifying 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 do not specify logical keys for logical fact tables.

  1. In the Business Model and Mapping layer of the 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.

Reviewing Foreign Keys for a Logical Table

Oracle recommends that you do not use foreign key joins in logical tables.

If you must create these joins, you must first enable the Allow logical foreign key join creation option in the Options dialog.

See Creating Logical Foreign Key Joins with the Joins Manager.

The Foreign Keys tab of the Logical Table dialog exists so that you can view logical foreign keys you might have had in a previous release of Oracle Business Intelligence.