Creating and Administering General Properties for Physical Tables

Creating and Administering General Properties for Physical Tables


Use the General tab of the Physical Table dialog box to create or edit a physical table in the Physical layer of the Administration Tool.
This section contains the following topics:

Creating or Editing Physical Tables

This section describes how to create or edit the general properties for a table. This includes physical cube tables and alias tables.
To create a physical table or edit general properties for tables and alias tables
  1. In the Physical layer of the Administration Tool, perform one of the following steps:
    • To create a physical table, right-click the physical database and choose New Object > Physical Table.
    • To create a physical cube table for a multidimensional data source, right-click the physical database and choose New Object > Physical Cube Table.
      NOTE:  It is strongly recommended that you import cube tables, not create them manually.
    • To create an alias table, right click a physical table, and choose New Object > Alias.
      NOTE:  You can also create aliases on opaque views and stored procedures.
    • To edit an existing physical table, double-click the physical table icon.
  2. In the selected Physical Table dialog box, complete the fields using Table 13 as a guide.
Table 13. Physical Table General Properties for Relational and XML Data Sources
Property
Description
Name
The Oracle BI Administrator assigns a name to new table.
Cacheable
To include the table in the Oracle BI Server query cache, select this check box. When you select this check box, the Cache persistence time settings become active. This is useful for OLTP data sources and other data sources that are updated frequently. Typically, you should check this option for most tables.
Cache never expires
When you select this option, cache entries do not expire. This could be useful when a table will be important to a large number of queries users might run. For example, if most of your queries have a reference to an account object, keeping it cached indefinitely could actually improve performance rather than compromise it.
CAUTION:  This is only of use on some objects. Set this option for too many objects and the cache will become manageably large or objects might begin dropping out of the cache at inefficient times.
Cache persistence time
How long table entries should persist in the query cache. The default value is Infinite, meaning that cache entries do not automatically expire. However, this does not mean that an entry will always remain in the cache. Other invalidation techniques, such as manual purging, LRU (Least Recently Used) replacement, metadata changes, and use of the cache polling table, result in entries being removed from the cache.
If a query references multiple physical tables with different persistence times, the cache entry for the query will exist for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry.
If you change the default to minutes or seconds, type a whole number into the field on the left.
External name
Applies to physical cube tables from a multidimensional data source. If you select a Table Type of Physical Table, the external data source name appears.
Override Source Table Caching Properties
Check box available for alias tables. When selected, the cacheable properties become available and you can clear or select the appropriate options.
Source Table
Applies to alias tables. The Select button allows you to choose the physical table from which to create an alias table.
Table Type
Physical Table values: Physical Table, Stored Proc (stored procedure), or Select.
Physical Cube Table values: Physical Table or Select.
Use Dynamic Name
Check box available for non-multidimensional data source tables when you select a Table type of Physical Table. When selected, a dialog box opens in which you can choose a session variable.
Use Database Specific SQL
Default Initialization String
For non-multidimensional data source tables (not alias tables), this appears if you choose a Table Type of Stored Proc or Select. For multidimensional data source tables, this appears if you choose a Table Type of Select.
When you select the check box, you can specify the database and type the SQL.

Deleting a Physical Table

When you delete a physical table, all dependent objects are deleted. For example columns, keys, and foreign keys. When you delete a physical cube table, hierarchies are also deleted.
NOTE:  The deletion fails if an alias exists on the physical table.
To delete a physical table from the Physical layer
  1. In the Physical layer of the Administration Tool, locate the table that you want to delete.
  2. Right-click the table and choose Delete.