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:
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 The following example shows a SQL initialization string using the 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 Requests for this table execute the For If you are using Physical SQL to deploy an opaque view, then you must use the This type of table is also called an opaque view. |
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
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:
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.
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. |
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. |
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.
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:
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.
Use the Physical Key dialog to specify the column or columns that define the primary key of the physical table.
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.
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.