Bookshelf Home | Contents | Index | PDF     

Oracle® Business Intelligence Server Administration Guide > Creating and Administering the Physical Layer in an Oracle BI Repository >

About Physical Tables

This topic is part of the Process of Creating the Physical Layer from Relational Data Sources and the Process of Creating the Physical Layer from Multidimensional Data Sources.

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. They provide the metadata necessary for the Oracle BI 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 values in the Table Type field in the Physical Table dialog box. A virtual physical table can be a stored procedure or a Select statement. Creating virtual tables can provide the Oracle BI Server and the underlying databases with the proper metadata to perform some advanced query requests.

Table Types for Physical Tables

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

Table 12. Table Type Descriptions for Physical Tables
Table Type
Description

Physical Table

Specifies that the physical table object represents a physical table.

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 database specific, select the Use database specific SQL check box. At run time, if a stored procedure has been defined, 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, refer to Using Stored Procedures with an Oracle Database.

For information about stored procedures and alias tables, see About Physical Alias Tables.

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 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 will execute the Select statement.

For Select statements that are database specific, select the Use database specific SQL check box. At run time, if a Select statement has been defined, the Select statement will be executed; otherwise, the default configuration will be executed.

Using Stored Procedures with an Oracle Database

Stored Procedures within Oracle do not return result sets. Therefore they cannot be initiated from within Oracle BI. You need to rewrite the procedure as an Oracle function, use it in a select statement in the Administration Tool initialization block, and then associate it with the appropriate Oracle BI session variables in the Session Variables dialog box.

The function uses the GET_ROLES function and takes a user Id as a parameter and returns a semi-colon delimited list of group names.

The following is an example of an initialization SQL string using the GET_ROLES function that is associated with the USER, GROUP, DISPLAYNAME variables:

select user_id, get_roles(user_id), first_name || ' ' || last_name

from csx_security_table

where user_id = ':USER' and password = ':PASSWORD'

  
Oracle® Business Intelligence Server Administration Guide Copyright © 2007, Oracle. All rights reserved.