Creating a Database Object Manually in the Physical Layer

If you create a database object manually, you need to manually set up all database elements such as connection pool, tables, and columns.

NOTE:  For multidimensional data sources, if you create the physical schema in the physical layer of the repository, you need to create one database in the physical layer for each cube, or set of cubes, that belong to the same catalog (database) in the data source. A physical database can have more than one cube. However, all of these cubes must be in the same catalog in the data source. You specify a catalog in the Connection Pool dialog box.

CAUTION:  It is strongly recommended that you import your physical schema.

To create a database object

1. In the Physical layer of the Administration Tool, right-click and choose New Database.
   Make sure that no object is selected when you right-click.
2. In the Database dialog box, in the General tab, complete the fields using Table 5 as a guide.

Table 5. Fields General Tab of the Database Dialog Box

Field	Description
Allow direct database requests by default (check box)	When checked, allows everyone to execute physical queries. The Oracle BI Server will send unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set can be rendered in Oracle BI Presentation Services, and then charted, rendered in a dashboard, and treated as an Oracle BI request. If you want most but not all users to be able to execute physical queries, check this option and then limit queries for specific users or groups. For more information about limiting queries, refer to Managing Query Execution Privileges. CAUTION:  If configured incorrectly, this can expose sensitive data to an unintended audience. For more information, refer to Recommendations for Allowing Direct Database Requests by Default. For more information about executing physical SQL, refer to Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide.
Allow populate queries by default (check box)	When checked, allows everyone to execute POPULATE SQL. If you want most but not all users to be able to execute POPULATE SQL, check this option and then limit queries for specific users or groups. For more information about limiting queries, refer to Managing Query Execution Privileges.
Data Source Definition CRM Metadata Tables	Not available for multidimensional data sources. When selected, the import utility will look for the table definition in Oracle's Siebel CRM specific tables. For Siebel CRM tables, the import reads the Siebel metadata dictionary to define the definition of physical tables and columns (it does not read the database data dictionary). This is for legacy Siebel Systems sources only.
Data Source Definition Database	The database type for your database. For more information about using the Features tab to examine the SQL features supported by the specified database type, refer to Specifying SQL Features Supported by a Database.
Data Source Definition Virtual Private Database	Identifies the physical database source as a virtual private database (VPD). When a VPD is used, returned data results are contingent on the users authorization credentials. Therefore, it is important to identify these sources. These data results affect the validity of the query result set that will be used with Caching. For more information, see Query Caching in the Oracle BI Server. NOTE:  If you select this check box, you also should select the Security Sensitive check box in the Session Variable dialog box. For more information, see Creating Session Variables.
Persist Connection Pool	To use a persistent connection pool, you must set up a temporary table first. For more information, refer to Setting Up the Persist Connection Pool Property.

Recommendations for Allowing Direct Database Requests by Default

This property allows all users to execute physical queries. If configured incorrectly, it can expose sensitive data to an unintended audience. Use the following recommended guidelines when setting this database property:

• The Oracle BI Server should be configured to accept connection requests only from a machine on which the Oracle BI Server, Oracle BI Presentation Services, or Oracle BI Scheduler Server are running. This restriction should be established at the TCP/IP level using the Oracle BI Presentation Services IP address. This will allow only a TCP/IP connection from the IP Address of the Oracle BI Presentation Services.
• To prevent users from running nQCmd.exe (a file that executes SQL script) by logging in remotely to this machine, you should disallow access by the following to the machine on which you installed the Oracle BI Presentation Services:
  • TELNET
  • Remote shells
  • Remote desktops
  • Teleconferencing software (such as Windows NetMeeting)
    If necessary, you might want to make an exception for users with Administrator permissions.
• Only users with Administrator permissions should be allowed to perform the following tasks:
  • TELNET into the Oracle BI Server and Oracle BI Presentation Services machines to perform tasks such as running nQCmd.exe for cache seeding. For more information, see Executing the SQL Script File to Create and Delete Aggregates.
• Access to the advanced SQL page of Answers to create reports. For more information, refer to Oracle Business Intelligence Presentation Services Administration Guide.
• Setup group/user-based permissions on the Oracle BI Presentation Services to control access to editing (preconfigured to allow access by the Oracle BI Presentation Services Administrators) and executing (preconfigured to not allow access by anyone) direct database requests. For more information, refer to Oracle Business Intelligence Presentation Services Administration Guide.