Create or Change Connection Pools
If you didn't import physical schemas, you must create a database object before you create a connection pool.
Database objects and connection pools are created automatically when you import physical schemas, for both relational and multidimensional data sources.
You create or change a connection pool in the Physical layer of the Model Administration Tool.
To modify more than one connection pool, use the List Connection Pool Command and the Update Connection Pool Command.
If you've already defined an existing database and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata to import metadata for this data source. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled. See Import Metadata and Working with Data Sources.
To automate connection pool changes for use in a process such as production migration, consider using the XML API. See About the Oracle BI Server XML API in Managing Security for Oracle Analytics Server.
Note: Oracle Analytics doesn’t support the variables :user and :password in data source connection credentials.
- In the Physical layer of the Model Administration Tool, right-click a database, select New Object, and then select Connection Pool.
- Specify or adjust the properties as needed, then click OK.
Set Connection Pool Properties in the General Tab
You can learn about the properties in the General tab of the Connection Pool dialog.
The properties listed in the General tab vary according to the data source type. For example, XMLA data sources have a connection pool property for URL, while relational and XML data sources have the option Require fully qualified table names.
- In the Connection Pool dialog, click the General tab, and then complete the fields.
Common Connection Pool Properties in the General Tab
The topic describes connection pool properties in the General tab that are common among most data source types.
The table describes the properties in the General tab of the Connection Pool dialog that are common for different data source types.
| Property | Description |
|---|---|
|
Name |
The name for the connection pool. The name is assigned automatically for connection pools created on import. |
|
Permissions |
Use this option to assign permissions for individual users or application roles to access the connection pool. For example, you can set up a privileged group of users to have its own connection pool. These permissions aren't intended for use as data access security. For example, connection pool permissions don't protect cache entries. |
|
Call interface |
Identifies the application programming interface (API) to access the data source. You can access some databases using native APIs, using ODBC, or with APIs and ODBC together. Java data sources are accessed using JDBC/JNDI. If the call interface is XML, the XML tab is displayed for you to update the applicable properties. |
|
Maximum connections |
The maximum number of connections allowed for this connection pool. The default is 10. You can determined the value by the database make and model and the configuration of the hardware for the computer on which the database runs, as well as the number of concurrent users who require access. For Microsoft Analysis Services data sources, you might encounter 503 Service Not Available errors if the Max Connections setting in the connection pool (default 10) is greater than the See Improve Use of System Memory Resources with TimesTen Data Sources. For deployments with Oracle Analytics Interactive Dashboards pages, consider estimating this value at 10% to 20% of the number of simultaneous users multiplied by the number of requests on a dashboard. You can adjust the number based on usage. Define the total number of all connections in the repository to less than 800. To estimate the maximum connections needed for a connection pool dedicated to an initialization block, you might use the number of users concurrently logged on during initialization block processing. |
|
Require fully qualified table names |
Select this option if the database or database configuration requires fully qualified table names. This option isn't available for some data source types. When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database. The fully qualified names are based on the physical object names in the repository. If you're querying the same tables from which the Physical layer metadata was imported, you can safely select this option. If you've migrated your repository from one physical database to another physical database that has different database and schema names, the fully qualified names are invalid in the newly migrated database. In this case, if you don't select this option, the queries succeed against the new database objects. For some data sources, fully qualified names are a safer because they guarantee that the queries are directed to the desired tables in the desired database. For example, if the RDBMS supports a master database concept, a query against a table named Customer first looks for that table in the master database, and then looks for it in the specified database. If the table named Customer exists in the master database, that table is queried, not the table named Customer in the specified database. It's sometimes necessary to select this option when you're using an Oracle Database, and you're accessing the database with a user that isn't the owner of the schema containing the tables. When the Oracle Database interprets table names in SQL, it assumes that the user that made the query is the owner if the table name isn't fully qualified in the query. This can result in an incorrect qualified name. For example, if the user SAMPLE creates a table called CUSTOMER, the fully qualified table name is SAMPLE.CUSTOMER. When the SAMPLE user references the CUSTOMER table in a query, the Oracle Database assumes the fully qualified table name is SAMPLE.CUSTOMER, and the access is successful. However, if the JANEDOE user references the CUSTOMER table in a query, the Oracle Database assumes the fully qualified table name is JANEDOE.CUSTOMER, and a Table or view not found error can result. To enable access for JANEDOE, you must select Require fully qualified table names in the connection pool so that the Oracle BI Server specifies SAMPLE.CUSTOMER in all queries. |
|
Data source name |
The name of the data source to which you want this connection pool to connect and send physical queries. The value you enter in this field depends on the selected call interface:
|
|
Shared logon |
When selected, all users whose queries use the connection pool to access the underlying database use the same user name and password. If this option is selected, then all connections to the database that use the connection pool use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN or in user configuration. If this option isn't selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile. When you use Oracle Call Interface (OCI) to connect to the database, you can deselect the Shared logon property of a connection pool for the Oracle database. OCI gets the credentials of the user in the repository. The Shared logon option is enabled by default in Essbase connection pools. You can't disable Shared logon for Essbase connection pools.. |
|
Enable connection pooling |
When selected, allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query. If you don't select this option, each query sent to the database opens a new connection. |
|
Timeout |
Specify the amount of time and in what increment such as minutes that a connection to the data source remains open after a request completes. During this time, new requests use this connection rather than open a new one, up to the number specified for the maximum connections. The time is reset after each completed connection request. If you're using an ADF data source and the call interface is |
|
Use multithreaded connections |
When this option is selected, the Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection, number of threads = maximum connections. Even if threads are idle, they consume memory. The parameter |
|
Parameters supported |
If this option isn't selected, and the database features table supports parameters, special code runs that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database. |
|
Isolation level |
For ODBC gateways only. The value sets the transaction isolation level on each connection to the back-end database. The isolation level setting controls the default transaction locking behavior for all statements issued by a connection. You can only set one at a time. It remains set for that connection until it's explicitly changed. The following options are available: Dirty read. Implements dirty read, isolation level 0 locking. This is the least restrictive isolation level. When this option is set, it's possible to read uncommitted or dirty data, change values in the data, and have rows appear or disappear in the data set before the end of the transaction. Dirty data is data to clean before running a query to obtain correct results, for example, duplicate records, records with inconsistent naming conventions, or records with incompatible data types. Committed read. Specifies that shared locks are held while the data is read to avoid dirty reads. You can change the data before the end of the transaction, resulting in non-repeatable reads or phantom data. Repeatable read. Places locks on all data that's used in a query, preventing other users from updating the data. You can insert new phantom rows into the data set by another user and are included in later reads in the current transaction. Serializable. Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only if necessary. |
Multidimensional Connection Pool Properties in the General Tab
Learn how to use the connection pool properties.
The table describes the properties in the General tab of the Connection Pool dialog that are specific to multidimensional data sources. Some properties only appear for certain types of multidimensional data sources.
-
URL
This property is only displayed for XMLA data sources. Specify the URL to connect to the XMLA provider. This URL points to the XMLA virtual directory of the computer hosting the cube. This virtual directory must be associated with
msxisapi.dll, part of the Microsoft XML for Analysis SDK installation. For example, the URL might look like the following:http://SDCDL360i101/xmla/msxisap.dll -
Essbase Server
This property is only displayed for Essbase data sources. Specify the host name of the computer where the Essbase Server is running.
You can import metadata from an Essbase cluster, but you must still specify an individual Essbase Server host name and port number in the Essbase Server field.
If the Essbase Server is running on a non-default port, or if it's part of an Essbase Cluster, you must include the port number in the Essbase Server field, in the format
hostname:port. -
SSO
This property is only displayed for Essbase, Hyperion Financial Management, and Hyperion Planning data sources.
For Essbase, select this option if you want Essbase to be able to enforce security policies that provide different cube access or member-level access to different users. If you select this option then you must also select the Shared logon option.
Don't select this option if all users are expected to have the same access to the Essbase cube. In this case, all the users have the same access to the cube based on the shared credentials specified in the connection pool. If you don't select this option then you must also select the Shared logon option.
For Hyperion Financial Management or Hyperion Planning, select this option and be sure that the Shared logon option is unchecked to authenticate against Hyperion Financial Management or Hyperion Planning using a shared token, rather than using a set of shared credentials in the connection pool.
If you select this option, you should also select Virtual Private Database in the corresponding database object to protect cache entries.
For Essbase, Hyperion Financial Management, and Hyperion Planning data sources installed with the EPM System Installer, preconfiguration is required before you select this option. See Configure SSO for Essbase, Hyperion Financial Management, or Hyperion Planning Data Sources.
-
Shared logon
This property is only displayed for Essbase, Hyperion Financial Management, and Hyperion Planning data sources.
For all Essbase data sources, it's required that you select this option. See Configure Essbase to Use a Shared Logon.
For Hyperion Financial Management or Hyperion Planning, you set this option based on how you set the SSO property.
-
If you checked the SSO property, then don't check this option. Not checking this option causes authentication against Hyperion Financial Management or Hyperion Planning using a shared token, rather than using a set of shared credentials in the connection pool.
-
If you didn't check the SSO property, then check this option to enable the Oracle BI Server to use the same shared logon credentials to connect to the data source for all Oracle BI users. All users share the same access to the data source.
-
-
Data Source
Specify the vendor-specific information used to connect to the multidimensional data source. Consult your multidimensional data source administrator for setup instructions because specifications can change. For example, if you use v 1.0 of the XML for Analysis SDK, then use the value
Provider-MSOLAP;Data Source-local. If you use v 1.1, use the value,Local Analysis Server. -
Catalog
Specify the list of catalogs available, if you imported data from your data source. The cube tables correspond to the catalog you use in the connection pool.
-
Use session
This property is only displayed for XMLA data sources. An option that controls whether queries go through a common session. Consult your multidimensional data source administrator to determine whether this option is enabled. Default is Off, not selected.
Set Connection Pool Properties in the Connection Scripts Tab
You can create connection scripts and set the scripts to run before the connection is established, before a query is run, after a query is run, or after the connection is disconnected.
For example, you can create a connection script that, on connect, inserts the name of the user and the connection time into a table.
This topic describes the properties in the Connection Scripts tab of the Connection Pool dialog. The Connection Scripts tab is available for ODBC, OCI, Oracle OLAP, and ADF data sources.
Connection scripts can contain any commands accepted by the database, such as a command to turn on quoted identifiers. This enables mainframe environments to maintain security in one central location.
Because the connection script is sent directly to the data source, the script should use native SQL or another language understood by the data source, not Oracle BI Server Logical SQL.
-
In the Connection Pool dialog, click the Connection Scripts tab, and then complete the fields using the information in the following table.
To enter a new connection script, click New next to the appropriate script type. Then, enter or paste the SQL statements for the script and click OK.
You can edit existing scripts by clicking the ellipsis button to launch the Physical SQL window. Use the Up Arrow and Down Arrow buttons to reorder existing scripts.
Click Delete to remove a script.
The table describes the properties in the Connection Scripts tab of the Connection Pool dialog.
| Property | Description |
|---|---|
|
Execute on connect |
Contains SQL queries that are run before the connection is established. |
|
Execute before query |
Contains SQL queries that are run before the query is run. |
|
Execute after query |
Contains SQL queries that are run after the query is run. |
|
Execute on disconnect |
Contains SQL queries that are run after the connection is closed. |
Set Connection Pool Properties in the XML Tab
Use the Connection Pool Properties in the XML tab to set properties for XML data sources.
The XML tab in the Connection Pool dialog provides the same functionality as the XML tab of the Physical Table dialog. The properties in the XML tab of the Physical Table dialog override the corresponding settings in the Connection Pool dialog.
-
In the Connection Pool dialog, click the XML tab, and then complete the fields using the information in the table that follows.
The table describes the properties in the XML tab of the Connection Pool dialog.
| Property | Description |
|---|---|
|
Connection method: Search script |
This property is only displayed for XML Server data sources. Click Browse to select the search script file you want to use to locate the XML Server data source. |
|
Connection properties: URL refresh interval |
This property is used for XML data sources and isn't available for XML Server data sources. The refresh interval is analogous to setting cache persistence for database tables. The URL refresh interval is the time interval after which the XML data source is queried again directly rather than using results in cache. The default setting is infinite, meaning the XML data source is never refreshed. If you specified a URL to access the data source, set the URL refresh interval.
|
|
Connection properties: URL loading time-out |
The timeout interval for queries. The default is 15 minutes. If you specified a URL to access the data source, set the URL loading time-out as follows:
|
|
Connection properties: Maximum connections |
The maximum number of connections. The default is 10. |
|
Query input supplements: Header file/Trailer file |
This property is only displayed for XML Server data sources. Click Browse to locate the header and trailer files. |
|
Query output format |
For XML data sources, choose only XML. Other output formats are available for XML Server data sources. |
Search Script Example
Use this search script example to create a search script for XML data source.
Example 8-1 Search Script
<ConnectionPool name="Connection Pool" parentName=""Stock Quotes"" parentId="3023:3037" parentUid="80000557-0bcf-0000-714b-e31d00000000" id="3029:3046" uid="8000055b-0bd5-0000-714b-e31d00000000" password="E3130008E1C4CAD47041E4AE68B048E6 7C2E35213306F12832914CBE7A9DD95561D771DED06484112B1FC6F27B6D0D58" timeout="4294967295" maxConnDiff="10" maxConn="32" dataSource="http://www.host.net/stockquote.asmx" type="Default" reqQualifedTableName="false" isSharedLogin="true" isConcurrentQueriesInConnection="false" isCloseAfterEveryRequest="true" xmlRefreshInterval="4294967295" scriptPath="java.exe -Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80 -classpath \analytics\server\Query\Execution\DbGateway\DbGatewayXML\Test\stocktick_webservices -jar \analytics\server\Query\Execution\DbGateway\DbGatewayXML\Test\stocktick_webservices\XMLServiceAdapter.jar" outputType="xml" gwDelim="," ignoreFirstLine="false" bulkInsertBufferSize="0" transactionBoundary="0" xmlaUseSession="false" xmlHeaderPath="C:\orahome_2015\biclient\oraclebi\orainst\config\OracleBIServerComponent\coreapplication\NQSQueryHeader.xml" trailerPath="C:\orahome_2015\biclient\oraclebi\orainst\config\OracleBIServerComponent\coreapplication\NQSQueryTrailer.xml" supportParams="false" isSiebelJDBSecured="false">
</ConnectionPool>
Set Connection Pool Properties in the Write Back Tab
Use the Write Back tab to set write back properties for ODBC, OCI, Oracle OLAP, and ADF data sources.
-
In the Connection Pool dialog, click the Write Back tab, and then complete the fields using the information in the table.
See About Setting the Buffer Size and Transaction Boundary.
The table describes the properties in the Write Back tab of the Connection Pool dialog.
| Property | Description |
|---|---|
|
Temporary table: Prefix |
When the Oracle BI Server creates a temporary table, these are the first two characters in the temporary table name. The default value is |
|
Temporary table: Owner |
Table owner name used to qualify a temporary table name in a SQL statement, for example to create the table |
|
Temporary table: Database name |
Database where the temporary table is created. This property applies only to IBM OS/390 because IBM OS/390 requires database name qualifier as part of the |
|
Temporary table: Tablespace name |
Tablespace where the temporary table is created. This property applies to OS/390 only as OS/390 requires tablespace name qualifier as part of the |
|
Bulk insert: Buffer size (KB) |
Used for limiting the number of bytes each time data is inserted in a database table. For optimum performance, consider setting this parameter to 128. |
|
Bulk insert: Transaction boundary |
Controls the batch size for an insert in a database table. For optimum performance, consider setting this parameter to 1000. |
|
Unicode database type |
Select this option when working with columns of an explicit Unicode data type, such as
Unicode and non-Unicode data types can't coexist in a single non-Unicode database. For example, mixing the |
Connection Pool Properties in the Miscellaneous Tab
Use the Miscellaneous tab of the Connection Pool dialog to set application properties for ADF, JDBC, and JNDI data sources.
To set application properties, see Specify Application Properties for JDBC (Direct Driver) or JDBC (JNDI) Data Sources.
The table describes the properties in the Miscellaneous tab of the Connection Pool dialog.
| Property | Description |
|---|---|
|
AppModule Definition |
The fully qualified Java package name of the Root Application Module to which you want to connect, such as |
|
AppModule Config |
Determines which application configuration is used in the connection, such as |
|
URL |
The URL to the Oracle Analytics Server broker servlet, in the format: http://host:port/APP_DEPLOYMENT_NAME/obieebroker For example: http://localhost:7001/SnowflakeSalesApp/obieebroker The URL is case-sensitive. |
|
SQL Bypass Database |
(Optional) The name of the SQL Bypass database. The SQL Bypass database must be a physical database in the Physical layer of the repository. The database object for the SQL Bypass database must have a valid connection pool, with connection information that points to the same database that's being used by the JDBC Data source defined in the Oracle WebLogic Server. The SQL Bypass database doesn't need to have any tables under it. After a valid database name is supplied, the SQL Bypass feature is enabled for all queries. The SQL Bypass feature directly queries the database so that aggregations and other transformations are pushed down where possible, reducing the amount of data streamed and worked on in Oracle Analytics Server. See About Specifying a SQL Bypass Database. |
EXECUTE PHYSICAL DATABASE
Use EXECUTE PHYSICAL DATABASE statement to send physical SQL to the Oracle BI Server to connect to data sources.
The EXECUTE PHYSICAL DATABASE statement enables processing physical queries from the client without knowing the connection pool information.
Syntax
EXECUTE PHYSICAL DATABASE DatabaseName/*add a valid SQL statement