Database Objects,

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.

General

Database

Database define the default features of the database.

Virtual Private Database

The virtual Private Database check box 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 is important to identify these sources. These data results affect the validity of the query result set that is used with caching.
Virtual Private Database specify that the data source is using row-level database security. If you are using row-level database security with shared caching, then you must select this option to prevent the sharing of cache entries whose security-sensitive variables do not match.

Persist Connection Pool

A persist connection pool is a database property that is used for specific types of queries (typically used to support Marketing queries).
In some queries, all of the logical query cannot be sent to the transactional database because that database might not support all of the functions in the query. This issue might be solved by:
  1. temporarily constructing a physical table in the database
  2. and rewriting the Oracle BI Server query to reference the new temporary physical table.

Allow populate queries by default

When selected, allows everyone to execute POPULATE SQL. (When a criteria block is cached, the Populate stored procedure writes the Cache/Saved Result Set value to the database).
If you want most, but not all, users to be able to execute POPULATE SQL, deselect this option and then limit queries for specific users or groups.

Allow direct database requests by default

When selected, allows all users to execute physical queries. See OBIEE 10G/11G - Direct Database Request

OBIEE - Database Features Parameters

About

When you create a database in the OBIEE physical layer some databases features parameters are automatically checked depending on the type of your database. You can find the type in the database section of the database object. This features are mostly parameters that control the generation of the physical query.
When a feature is marked as supported (checked) in the Features tab of the Database dialog, the Oracle BI Server typically pushes the function or calculation down to the data source for improved performance. When a function or feature is not supported in the data source, the calculation or processing is performed in the Oracle BI Server.
The supported features list in the Features tab uses the feature defaults defined in the DBFeatures.INI file.

Articles Related

Syntax

The syntax of the parameters is ”the_feature_SUPPORTED”
Example:
  • LEFT_OUTER_JOIN_SUPPORTED,
  • RIGHT_OUTER_JOIN_SUPPORTED,
  • …

Type of features

Database operations/functions

If the database is able to perform the database operations (Left outer join, Right outer join, …) or some functions (AVG, MIN, MAX, …) for the two first. If one feature is not supported (not checked), OBIEE will performed it and act then as a database engine.

Generation of the SQL issued

How to generate the Physical SQL issued:
  • WITH_CLAUSE_SUPPORTED,
  • PERF_PREFER_MINIMAL_WITH_USAGE,
  • PERF_PREFER_INTERNAL_STITCH_JOIN
Some features even enabled and pushed out to the underlying database may slow the query. For instance, ROWNUM is a supported feature of Teradata but significantly decrease the query performance.

Default values

The default value are defined in the file Oracle_Bi_Home\server\Config\DBFeatures.INI and you can change them:

OBIEE 10G/11G - Direct Database Request

About

The Direct Database Request permit you to perform SQL statement directly to a datasource. 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 Presentation Services, and then charted, rendered in a dashboard, and treated as an Oracle BI request.
Becareful, you can perform SELECT but also CREATE or even DELETE and DROP statement!
You bypass completely the OBI Server mechanisms such as :
You can use it to perform complex query such as using a table function.

Articles Related

How to create a Direct Database Access ?

Access to Direct Database Access

10G

Click on answer and you should see a link in the subject area. (If not see the security paragraph below)

11G

In the global header, select New, then Analysis, then Create Direct Database Request.

Creation

Enter :
  • the connection pool
  • the sql statement
You can find the name of the connection pool in the Oracle BI Repository in the physical layer.
You can use in the SQL statement a variable. Below, a example with a presentation service variable with a default value of 'Photo'.

Database Request in Dashboard

Add a prompt in the dashboard to update the presentation service variable and the Direct Database Request can be filtered.
And the download link works perfectly and gives you the filtered data (Tested on version 10.3.4.1)

Security, Privileges

OBI Presentation Service

You must grant your account with the privilege “Write Back to database” :
  1. Login in OBI Presentation Service Website
  2. Follow this links : Setting / Administration / Manage Privileges
  3. Grant the privilege Answers/“Execute Direct Database Requests” to the permitted group

Administration Tool

You must grant the direct access database to the user through the Administration Tool in the security part.
  1. Login in OBI Administration
  2. Go to the menu : Manage / Security
  3. Grant the privilege “Execute Direct Database Access” to the permitted group of user
write_back_execute_direct_database_request.jpg

Support

ORA-00911: invalid character at OCI call OCIStmtExecute

Be careful that you have to suppress the semicolon at the end of the statement.

[nQSError: 27022] Unresolved Connection Pool object: ..... (HY000)

Be sure to have the name of the database and the name of the connection pool framed by two doubles quotes.
Example:
database."Connection Pool"
is not good whereas
"database"."Connection Pool"
is good.

Documentation / Reference

Labels: