Set Up Database Objects

Importing metadata from a data source automatically creates a database object for the schema, but you may need to adjust or view the database properties.

The following sections provide information about how to create, edit, or view properties for database objects in the Physical layer:

About Database Types in the Physical Layer

If you import the physical schema into the Physical layer, the Model Administration Tool usually assigns database type automatically.

The following list contains additional information about automatic assignment of database types:

Relational data sources

During the import process, ODBC drivers provide the Oracle BI Server with the database type. If the server can't determine the database type, an approximate ODBC type is assigned to the database object. Replace the ODBC type with the closest matching entry from the Database list.
Multidimensional data sources

Microsoft Analysis Services are the only supported XMLA-compliant data sources currently available. After you import metadata from a multidimensional data source, check the database object and update the appropriate database type and version if necessary.

Create a Database Object Manually in the Physical Layer

When you create a database object manually, you must also manually set up an associated connection pool.

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 are in the same catalog (database) in the data source. A physical database can have more than one cube. The cubes must belong to the same catalog in the data source. To learn the properties to specify and their values when creating a database, see Database General Properties Reference.

Important:

Oracle strongly recommends importing your physical schema.

In the Model Administration Tool, in the Physical layer without any objects selected, right-click and select New Database.
In the Database dialog on the General tab, type a Name for the database.
In Data source definition, from the Database Type list, select Database as the value.
Optional: Select CRM metadata tables only for relational data sources and legacy Siebel Systems sources.
Optional: Select Virtual Private Database to identify the physical database source as a virtual private database (VPD).

Always select Virtual Private Database for Essbase, Hyperion Financial Management, and Hyperion Planning data sources that are configured for SSO in the corresponding connection pool.
Optional: Select Allow populate queries by default to give users the ability to populate the database.
Optional: Select Allow direct database requests by default to allow users to run queries.

Database General Properties Reference

Review the database properties in the table to learn which properties to configure and when you can or should specify values.

Option	Description
Data source definition: Database	The database type for your database.
CRM metadata tables	When selected, indicates that the definition of physical tables and columns for Siebel CRM tables was derived from the Siebel metadata dictionary.
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 user's authorization credentials. Therefore, it's important to identify these sources. These data results affect the validity of the query result set that's used with caching. Always select this option for Essbase, Hyperion Financial Management, and Hyperion Planning data sources that are configured for SSO in the corresponding connection pool. If you select this option, you also should select the Security Sensitive option in the Session Variable dialog.
Persist connection pool	To use a persistent connection pool, you must set up a temporary table first.
Allow populate queries by default	When selected, allows everyone to run `POPULATE SQL`. If you want most, but not all, users to be able to run `POPULATE SQL`, select this option and then limit queries for specific users or groups.
Allow direct database requests by default	When selected, allows all users to run physical queries. The Oracle BI Server sends unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set can be rendered in Oracle BI Server, and then charted, rendered in a dashboard, and treated as an Oracle Analytics Server request. If you want most, but not all, users to be able to run physical queries, select this option and then limit queries for specific users or groups.

When to Allow Direct Database Requests by Default

The property, Allow direct database requests by default, provides the ability for users to run 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 computer on which the Oracle BI Server, Oracle BI Presentation Services, or Oracle BI Scheduler are running. This restriction should be established at the TCP/IP level using the Oracle BI Server IP address. This allows only a TCP/IP connection from the IP address of Oracle BI Server.
To prevent users from running nqcmd, a utility that runs SQL scripts, by logging in remotely to this computer, you should disallow access by the following to the computer on which you installed 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 computers to perform tasks such as running nqcmd for cache seeding.
- Access the advanced SQL page of Answers to create requests.
Set up group/user-based permissions on Oracle BI Presentation Services to control access to editing, preconfigured to allow access by Oracle BI Presentation Services administrators, and processing, preconfigured to not allow access by anyone, direct database requests.

SQL Features Supported by a Data Source

When you import metadata or specify a database type in the General tab of the Database dialog, the set of SQL features for that database object is automatically populated with default values appropriate for the database type.

The Oracle BI Server uses the supported SQL features with the specified data source.

When a feature is marked as supported, checked in the Default column on the Features tab of the Database dialog, the Oracle BI Server pushes the function or calculation down to the data source for improved performance. When a function or feature isn't supported in the data source, the calculation or processing is performed in the Oracle BI Server.

The supported features list uses the defaults defined in the DBFeatures.defaults file, located in ORACLE_HOME/bi/bifoundation/server/bin. You shouldn't modify this file. You can review the DBFeatures.defaults file to compare the features supported by different data source types.

You can tailor the query features for a data source such as when upgrading to a new version of a data source to see if the updated feature is reflected in the Oracle BI Server defaults. When the supported feature isn't shown in the Features tab, you can update the settings in the Features tab to reflect the actual features supported by the new version of the data source. If a data source supports a particular feature such as left outer join queries but you want to prohibit the Oracle BI Server from sending such queries to a particular data source, you can change this default setting in the Features tab. If you've federated data sources that run functions differently, to ensure that query results are consistent, you can disable the appropriate functions on the Features tab so that the calculations are performed in a consistent manner in the Oracle BI Server.

Important:

If you enable SQL features that the data source doesn't support, your query may return errors and unexpected results. If you disable supported SQL features, the server could issue less efficient SQL to the data source.

In most cases, you should keep the default values. If you do change the defaults to mark a feature as supported in the Features tab, make sure that the feature is actually supported by the data source.

Note:

Don't change the OPTIMIZE_MDX_FILTER_QUALIFICATION value.

See Review Supported Database Features.

The table lists the options available on the Features tab of the Database dialog.

Option	Description
Feature	The name of the database feature, such as `COUNT_DISTINCT_SUPPORTED`.
Value	Shows the current value for the given feature. Selected indicates that the feature is supported in the data source, and that the function or feature should be performed in the data source rather than in the Oracle BI Server. Some features show a default value in the Value column rather than selected/not selected, such as 10 for `MAX_ENTRIES_PER_IN_LIST`. It's strongly recommended that you keep the default selections and default values.
Default	Shows the default value for the given feature. The defaults listed in this column are specified in the file `DBFeatures.defaults`.
Find	Searches for a feature in the list.
Find Again	This option becomes available after you click Find. It lets you perform multiple searches for the same string.
Query DBMS	Use Query DBMS only when you're installing and querying a data source that has no set of feature defaults in the Oracle BI Server. Query DBMS enables querying the type of data source for Feature table entries so that you can find out which SQL features are supported. You can then change the entries that appear in the Features tab based on your query results. Query DBMS isn't available if you're using an XML or a multidimensional data source. The Query DBMS feature results aren't always an accurate reflection of the SQL features actually supported by the data source. When using this feature, you should verify that the list of supported features in the Features tab matches the actual features supported by your data source. Refer to the documentation for your data source for details.
Reset to defaults	This button restores the default values for this data source type from the `DBFeatures.defaults` file.

View Database Properties

You can extend the Physical layer metadata for some data sources.

For example, for Oracle ADF data sources, you can view custom database properties that are passed to the Model Administration Tool from Oracle ADF BI view objects. These properties aren't usually edited.

The table shows examples of custom properties.

Category	Key Name	Value	Description
FscmTopModelAM.AccountBIAM	BIObject_FLEX_TREE_VS_COST_CENTER_LABEL_VI	Dim - Cost Center	FLEX_TREE_VS_COST_CENTER_LABEL_VI view object needs to map to the Dim - Cost Center logical dimension.
FscmTopModelAM.AccountBIAM	BIFlexfieldViewUsage	FLEX_BI_AcctKff_VI	FLEX_BI_AcctKff_VI is the CCID view object for FscmTopModelAM.AccountBIAM.
FscmTopModelAM.AccountBIAM	EnforceCustomDataType_FscmTopModelAM.AccountBIAM	"Segment 1":"VARCHAR"; "Segment ID":"DOUBLE"	For FscmTopModelAM.AccountBIAM view objects, the data type of some physical columns, the values are overridden with values passed in the property.

Review Supported Database Features

In the Model Administration Tool, you can review the features supported by databases and data sources. You can use Database Features when trying to troubleshoot a query or other operation that isn't working as expected.

Features are the SQL expressions, statements, function, operations, and other features that you can run against the database such as a query that uses an ISDESCENDANT statement, operations such as ADD or SQRT (square root) operations are supported. If a check displays in the Value or Default columns, the feature is supported. For specific information about the Value or Default columns, see SQL Features Supported by a Data Source.

Open the Model Administration Tool.
From the File menu, select Online Mode or Offline Mode.
In the Open Repository dialog, select a repository, and click Open.
In the Physical column, right-click a database or data source, and select Properties.
In Database Properties, click the Features tab to review the supported features for the specific database or data source.