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.
- In the Physical layer of the Administration Tool, right-click and choose New Database.
- In the Database dialog box, in the General tab, complete the fields using Table 5 as a guide.
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. | |
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. | |
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. | |
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. | |
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. | |
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:- temporarily constructing a physical table in the database
- 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 RequestOBIEE - 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:- by database in its feature tab
- by choosing an other database source definition
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 :- the Security
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” :- Login in OBI Presentation Service Website
- Follow this links : Setting / Administration / Manage Privileges
- 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.- Login in OBI Administration
- Go to the menu : Manage / Security
- Grant the privilege “Execute Direct Database Access” to the permitted group of user
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
Database define the default features of the 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.
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:
- temporarily constructing a physical table in the database
- and rewriting the Oracle BI Server query to reference the new temporary physical table.
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.
When selected, allows all users to execute physical queries. See OBIEE 10G/11G - Direct Database Request
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.
The syntax of the parameters is ”the_feature_SUPPORTED”
Example:
- LEFT_OUTER_JOIN_SUPPORTED,
- RIGHT_OUTER_JOIN_SUPPORTED,
- …
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.
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.
The default value are defined in the file Oracle_Bi_Home\server\Config\DBFeatures.INI and you can change them:
- by database in its feature tab
- by choosing an other database source definition
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 :
- the Security
You can use it to perform complex query such as using a table function.
Click on answer and you should see a link in the subject area. (If not see the security paragraph below)
In the global header, select New, then Analysis, then Create Direct Database Request.
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'.
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)
You must grant your account with the privilege “Write Back to database” :
- Login in OBI Presentation Service Website
- Follow this links : Setting / Administration / Manage Privileges
- Grant the privilege Answers/“Execute Direct Database Requests” to the permitted group
You must grant the direct access database to the user through the Administration Tool in the security part.
- Login in OBI Administration
- Go to the menu : Manage / Security
- Grant the privilege “Execute Direct Database Access” to the permitted group of user
Be careful that you have to suppress the semicolon at the end of the statement.
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.
Posts
