Work with Physical Tables

About Tables in the Physical Layer

A physical table is an object in the Physical layer of the Oracle BI repository that corresponds to a table in a data source.

Metadata for physical tables is usually imported from the data source. This metadata enables the Oracle BI Server to access the data source tables with SQL requests.

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. The deletion fails if an alias exists on the physical table.

In addition to importing data source tables into the Physical layer, you can create virtual physical tables in the Physical layer, using values in the Table Type field in the Physical Table dialog. Creating virtual tables can provide the Oracle BI Server and the underlying data sources with the proper metadata to perform some advanced query requests.

You can store a virtual physical table as a stored procedure or a SELECT statement. A virtual physical table created from a SELECT statement is also called an opaque view. You can define an opaque view, and deploy it in the data source to create a deployed view, see Deploy Opaque Views.

Use the Table Type list in the General tab of the Physical Table dialog to specify the physical table object type. The following table describes the available object types.

Table Type Description

Table Type	Description
Physical Table	Specifies that the physical table object represents a data source table. This table type is used for both relational physical tables and multidimensional cube tables.
Stored Proc	Specifies that the physical table object is a stored procedure. When you select this option, you type the stored procedure in the text box. Requests for this table call the stored procedure. For stored procedures that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type doesn't have a corresponding database-specific string defined. Stored procedures within an Oracle Database might not return result sets. You can't initiate stored procedures from within Oracle Analytics Server. You need to rewrite the procedure as an Oracle function, use the Oracle function in a `SELECT` statement in the Model Administration Tool initialization block, and associate the Oracle function with the appropriate Oracle BI Server session variables in the Session Variables dialog. The following example shows a SQL initialization string using the `GET_ROLES` function that's associated with the `USER`, `GROUP`, and `DISPLAYNAME` variables. The function takes a user Id as a parameter and returns a semicolon-delimited list of group names: SELECT user_id, get_roles(user_id), first_name \|\| ' ' \|\| last_name FROM csx_security_table WHERE user_id = ':USER' and password = ':PASSWORD'
Select	Specifies that the physical table object is a `SELECT` statement. When you select this option, you type the `SELECT` statement in the text field, and you also need to manually create the table columns. The column names must match the ones specified in the `SELECT` statement. Column aliases are required for advanced SQL functions, such as aggregates and `CASE` statements. Requests for this table run the `SELECT` statement. For `SELECT` statements that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type doesn't have a corresponding database-specific string defined. If you're using Physical SQL to deploy an opaque view, then you must use the `VALUELISTOF` function. This type of table is also called an opaque view.

Physical Table

Specifies that the physical table object represents a data source table. This table type is used for both relational physical tables and multidimensional cube tables.

Stored Proc

Specifies that the physical table object is a stored procedure. When you select this option, you type the stored procedure in the text box. Requests for this table call the stored procedure.

For stored procedures that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type doesn't have a corresponding database-specific string defined.

Stored procedures within an Oracle Database might not return result sets. You can't initiate stored procedures from within Oracle Analytics Server. You need to rewrite the procedure as an Oracle function, use the Oracle function in a SELECT statement in the Model Administration Tool initialization block, and associate the Oracle function with the appropriate Oracle BI Server session variables in the Session Variables dialog.

The following example shows a SQL initialization string using the GET_ROLES function that's associated with the USER, GROUP, and DISPLAYNAME variables. The function takes a user Id as a parameter and returns a semicolon-delimited list of group names:

SELECT user_id, get_roles(user_id), first_name || ' ' || last_name
FROM csx_security_table
WHERE user_id = ':USER' and password = ':PASSWORD'

Select

Specifies that the physical table object is a SELECT statement. When you select this option, you type the SELECT statement in the text field, and you also need to manually create the table columns. The column names must match the ones specified in the SELECT statement. Column aliases are required for advanced SQL functions, such as aggregates and CASE statements.

Requests for this table run the SELECT statement.

For SELECT statements that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type doesn't have a corresponding database-specific string defined.

If you're using Physical SQL to deploy an opaque view, then you must use the VALUELISTOF function.

This type of table is also called an opaque view.

About Physical Alias Tables

An alias table (alias) is a physical table that references a different physical table as its source, called the original table.

Alias tables are an important part of designing a Physical layer because they enable you to reuse an existing table more than once, without having to import it several times.

There are two main reasons to create an alias table:

To set up multiple tables, each with different keys, names, or joins, when a single data source table needs to serve in different semantic roles. Setting up alias tables in this case is a way to avoid triangular or circular joins.

For example, an order date and a shipping date in a fact table may both point to the same column in the time dimension data source table, but alias the dimension table so that each role is presented as a separately labeled alias table with a single join. These separate roles carry over into the business model, so that Order Date and Ship Date are part of two different logical dimensions. If a single logical query contains both columns, the physical query uses aliases in the SQL statement so that it can include both of them.

You can also use aliases to enable a data source table to play the role of both a fact table, and a dimension table that joins to another fact table, often called a fan trap.
To include best practice naming conventions for physical table names. For example, you can prefix the alias table name with the table type such as fact, dimension, or bridge, and not change the original physical table names. Some organizations create alias tables for all physical tables to enforce best practice naming conventions. In this case, all mappings and joins are based on the alias tables rather than the original tables.

Alias table names appear in physical SQL queries. Using alias tables to provide meaningful table names can make SQL queries referencing those tables easier to read. For example:

WITH
SAWITH0 AS (select sum(T835.Dollars) as c1
from
     FactsRevT835/*AllRevenue(Billed Time Join)*/)
select distinct 0 as c1,
     D1.c1 as c2
from
     SAWITH0 D1
order by c1

In this query, the meaningful alias table name A11 Revenue (Billed Time Join) has been applied to the terse original physical table name FACTSREV. In this case, the alias table name provides information about which role the table was playing each time it appears in SQL queries.

Alias tables can have cache properties that differ from their original tables. To set different cache properties for an alias table, select the option Override Source Table Caching Properties in the Physical Table dialog for the alias table. In alias tables, you can't add, delete, or modify columns. Because columns are automatically synchronized, no manual intervention is required.

Synchronization ensures that the original tables and their related alias tables have the same column definitions. For example, if you delete a column in the original table, the column is automatically removed from the alias table.

You can't delete an original table unless you delete all its alias tables first. Alternatively, you can select the original table and all its alias tables and delete them at the same time.

You can change the original table of an alias table, if the new original table is a superset of the current original table. However, this could result in an inconsistent repository if changing the original 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 lets you cancel the action. Running a consistency check identifies orphaned aliases.

When you edit a physical table or column in online mode, you must check out all alias tables and columns. Alias tables and original tables never share columns. Aliases and original tables have distinctly different columns that alias each other. The behavior of online checkout uses the following conventions:

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

Alias tables inherit some properties from their original tables. A property that's proxied is a value that's always the same as the original table. The proxied properties are the ones that are dimmed in the alias table dialog. If the original table changes its value for that particular property, the same change is applied on the alias table.

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

Cacheable, the inherited property is overridden
Cache never expires and Cache persistence time, the inherited properties are overridden
Row Count
Last Updated
Table Type
External Db Specifications

The following is a list of the properties that aren't proxied:

Name
Description
Display Folder Containers
Foreign Keys
Columns
Table Keys
Complex Joins
Source Connection Pool
Polling Frequency
All XML attributes

Create and Manage Physical Tables and Physical Cube Tables

Use the General tab of the Physical Table dialog to create or edit physical tables and physical cube tables in the Physical layer of the Administration Tool.

This section contains the following topics:

Create Physical Tables

You can create or edit the general properties for a table, including both relational physical tables and physical cube tables.

In the properties for physical tables, you can view the name-value pairs used for data sources as a generic mechanism for extending the Physical layer metadata. These values are passed from the data source, but you can edit the values as needed. See View Physical Column Properties.

There are additional configuration settings that affect the behavior of the query cache. See Configure Query Caching.

Review Physical Table Properties before you create the physical table to understand the configuration options.

Important:

Oracle strongly recommends that you import cube tables, not create them manually.

In the Model Administration Tool, expand a database in the Physical layer, right-click the database schema, and select New Physical Table.
In the Physical Table dialog, specify a Name for the table.
From the Table Type list, select Physical Table.
Optional: Select Use Dynamic Name when using a non-multidimensional data source.
In Browse, select the value to use for the table.
Select Cacheable when the table isn't accessed in real time.
If you selected Cacheable, select the persistence time frame for the table.
Optional: In Hint, specify a value.
Optional: In Description, provide brief information about the table, and click OK.

Physical Table Properties

Review this table before creating physical tables, stored procedure tables, or selection tables.

Property	Description
Name	Indicates the physical table name.
Table Type	Indicates the physical table type. The valid values are Physical Table, Stored Proc (stored procedure), or Select.
Use Dynamic Name	Select Use Dynamic Name to use a session variable to specify the physical table name, similar to catalog and schema objects. This option is available for non-multidimensional data source tables when you select Physical Table. You can choose Use Dynamic Name if you've a multi-tenancy implementation, and you want to define a separate physical table name for each customer. Another example would be to select between primary and shadow tables that are valid at different times in the ETL cycle. In both cases, you can assign session variables to dynamically select the appropriate table.
Default Initialization String / Use database specific SQL	When creating a physical table for non-multidimensional data source tables (not alias tables), this option is available in the Physical Table dialog if you choose the Stored Proc or Select table types. For multidimensional data source tables, this option is available in the Physical Table dialog if you choose the Select table type. When you select Default Initialization String / Use database specific SQL option, you can specify the data source and type the SQL statements.
Cacheable	Select Cacheable to include the table in the Oracle BI Server query cache. Typically, select this option for tables that don't need to be accessed in real time. When you select this option, the Cache persistence time settings become active.
Cache never expires	When you select Cache never expires, cache entries don't automatically expire. This option is useful when a table is important to a large number of queries that users might run. For example, if most queries have a reference to an account object, keeping it cached indefinitely could actually improve performance rather than compromise it. Selecting Cache never expires doesn't mean that an entry always remains in the cache. Other invalidation techniques, such as manual purging, LRU (Least Recently Used) replacement, metadata changes, or use of the cache polling table can result in entries being removed from the cache.
Cache persistence time	Specifies the time period in which the table entries are persisted in the query cache. Setting a cache persistence time is useful for OLTP data sources and other data sources that are updated frequently. For example, you could set this option to refresh the underlying physical tables daily for a particular dashboard. If a query references multiple physical tables with different persistence times, the cache entry for the query exists 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. See Troubleshoot Problems with Event Polling Tables in Administering Oracle Analytics Server.
External name	Applies to physical cube tables from multidimensional data sources. The external name is the physical name that's used when referencing the cube table in physical SQL queries. This value must reflect the external name defined in the data source.
Display Column	For Essbase data sources only, see Work with Essbase Data Sources.
Hint	Available only for some data sources, see Use Hints in SQL Statements.

Create Alias Tables

You can also create aliases on opaque views and stored procedures.

The following table describes properties that are specific to alias tables. See Create Physical Tables.

Property	Description
Source Table	Applies to alias tables. Click Select to choose the original physical table from which to create an alias table.
Override Source Table Caching Properties	Applies to alias tables. Click this field to enable the cacheable properties. You can select or clear the appropriate cacheable options.

In the Model Administration Tool, with a repository open, right-click an existing physical table and select New Object, then select Alias to create an alias table.

Set Physical Table Properties for XML Data Sources

Use the XML tab to set or edit properties for an XML data source.

The XML tab of the Physical Table dialog provides the same functionality as the XML tab of the Connection Pool dialog. However, setting properties in the Physical Table dialog overrides the corresponding settings in the Connection Pool dialog. See Set Connection Pool Properties in the XML Tab.

Create and Manage Columns and Keys for Relational and Cube Tables

Each physical table and physical cube table in the Physical layer of the Model Administration Tool has one or more physical columns.

You can use the Columns, Keys, and Foreign Keys tabs in the Physical Table dialog to view, create new, and edit existing columns, keys, and foreign keys that are associated with the table.

This section contains the following topics:

Create and Edit a Column in a Physical Table

An imported column's properties are set automatically. After import, you can modify the column's property, including its type and whether null values are allowed for the column.

Creating, modifying, or deleting a column in an original physical table also creates, modifies, or deletes the same column on all its alias tables.

The following list contains information about nullable and data type values for columns imported into the Physical layer.

Nullable indicates whether null values are allowed for the column. If null values can exist in the underlying table, you need to select this option. This allows null values to be returned to the user, which is expected with certain functions and with outer joins. It's generally safe to change a non-nullable value to a nullable value in a physical column.
Type indicates the data type of the column. Use caution when changing the data type. Setting the values to data types that are incorrect in the underlying data source might cause unexpected results. If there are any data type mismatches, correct them in the repository or reimport the columns that have mismatched data types.

If you reimport columns, you also need to remap any logical column sources that reference the remapped columns. The data type of a logical column in the business model must match the data type of its physical column source. The Oracle BI Server passes these logical column data types to client applications.

Longvarchar and longvarbinary data types are supported for writing complete Logical SQL statements into usage tracking tables for debugging purposes. They aren't supported for general-purpose queries, and can't be displayed in Oracle BI Server. Use direct SQL utilities to access columns with these data types.

Except when stated otherwise, the characteristics and behavior of a physical cube column are the same as for other physical columns.

For XML data sources, this field stores and displays the unqualified name of a column (attribute) in an XML document.

A new physical cube column is created as a measure by default. See Work with Multidimensional Sources in the Physical Layer.

In the Model Administration Tool, in the Physical layer, right-click a physical table and select New Object, then select Physical Column to create a column.
Right-click a physical cube table, select New Object, and then select Physical Cube Column to create a physical cube column for a multidimensional data source.
Double-click the physical column object in the Physical layer to edit an existing physical column.
In the Physical Column dialog, type a name for the physical column.
In the Type field, select a data type for the physical column.
If applicable, specify the length of the data type.

When using multidimensional data sources, if you select VARCHAR, you must type a value in the Length field.
Select the Nullable option if the column is allowed to have null values.
In the External Name field, type an external name.
- Required if the same name such as STATE is used in multiple hierarchies.
- Optional for XML documents. The External Name field stores and displays the fully qualified name of a column (attribute).
In multidimensional data sources when the physical cube column is a measure, from the Aggregation role list, select the appropriate value.
Click OK.

Specify a Primary Key for a Physical Table

Use the Physical Key dialog to specify the column or columns that define the primary key of the physical table.

In the Physical layer of the Model Administration Tool, right-click a physical table and select Properties.
In the Physical Table dialog, click the Keys tab.
In the Keys tab, click New.
In the Physical Key dialog, type a name for the key.
Select the column that defines the primary key of the physical table.
Optional: Type a description for the key.
Click OK.

Delete Physical Columns for All Data Sources

Learn what happens when you delete a physical column.

When you delete a physical column, the following occurs:

Multidimensional data sources. If you delete property or key columns from a level, the association is deleted and the column changes to a measure under the parent cube table.
Alias tables. Deleting a column in an original physical table deletes the same column on all its alias tables.

View Physical Column Properties

The Properties tab for physical columns displays name-value pairs that are used for some data sources as a generic mechanism for extending the Physical layer metadata.

The values are passed up from the data source, but you can edit the values if needed.

View Data in Physical Tables or Columns

You can view the data in a physical table or an individual physical column by right-clicking the object and choosing View Data.

In online editing mode, you must check in changes before you can use this option.

View Data doesn't work in online mode if you set the user name and password for connection pools to :USER and :PASSWORD. In offline mode, the Set values for variables dialog appears so that you can populate :USER and :PASSWORD as part of the viewing process.

View Data isn't available for physical cube tables or columns. See View Members in Physical Cube Tables.

Because the View Data feature issues a row count, it isn't available for data sources that don't support row counts. See Display and Update Row Counts for Physical Tables and Columns.