OBIEE 11G FAQs

1.1          Physical Layer

1.What is connection pool

a.    Connection pool is a physical layer object ,is useful to store data base credentials like database name, username, password..etc.

b . OBIEE oracle BI Server contacts data base using connection pool 

2.How to import views/materialized views?

While importing we need to enable views 

3.What is the isolation level in a connection pool

a.  It applies only For ODBC and DB2 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. Only one option can be set at a time. It remains set for that connection until it is explicitly changed. 

b.    The following options are available:

                                i.    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 the data 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).

                                         ii.    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.

                                        iii.    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.

                                       iv.    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.

2.How many connection Pools you used in your project

a.    It is a general question ,there is no fixed answer, however in each project we will have minimum three connection pools

                                          i.    One to import and execute reports queries

                                         ii.    One to execute session initialization block sql queries

                                        iii.    One for usage tracking purpose

b.    Recommended number is between 2 to 10.but justification is important.

3.To get data of another schema ,then what we need to enable in connection pool

a.    Required fully qualified names

b.    DB permissions

4.Can we create multiple connection pools under one database object

a.    Yes, we can

5.What is the difference between Duplicate and Alias in physical layer

a.    We will use Alias in physical layer to resolve below problems

                                          i.    Self Join

                                         ii.    To maintain naming Standards in physical layer

                                        iii.    Resolve loops or closed paths

b.    Duplicate normally used in BMM layer to create a Xerox copy of existing logical objects like Business model or logical table or logical column

6.How many types of Joins are there in Physical layer?

               A: - Two types

                                          i.    Foreign Key join

1. Equivalent to Equi join

2. Accepts only = operator

3. Most used join in physical layer

                                        ii.    Complex Join

1. Equivalent to Non Equi join

2. Accepts all operators like (=,<>,=>,,=…….etc)

3. Less used join in Physical layer

7.Can we create self join

a.    By using Alias table , we can create self join.

8.Can we create outer join in physical layer.

a.    No, We can create using BMM layer logical join

9.How to remove unused objects from physical layer

a.    It will remove objects, which we did not use in BMM layer

b.    ToolsàUtilitiesà Remove unused objects from physical layer

10.How to synchronize physical layer objects

a.    It is useful to synchronize RPD metadata with DB metadata

b.    ToolsàUtilitiesàUpdate Physical Layer

11.What is complex join, in which scenario we use it.

a.    If foreign key is not possible then we will go for complex join. For example between emp and salgrade we will use complex join

b.    In place of non equi join ,complex join is used

12.In foreign key join can we use other than equal to operator

a.    No

13.What is Opaque view

a.    A SQL query or select statement is called as opaque view.

b.    If we need a new table then go for physical table (or) materialized view. In worst situation go for opaque view.

c.    Opaque views are not supported by non relational db.(example MS Excel ,XML ,ESSBASE …etc)

14.Is deploying opaque is mandatory?

a.    No. but it is recommended due to below reason

b.    Without deploying view into database, if we use opaque view in reports then oracle BI server needs to create complex queries. To avoid complex queries oracle recommended to deploy each and every opaque view into database.

15.When automatic joins will be created in physical layer.

a.    In DB level foreign key relationship

b.    While you are importing you should enable foreign key options.

16.What is Alias

a.    A virtual physical table (or) reference of physical table is called as alias.

b.    On one physical table we can create ‘n’ no. of aliases.

c.    Whatever changes occurred in physical table that will immediately reflect in alias (except Key definitions)

17.Can we create or delete a column from Alias.

a.    No

18.How to eliminate Circular Join or loop or closed path.

a.    By Using Alias