Working with Physical Tables

Learn about the different things you can do with physical table objects in the Physical layer of the Oracle BI repository.

Both physical tables from relational data sources and physical cube tables from multidimensional data sources use the table type, Physical Table.

Many of the tasks described in this section apply to relational and multidimensional data sources. See Working with Multidimensional Sources in the Physical Layer.

This section contains the following topics:

• About Tables in the Physical Layer

• About Physical Alias Tables

• Creating and Managing Physical Tables and Physical Cube Tables

• Creating and Managing Columns and Keys for Relational and Cube Tables

• Viewing Data in Physical Tables or Columns

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.

A virtual physical table can be 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 Deploying 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
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 will 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 does not have a corresponding database-specific string defined. Stored procedures within an Oracle Database might not return result sets. You cannot initiate stored procedures from within Oracle Business Intelligence. You need to rewrite the procedure as an Oracle function, use the Oracle function in a SELECT statement in the 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 is 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 execute 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 does not have a corresponding database-specific string defined. If you are 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 can be 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, columns cannot be added, deleted, or modified. 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 cannot 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, all alias tables and columns must be checked out. 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 is proxied is a value that is always the same as the original table, and cannot be changed. 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 can be overridden

• Cache never expires and Cache persistence time, the inherited properties can be overridden

• Row Count

• Last Updated

• Table Type

• External Db Specifications

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

• Name

• Description

• Display Folder Containers

• Foreign Keys

• Columns

  Note:

  Alias tables and original tables never share columns. Aliases and original tables have distinctly different columns that alias each other.

• Table Keys

• Complex Joins

• Source Connection Pool

• Polling Frequency

• All XML attributes

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

• Creating or Editing Physical Tables

• Creating Alias Tables

• Setting Physical Table Properties for XML Data Sources

Creating or Editing Physical Tables

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

The Properties tab for physical tables 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 from the data source, but you can edit the values if needed. See Viewing Physical Column Properties.

1. In the Physical layer of the Administration Tool, do one of the following steps:
   • Create a physical table, right-click the physical database or physical catalog and select New Object, then select Physical Table.
   • If the database object has physical schemas defined, right-click the physical schema and select New Physical Table.
   • Create a physical cube table for a multidimensional data source, right-click the physical database and select New Object, then select Cube Table.

   Caution:

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

2. In the Physical Table dialog, complete the fields using the table as a guide.

Property	Description
Name	The name of the physical 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	Select this option 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 a table type of Physical Table. You might want to choose this option if you have 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	For non-multidimensional data source tables (not alias tables), this option 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 this option, you can specify the data source and type the SQL statements.
Cacheable	Select this option to include the table in the Oracle BI Server query cache. Typically, select this option for tables that do not need to be accessed in real time. When you select this option, the Cache persistence time settings become active. Note that there are additional configuration settings that affect the behavior of the query cache. See Configuring Query Caching.
Cache never expires	When you select this option, cache entries do not automatically expire. This could be 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. Note that selecting this option does not 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	How long table entries should persist in the query cache, or in other words, the cache expiration time. 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 Troubleshooting Problems with Event Polling Tables in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
External name	Applies to physical cube tables from multidimensional data sources. The external name is the physical name that is 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 Working with Essbase Data Sources.
Hint	Available only for some data sources, see Using Hints in SQL Statements.

Creating 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 Creating or Editing 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 Oracle BI Administration Tool, with a repository open, right-click an existing physical table and select New Object, then select Alias to create an alias table.

Setting 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 Setting Connection Pool Properties in the XML Tab.

Creating and Managing Columns and Keys for Relational and Cube Tables

Each physical table and physical cube table in the Physical layer of the Oracle BI 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.

The following list describes the buttons that appear in the tabs:

• New. Lets you create a new object by opening the dialog that corresponds to the tab.

• Edit. When you select an object and click Edit, the dialog that corresponds to the tab appears. You can then edit the properties of the object.

• Delete. Deletes the selected object.

This section contains the following topics:

• Creating and Editing a Column in a Physical Table

• Specifying a Primary Key for a Physical Table

• Deleting Physical Columns for All Data Sources

• Viewing Physical Column Properties

Creating and Editing 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.

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 is 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 are not supported for general-purpose queries, and cannot 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.

Note:

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

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 Working with Multidimensional Sources in the Physical Layer.

1. In the Administration Tool, in the Physical layer, right-click a physical table and select New Object, then select Physical Column to create a column.
2. 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.
3. Double-click the physical column object in the Physical layer to edit an existing physical column.
4. In the Physical Column dialog, type a name for the physical column.
5. In the Type field, select a data type for the physical column.
6. 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.

7. Select the Nullable option if the column is allowed to have null values.
8. 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).

9. In multidimensional data sources when the physical cube column is a measure, from the Aggregation role list, select the appropriate value.
10. Click OK.

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

1. In the Physical layer of the Administration Tool, right-click a physical table and select Properties.
2. In the Physical Table dialog, click the Keys tab.
3. In the Keys tab, click New.
4. In the Physical Key dialog, type a name for the key.
5. Select the column that defines the primary key of the physical table.
6. (Optional) Type a description for the key.
7. Click OK.

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

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

Viewing 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 is not available for physical cube tables or columns. See Viewing Members in Physical Cube Tables.

Because the View Data feature issues a row count, it is not available for data sources that do not support row counts. See Displaying and Updating Row Counts for Physical Tables and Columns.

Note:

View Data does not 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.