Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘Physical Layer Objects’ Category

Physical Layer – Alias Physical Table

Posted by Santosh Kumar Gidadmani on February 17, 2011

An alias physical table type specifies that the physical table object is an alias to another table.
Uses of Alias Table in physical layer:
1. Single dimension table simultaneously appears several times in the same fact table.
2. To configure the self join in Physical Layer
3. Re structure and organize the physical tables
4. To separate two date columns used in the same table and requires join for some calculation.
5. It’s easier to give the access of objects to users in case of Multi User Development Environment.

Alias Synchronization
An alias table always inherits all of the column definitions from the source table and synchronization happens automatically. Consequently, columns in an alias table cannot be modified. All columns opened from an alias table become read-only. If you add or delete any columns in the source table, the same changes will automatically happen in alias table.

How to create an Alias table:
Right click on any table and select New Object and click on Alias, provide the name of the table and click Ok. The alias table appears with a green arrow alias icon in the Physical layer.

– Santosh

Posted in Alias Table, OBIEE 10g, OBIEE 10g RPD, Physical Layer Objects | Leave a Comment »

Physical Layer – Physical Table Properties

Posted by Santosh Kumar Gidadmani on February 17, 2011

A physical table is an object in the Physical layer of the Administration Tool that corresponds to a table in a physical database. They provide the metadata necessary for Oracle BI Server to access the tables with SQL requests. When data source definitions are imported, no actual data is moved. Data remains stored in the physical data source.

Physical Table Properties

Physical Column Property

1. Table Name: By default, the name corresponds to the table name defined during import, but you can rename it.
2. The Table Type: You can define physical table (default), store procedure and select statement. When you select either (Store procedure or select) options, a text pane below the Table Type drop-down list becomes active, allowing you to enter the stored procedure or the select statement.
3. Cacheable: Cacheable is selected by default (that means, If you enable cache in the NQconfig.INI under cache parameter, all the tables will default cache entries). You can select cache persistence time to purge cache entries.
4. Hints: Hints are instructions placed within a SQL statement that tell the database query optimizer the most efficient way to execute the statement. Hints override the optimizer’s execution plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan.
5. Columns: A physical column is an object in the Physical layer of the Administration Tool that corresponds to a column in a physical database.
6. Keys: It’s a primary key which uniquely identifies a single row of data, it consists of a column or set of columns and is identified by a key icon.
7. Foreign Key: A foreign key is a column or a set of columns in one table that references the primary key columns in another table.

– Santosh

Posted in OBIEE 10g, OBIEE 10g RPD, Physical Layer Objects | Leave a Comment »

Physical Layer – Connection Pool

Posted by Santosh Kumar Gidadmani on February 15, 2011

The connection pool contains information about the connection between Oracle BI Server and a data source. For each data source, there is at least one corresponding connection pool.

Components of Connection Pool
1. Connection Pool name: Name of the connection pool.
2. Call Interface: The call interface represents the driver which you use to connect to the data source. There are three kinds ODBC, OCI, XML.
3. Maximum Connections: Mention the maximum connections based on the concurrent users in the organization. Once this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool if available or, if no other connection pools exist, the connection request waits until a connection becomes available. Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Ensure the right connections are provided.
4. Require fully qualified table names: When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.
5. Data source name (DSN): Name of the DSN
6. Share Logon: If this option is checked, then all connections to the database that uses the connection pool will use the user name and password specified in the connection pool. 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 Siebel user profile.
7. Enable Connection Pooling: It allows multiple concurrent query requests to share a single database connection. This reduces the overhead of connecting to a database because it does not open and close a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.
8. Use multithreaded connections: When the check box is selected, Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection. Even if threads are idle, they consume memory.
9. Execute queries synchronously: This provides the way to communicate with Oracle Database, if this option is checked, it is run asynchronously. If this option is not checked, the query is run synchronously. It is always uncheck by default.
10. Parameters supported: If this option is checked, that means all the DB parameters mentioned in the DB features are supported by server. By default, this option will be checked.
11. Isolation level: This option controls the transaction locking behavior for all statements issued by a connection. There are four kinds
1. Committed Read- Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.
2. Dirty Read: Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
3. Repeatable Read: Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.
4. Serialization: Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

– Santosh

Posted in Connection Pools, OBIEE 10g, OBIEE 10g RPD, Physical Layer Objects | Leave a Comment »

%d bloggers like this: