OBIEE INTERVIEW QUESTIONS

Handson01: Physical Layer 

1. What is connection pool
1.  Connection pool is a physical layer object ,is useful to store data base credentials like database name, username, password..etc.
2.  OBIEE oracle BI Server contacts data base using connection pool
2. How to import views/materialized views?
1.  While importing we need to enable views option
3. What is the isolation level in a connection pool
1. 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.
2. The following options are available:
1. 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).
2. 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.
3. 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.
4. 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.
4. How many connection Pools you used in your project
1. It is a general question ,there is no fixed answer, however in each project we will have minimum three connection pools
1. One for importing tables  and execute reports
2. One for execute session initialization block sql queries
3. One for usage tracking purpose
2. Recommended number is between 2 to 10.but justification is important.
5. To get data of another schema ,then what we need to enable in connection pool
1. Required fully qualified names
2. DB permissions
   
   
6. Can we create multiple connection pools under one database object
1.  Yes, we can
   
   
7. What is the difference between Duplicate and Alias in physical layer
1.  We will use Alias in physical layer to resolve below problems
1. Self Join
2. To maintain naming Standards in physical layer
3. Resolve loops or closed paths
2. Duplicate normally used in Physical layer  to create a duplicate copy of existing Select table (Opaque View)  /stored Procedure object
8. How many types of Joins are there in Physical layer?
1. Two types
1.  Foreign Key join
1. Equivalent to Equi join
2. Accepts only = operator
3. Most used join in physical layer
2.  Complex Join
1. Equivalent to Non Equi join
2. Accepts all operators like (=,<>,=>,,=…….etc)
3. Less used join in Physical layer
9. Can we create self join
1. By using Alias table , we can create self join.
10. Can we create outer join in physical layer.
1. In Physical Layer Outer join not possible ,however we can create outer join in BMM layer .
11. How to remove unused objects from physical layer
1.  It will remove objects, which we did not use in BMM layer
2. Tools menu | Utilities | Remove unused objects from physical layer
12. How to synchronize physical layer objects
1. It is useful to synchronize RPD metadata with DB metadata
2. Tools | Utilities | Update Physical Layer
13. What is complex join, in which scenario we use it.
1.  If foreign key is not possible then we will go for complex join. For example between emp and salgrade we will use complex join
2. In place of non equi join ,complex join is used
14. In foreign key join can we use other than equal to operator
1. No
15. What is Opaque view
1. A SQL query or select statement is called as opaque view.
2. If we need a new table then go for physical table (or) materialized view. In worst situation go for opaque view.
3. Opaque views are not supported by non relational db.(example MS Excel ,XML ,ESSBASE …etc)
4. If we need to call data base function then we have to go for Opaque View .
16. Is deploying opaque is mandatory?
1. No. but it is recommended due to below reason
2.  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.
17. When automatic joins will be created in physical layer.
1.  If we have primary key and foreign Key relation ship in database level and while importing if we enable foreign key options then automatic joins will be created .
18. What is Alias
1. A virtual physical table (or) reference of physical table is called as alias.
2. On one physical table we can create ‘n’ no. of aliases.
3.  Whatever changes occurred in physical table that will immediately reflect in alias (except Key definitions)
4. It is mainly useful to resolve loops
19. Can we create or delete a column from Alias.
1. No
20. How to eliminate Circular Join or loop or closed path.
1. By Using Alias

Handson02:Business Model and Mapping Layer

1. What is the business model
1. Is the highest-level object in the BMM layer
2. Contains the business model definitions and the mappings from logical to physical tables
3. Simplifies the physical schema
4. Captures how users think about their businesses using their own vocabulary
2. Can we create business model with single logical table, if no please let me know work around.
1. Approach 1: Creating duplicate table in BMM layer
2. Approach 1: Creating alias and creating join between original table and alias
3. Can we create business model from multiple databases, did you created then tell me one scenario
1. Yes
2. Scenario: when we want to compare the Actual data with target data and these data coming from sources like oracle database and MS-Excel respectively 
4. What is the Logical Table Source and when we will go for multiple logical tables
1. Logical table sources define the mappings from single logical table to one or more physical tables. 
2. A logical table contains one or more logical table source. 
3. The mapping between physical columns and logical columns can manage using LTS
4. By integrating multiple physical tables to LTS or  By integrating multiple LTS to LT we can convert snow flake schema into star schema 
5. When we need to integrate multiple physical tables in LTS.
1. If we have snowflake schema then want to convert that into star schema then we will go for multiple logical tables.
6. Did you get chance to map a logical column with multiple physical columns
1. Yes, If we want to configure aggregate tables then we need to do it .
7. What is logical join and why it is useful?
1. A: The join defined in the BMM Layer is called logical join
1. Logical join is used to defined driving table
2. Logical Join is used to define Inner, Right, Left and Full outer joins
3. Used to defined the cardinality
4. Logical join is useful to identify dimension and fact table
5. Logical join allows Oracle BI Server to make the best decision about the exact physical SQL to generate based on the logical query path.
8. 7.Can we have a table without logical join?
1. No
9. Can we have a dimension table without primary key?
1. No
10. Can we copy RPD objects within the RPD and from one RPD to another RPD?
1.  Yes, we can copy RPD objects with in the RPD.
2. We can copy one RPD to another  RPD but we need to copy corresponding layers only i.e. (physical to physical, BMM to BMM and presentation to presentation)
11. What is Renaming wizard and which layers can be modified.
1. Renaming wizard is used to rename all objects ( example : tables and columns) of BMM and Presentation layer
2. 
  
 
 

Handson03:Presentation Layer

1. Can we create a subject area from multiple business models
1. No
2. In which version of OBIEE , presentation layer supporting hierarchy object
1. OBIEE 11G
3. What is Aliases


1. Keep track of any changes to Presentation layer objects
2. 
4. What are nested tables
1. Prefix the name of the presentation folder to be nested with a hyphen and a space. or type " - > "in description of the column 
2. Place it after the folder in which it nests
5. Can we map a presentation column with multiple logical columns
1. we can not
6. A report as shown is using by business

 

01 Physical Layer

STEP 1: Creating RPD file and importing metadata

1.    Start à All Programs à oracle business intelligence à BI administration

2.    Go to file menu à new repository.

3.    Name : RRITEC_EBA

4.    Repository password: RRitec123

5.    Retype password:RRitec123(minimum length should be 8 characters)

6.    Click on next

1.    Select Connection type: OCI 10g/11g (native connection of oracle data base)

2.    Data source name: ORCL

3.    User name: supplier2

4.    Password: supplier2

5.    Click on next

1.    Select tables, keys, foreign keys

1. Click on next
2. Expand supplier2 select D1_CALENDER2,D1_CUSTOMER2,D1_ORDERS2,D1_PRODUCTS.
3. Click on import selected (>).
4. Name the connection pool as RRITEC_CP
5. Click on ok
6. Click On finish.

Step2 : Creating keys and joins

·         Constraints: mainly we have 3 types of constraints.

                                      I.        Unique constraints à eg: emp no.

                                    II.        Not null constraints à eg: salary.

                                   III.        Check constraints à eg: phone number.

·         Keys: we have three types of keys

1.    Primary key

2.    Foreign key

3.    Composite key

1.    Primary key: unique + not null constraint is called as primary key.

Eg: Empno in emp table, deptno in dept table.

2.    Foreign key: reference of primary key is called as foreign key.

             Eg: deptno in emp table

3.    Composite key: if we need two or more columns to uniquely identify record then it is called as composite keys.

Eg: product no + product color.

 Joins:

A relationship is called as join. In database we have mainly 5 joins.

1.    Equi join >>>>>>>Foreign Key

2.    Non equi join>>>>>>Complex

3.    Outer join(left, right, full) >>>>>>  Logical join in BMM layer

4.    Self join.   >>>>>>   Alias (in Physical Layer)

5.    Cross join. >>>>> not supported by any BI tool

OBIEE (11G) Joins:

In OBIEE (11G) we have three types of joins.

1.    Foreign Key join

a.    Equivalent to Equi join

b.    Accepts only = operator

c.    Most used join in physical layer

2.    Complex Join

a.    Equivalent to Non Equi join

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

c.    Less used join in Physical layer

3.    Logical join : BMM layer Join is called as logical join (in OBIEE 10G it is also called as complex join)

Process to create joins:

In physical layer select supplier2 schema object click on physical diagram àobjects and all joins àclick on new join

·         Drag and drop from D1_orders table to calendar select yyyymmdd and period key click on ok.

·         Drag and drop from Orders table to customers. Select new key from customers and select custkey from orders.

·         Drag and drop orders table to products. Select product key from products and select prod key from orders.

·         Click on collapse all. Click on auto layout notice that we created star schema. Close physical diagram window.

Note1: Is OBIEE supports multiple databases?

Yes OBIEE has no limitation, to prove this import excel meta data into rpd

Step 1: Creating MS EXCEL Data base

1.    Open ms-excel.

2.    Create a file

Product	Price
Lux	10
Rin	20

·         Select all the cells name it as products. Similarly create one more table with the name of Sales .

  

Year	Sales
2001	100
2002	200

·         Save it into E drive with the name of RRITEC.

Step 2: Creating ODBC connection

·         Go to start à control panel àadministration tools à data source (ODBC)   à system DSN à Add

·         Select Microsoft excel driver à finish.

·         Give data source name : RRITEC_excel

·         Click on select workbook à select E drive , select required file à double click

·         Click on ok. Again ok. Again ok.

Step 3: Importing Meta data

·         Open administration tool.

·         Go to file menu à open à offline

·         Select RRITEC.rpd à open

·         Provide pwd as RRitec123

·         Go to file menu à import metadata

·         select connection type: ODBC 3.5

·         DSN :RRITEC_excel

·         Click on next again next

·         Select two tables(products, sales)

·         Click on import

·         Click on finish

Note2: Can we access all the tables of a database using one connection pool.

Yes. but we need to take care below points

1.    To access all the schemas of DB we should have all schemas permissions to connection pool user.

2.    In connection pool properties we need to enable Required fully qualified names.

3.    In connection pool select shared logon

Note3: To create automatic joins we need to have

1) In DB level foreign key relationship

2) While you are importing you should enable foreign key options.

Note4: Update Physical Layer

It is useful to synchronize RPD metadata with DB metadata

ToolsàUtilitiesàUpdate Physical Layer

Note5: Remove unused objects from physical layer

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

ToolsàUtilitiesà Remove unused objects from physical layer

This utility cannot track columns 

OBEE LAB COPY DOWN LOAD

Step 1: Open Google Chrome  browser

Step2: Copy and paste given material URL in your mail  into browser

XXXXXXXXXXXXXXXXXXXXXXXXX

Step3: In below screen click on sign in

Step 4: Provide your gmail  username  and password  | Click on Signin

Note : it won't work other than gmail id 

Step 5: Click on Add Drive | Open in drive

Step 6 : Select  MyDrive | Select 01 OBIEE LAB COPY | Go to More  | Click on Download