Logical Tables

Logical tables exist in the Business Model and Mapping layer. The logical schema defined in each business model needs to contain at least two logical tables and you need to define relationships between them.

Each logical table has one or more logical columns and one or more logical table sources associated with it. You can change the logical table name, reorder the logical table sources, and configure the logical keys (primary and foreign).

Creating Logical Tables

---

Typically, you create logical tables by dragging and dropping a physical table from the Physical layer to a business model in the Business Model and Mapping layer. If a table does not exist in your physical schema, you would need to create the logical table manually.

Drag and drop operations are usually the fastest method for creating objects in the Business Model and Mapping layer. If you drag and drop physical tables from the Physical layer to the Business Model and Mapping layer, the columns belonging to the table are also copied. After you drag and drop objects into the Business Model and Mapping layer, you can modify them in any way necessary without affecting the objects in the Physical layer.

When you drag physical tables (with key and foreign key relationships defined) to a business model, logical keys and joins are created that mirror the keys and joins in the physical layer. This occurs only if the tables that you drag include the table with the foreign keys. Additionally, if you create new tables or subsequently drag additional tables from the Physical layer to the Business Model and Mapping layer, the logical links between the new or newly dragged tables and the previously dragged tables must be created manually.

For more information about joins, refer to Defining Logical Joins with the Joins Manager and Defining Logical Joins with the Business Model Diagram.

To create a logical table by dragging and dropping

1. Select one or more table objects in the Physical layer.
   You must include the table with the foreign keys if you want to preserve the keys and joins from the physical layer.
2. Drag and drop the table objects to a business model in the Business Model and Mapping layer.
   When you drop them, the table objects, including the physical source mappings, are created automatically in the Business Model and Mapping layer.

To create a logical table manually

1. In the Business Model and Mapping layer, right-click the business model in which you want to create the table and select New Object > Logical Table.
   The Logical Table dialog box appears.
2. In the General tab, type a name for the logical table.
3. If this is a bridge table, select the option Bridge table.
   For more information, refer to Identifying Dimension Hierarchies.
4. (Optional) Type a description of the table.
5. Click OK.

Create Logical Tables

To create a new logical table, perform the following steps:

1.	In the Business Model and Mapping layer, right-click the SH business model and select New Object > Logical Table.
2.	In the Logical Table dialog box, name the table Sales Facts.
3.	Click OK to close the Logical Table dialog. The Sales Facts logical table appears in the SH business model in the Business Model and Mapping layer. The Sources folder is populated in the next step when you create a logical column.

NOTE:  After creating a logical table manually, you must create all keys and joins manually.

Adding or Editing 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, and define when to use logical tables sources and how content is aggregated. For instructions about how to perform these tasks, refer to Creating and Administering Logical Table Sources (Mappings).

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 table. Logical dimension tables must have a logical primary key. Logical keys can be composed of one or more logical columns.

NOTE:  Logical keys are optional for logical fact tables. However, it is recommended that you do not specify logical keys for logical fact tables. For more information, refer to Reviewing Foreign Keys for a Logical Table.

To specify a primary key in a logical table

1. In the Business Model and Mapping layer, double-click a table.
2. In the Logical Table dialog box, select the Keys tab and then click New.
3. In the Logical Key dialog box, perform the following steps:
   1. Type a name for the key.
   2. Select the check box for the column that defines the key of the logical table.
4. Click OK.

4. Reviewing Foreign Keys for a Logical Table

   ---

   You can use the Foreign Keys tab to review the foreign keys for a logical table.
   In fact tables, it is recommended that you use complex logical joins instead of foreign key logical joins. If complex logical joins are used, then there is more flexibility in defining the primary key. If the physical table has a primary key, then this field can be used as a logical key for the fact table. This is the method recommended for the Oracle BI repository.
   CAUTION:  It is recommended that you do not have foreign keys for logical tables. However, you can create logical foreign keys and logical complex joins using either the Joins Manager or the Business Model Diagram. A logical key for a fact table must be made up of the key columns that join to the attribute tables. For more information, refer to Defining Logical Joins.
   To review foreign key information for a logical table
   1. In the Business Model and Mapping layer, double-click a table.
   2. In the Logical Table dialog box, select the Foreign Keys tab.
   3. To review an existing foreign key, in the Foreign Keys list, select a key and click Edit.
      The Logical Foreign Key dialog box appears. For more information about changing information in this dialog box, refer to Defining Logical Joins.