Oracle^® Business Intelligence Server Administration Guide > Creating and Administering the Physical Layer in an Oracle BI Repository >

Creating and Setting Up Physical Tables

This topic is part of the Process of Creating the Physical Layer from Relational Data Sources and the Process of Creating the Physical Layer from Multidimensional Data Sources.

For all data sources, you can define general properties, columns, a primary key, and foreign keys.

About Physical Alias Tables

An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a logical table source, and inherits all its column definitions and some properties from the logical table source. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these logical table source types. For more information, see Creating and Administering the Business Model and Mapping Layer in an Oracle BI Repository.

Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table:

• To reuse an existing table more than once in your physical layer (without having to import it several times.
• To set up multiple alias tables, each with different keys, names, or joins.
• To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas. For more information, see Identifying the Database Content For The Business Model.

You can allow an alias table to have cache properties that differ from its source table by setting an override flag in the Physical Table dialog box. In alias tables, columns cannot be added, deleted, or modified. Columns are automatically synchronized; no manual intervention is required.

NOTE:  Synchronization makes sure that source tables and their related alias tables have the same column definitions. For example, if you delete a column in the source table the column is automatically removed from the alias table.

You cannot delete source tables unless you delete all its alias tables first. You can change the source table of an alias table, if the new source table is a superset of the current source table. However, this could result in an inconsistent repository if changing the source table deletes columns that are being used. If you attempt to do this, a warning message appears to let you know that this could cause a problem and allows you to cancel the action.

NOTE:  Running a consistency check identifies orphaned aliases.

When you edit a physical table or column in online mode, all alias tables and columns must be checked out. The behavior of online checkout uses the following conventions:

• If a source table or column is checked out, all its alias tables and columns will be checked out.
• If an alias table or column is checked out, its source table and column will be checked out.
• The checkout option is available for online repositories (if not read-only) and for all source and alias tables and columns.

Alias tables inherit some properties from their source tables. A property that is proxied is a value that is always the same as the source table, and cannot be changed. If the source table changes its value for that particular property, the same will be applied on the alias table.

The following is a list of the properties that are proxied:

• IsCacheable (the inherited property can be overridden)
• CacheExpiry (the inherited property can be overridden)
• Row Count
• Last Updated
• Table Type
• External Db Specifications

The following is list of the properties that are not proxied:

• Name
• Description
• Display Folder Containers
• Foreign Keys
• Columns (tables don't share columns, ever. Aliases and sources have distinctly different columns that alias each other)
• Table Keys
• Complex Joins
• Source Connection Pool
• Polling Frequency
• All XML attributes

About Creating and Setting Up Physical Tables for Multidimensional Data Sources

Each cube from a multidimensional data source is set up as a physical cube table, a type of physical table. It has all the capabilities of a table such as physical cube columns and keys (optional) and foreign keys (optional). It also has cube-specific metadata such as hierarchies and levels.

In the Physical layer, a physical cube table looks like a regular table but will have a different icon. For more information about icons, refer to Icons and Symbols in the Administration Tool.

When you import the physical schema, the Oracle BI Server imports the cube, including its metrics, hierarchies, and levels. Expanding the hierarchy icon reveals the levels in the hierarchy. In the Physical Cube Table dialog box, the Hierarchies tab lists the dimensional hierarchies in the cube.

Each multidimensional catalog in the database can contain multiple physical cubes. You can import one or more of these cubes into your BI repository. You can create a cube table manually. However, it is recommended that you import cube tables and their components.

NOTE:  If creating a cube manually, it is strongly recommended that you build each cube one hierarchy at a time and test each one before building another. For example, create the time hierarchy and a measure, and then test it. When it is correct, create the geography hierarchy and test it. This will help make sure you have set up each cube correctly and make it easier to identify any setup errors.

To create a physical table or a physical cube table and any necessary properties, perform the following tasks:

• Creating and Administering General Properties for Physical Tables
• Creating and Administering Columns and Keys in a Physical Table
• Setting Up Hierarchies in the Physical Layer for a Multidimensional Data Source
• Setting Physical Table Properties for an XML Data Source