Bookshelf v7.8.2: About Physical Tables

A physical table is an object in the Physical layer of the Administration Tool that corresponds to a table in a physical database. Physical tables are usually imported from a database or another data source, and they provide the metadata necessary for the Analytics Server to access the tables with SQL requests.

In addition to importing physical tables, you can create virtual physical tables in the Physical layer, using the Object Type option in the Physical Table dialog box. A virtual physical table can be an alias, a stored procedure, or a Select statement. Virtual physical tables have several uses. You can use them to create the equivalent of a database view; that is, the virtual table generated by some query expression. You can use them to behave like a synonym; that is, a table identical to another table but with a different name. You can also use them to define a portion of the domain of a group of fragmented aggregate tables, as described in Define a Physical Layer Table with a Select Statement to Complete the Domain. Creating virtual tables can provide the Analytics Server and the underlying databases with the proper metadata to perform some advanced query requests.

Object Types for Physical Tables

The Object Type drop-down list in the General tab of the Physical Table dialog box allows you to specify the physical table object type. Table 15 provides a description of the available object types.

Table 15. Object Type Descriptions for Physical Tables
Object Type	Description
None	Specifies that the physical table object represents a physical table. If you select None, you can type a database hint. For more information, see Using Database Hints.
Alias	Specifies that the physical table object is an alias to another table. When you select this option, the text pane to the right of the Object Type drop-down list becomes active, allowing you to type the alias name. The alias you type must be a valid physical table in the database specified in the connection pool. If you select Alias, you can type a database hint. For more information, see Using Database Hints. If the database features table supports the COMMENT_START and COMMENT_END properties, the Analytics Server will include the alias name as a comment in the physical SQL it generates when it uses the alias as a data source.
Stored Proc	Specifies that the physical table object is a stored procedure. When you select this option, the text pane to the right of the Object Type drop-down list becomes active, allowing you to type the stored procedure. Requests for this table will call the stored procedure. For stored procedures that are database specific, you can select the database type from the drop-down list above the text pane. At run time, if a stored procedure has been defined for the corresponding database's database type, then the stored procedure will be executed; otherwise, the default configuration will be executed. NOTE: Stored procedures using an Oracle database do not return result sets. For more information, see Using Stored Procedures with an Oracle Database.
Select	Specifies that the physical table object is a Select statement. When you select this option, the text pane to the right of the Object Type drop-down list becomes active, allowing you to type the select statement. Requests for this table will execute the Select statement. When you select this option, you 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. For Select statements that are database specific, you can select the database type from the drop-down list above the text pane. At run time, if a Select statement has been defined for the corresponding database's database type, then the Select statement will be executed; otherwise, the default configuration will be executed.