Working with Connection Pools in the Admin Tool

What is a Connection Pool ?
The connection pool is an object in the Physical layer that describes access to the data source. It contains information about the connection between the Oracle BI Server and that data source.
There is at least one connection pool for each database defined in the presentation layer of the Admin Tool. Depending on the need one can configure multiple connection pools to a database. 
Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database. The maximum number of concurrent connections to the database must be defined by consulting the DBA because increasing the allowed number of concurrent connections can potentially increase the load on the underlying database.

Connection Pool Properties in the General Tab...

 

Name:  Assign a name for the connection pool. A name is assigned automatically for connection pools created upon import.

Permissions: This option can be used to assign permissions for individual users or application roles to access the connection pool. This feature is not intended to be used for data access security.

Call interface: Identifies the application programming interface (API) with which to access the data source. Some databases can be accessed using native APIs, some use ODBC, and some work both ways.

Maximum connections: The maximum number of connections allowed for this connection pool. The default is 10. This value should be determined 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.

Require fully qualified table names: This option is to be selected if the database or database configuration

requires fully qualified table names. This option is not 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.

Data source name: The name of the data source to which you want this connection pool to connect and send physical queries.

Shared logon: Select this option if you want all users whose queries use the connection pool to access the underlying database using 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 is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.

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 do not 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.

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 DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when the Oracle BI Server terminates idle threads.

Parameters Supported:  When checked tells that that all the DB parameters mentioned in the DB features file are supported by the Oracle BI Server.

Isolation level: The isolation level setting controls the default transaction locking behavior for all statements issued by a connection. Only one option can be set at a time. It remains set for that connection until it is 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 is possible to read uncommitted or dirty data, change values in the data, and have rows appear or disappear in thedata set before the end of the transaction.                             Dirty data is data that needs to be cleaned before being queried 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. However, the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. 
• Repeatable read: Places locks on all data that is used in a query, preventing other users from updating the data. However, new phantom rows can be inserted 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.

 

Oracle BI Administration Tool - Setting Preferences

What is the Oracle BI Administration Tool?
The Oracle BI Administration Tool is a Windows application that you can use to create
and edit repositories.

Opening the Administration Tool
To open the Administration Tool, choose Start > Programs > Oracle Business
Intelligence > BI Administration.

Setting Preferences
You can use the Options dialog to set preferences for the Administration Tool.
To set preferences:
1. In the Administration Tool, select Tools, then select Options to display the
Options dialog.
2. On the General tab, select the options you want to choose.

Tile when resizing - Automatically tiles the layer panes of the repository when you re-size the Administration Tool. When this option is selected, the Cascade and Tile options are not available in the Windows menu of the Administration Tool.

Display qualified names in diagrams - Displays fully qualified names in the Physical Diagram and Business Model Diagram. For example, selecting this option displays "B - Sample Fcst Data"..."B02 Market" rather than B02 Market in the Physical Diagram.
Note: If you choose not to select this option, you can still see fully qualified names by moving the cursor over an object in the diagram, or by selecting an object in the diagram and then viewing the text in the status bar.

Display original names for alias in diagrams - Displays the names of original physical tables rather than the names of alias tables in the Physical diagram. Select this option when you want to identify the original table rather than the alias table name.

Show Calculation Wizard introduction page - Displays the Calculation Wizard introduction page. The introduction page also contains an option to suppress its display in the future.


Check out objects automatically - Automatically checks out an object when you double-click it. If you do not select this option, you are prompted to check out objects before you can edit them.

Show row count in physical view - Displays row counts for physical tables and columns in the Physical layer. Row counts are not initially displayed until they are updated. To update the counts, select Tools > Update All Row Counts. You can also right-click a table or column in the Physical layer and select the option Update Row Count.

Show toolbar - When selected, displays the Administration Tool toolbar.

Show statusbar - When selected, displays the Administration Tool status bar.

Prompt when moving logical columns - Lets you ignore, specify an existing, or create a new logical table source for a moved column.

Remove unused physical tables after Merge - Executes a utility to clean the repository of unused physical objects. It might make the resulting repository smaller.

Allow import from repository - When selected, the Import from Repository option on the File menu becomes available.

Allow logical foreign key join creation - When selected, provides the capability to create logical foreign
key joins with the Joins Manager. This option is provided for compatibility with previous releases and is generally not recommended

Skip Gen 1 levels in Essbase drag and drop actions - When selected, excludes Gen 1 levels when you drag and drop Essbase cubes or dimensions from the Physical layer to the Business Model and Mapping layer. Often, Gen 1 levels are not needed for analysis, so they can be excluded from the business model.

Hide unusable logical table sources in Replace wizard - By default, the Replace Wizard shows all logical table sources, even ones that are not valid for replacement. When this option is selected, unusable logical  table sources are hidden in the Replace Wizard screens. Click Info for details on why a logical table source that maps to that column does not appear in the list.
Selecting this option might result in the Wizard page loading more quickly, especially for large repositories.


3.On the Repository tab, you can set the following options:

Show tables and dimensions only under display folders - You can create display folders to organize objects in the Physical and Business Model and Mapping layers. They have no metadata meaning. After you create a display folder, the selected objects appear in the folder as a shortcut and in the database or business model tree as an object. You can hide the objects so that only the shortcuts appear in the display folder.

Hide level based measure - By default, each level of a dimension hierarchy in the Business Model and Mapping layer shows both dimension columns that are assigned to that level, and level-based measures that  have been fixed at that level. Level-based measures are objects that are not part of the dimension table, but that have been explicitly defined as being at a particular level. Hiding level-based measures in dimension hierarchies can reduce clutter. Note that the measures are still visible in the logical fact tables.

Default logging level - This option determines the default query logging level for all users. You can override this level for individual users in the User dialog. A query logging level of 0 (the default) means no logging.

LDAP -  If you are using any alternative LDAP servers, the Oracle BI Server maintains an authentication cache in memory for user identifiers and properties, which improves performance when using LDAP to  authenticate large numbers of users. Disabling the authentication cache can slow performance when hundreds of sessions are being authenticated. Note that the authentication cache is not used for Oracle WebLogic  Server’s embedded directory server.
  Properties for the authentication cache include: 

• Cache refresh interval. The interval at which the authentication cache entry for a logged on user is refreshed.

• Number of Cache Entries. The maximum number of entries in the authentication cache, preallocated when the Oracle BI Server starts. If the number of users exceeds this limit, cache entries are replaced using the LRU algorithm. If this value is 0, then the authentication cache is disabled. You need to specify some  additional LDAP properties when you are using a secure connection to your LDAP server. In other words, provide the following information when you have selected SSL on the Advanced tab of the LDAP Server dialog:  
• Key file name. The name of the key file that holds the client certificate and Certificate Authority (CA)  certificate. 
• Password and Confirm password. The password for the key file. Note that the authentication cache properties and key file properties are shared for all defined LDAP server objects.

4. On the Sort Objects tab, specify which repository objects appear in the Administration Tool in alphabetical order.

For example, if you want the database objects that appear in the Physical layer to appear in alphabetical order, select the Database option.

5. On the Cache Manager tab, select the columns you want to display in the Cache Manager. To change the  order of columns in the Cache Manager, select an item, then use the Up and Down buttons to change its position.

6. On the Multiuser tab, specify the path to the multiuser development directory and the name of the local developer for this Administration Tool

7. On the More tab, you can set the scrolling speed for Administration Tool dialogs,as well as set the default window size for the join diagrams.

■ To set the scrolling speed, position the cursor on the slider.
■ To set the default window size, in the Default diagram zoom list, choose a percentage or Best Fit.
The default window size is Best Fit. If you use the Best Fit option, the following rules apply:

• If there are five or fewer objects, the zoom level is 50%.
• If there are more than five objects, the zoom level changes automatically to Zoom to Fit.

8. Click OK when you are finished setting preferences.