Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Sunday, 17 November 2013

Physical Layer Objects in OBIEE 11g

What are the Physical Layer Objects

1. Database

2. Connection pool

3. Schema folder

4. Tables

5. Columns

6. Keys

Screen shots for your reference

Physical Layer Objects in OBIEE 11g
Physical Layer Objects in OBIEE 11g

Create an Alias in OBIEE

1. A common use of alias is to use the table or column to play simultaneously for several times.

2. A single dimension appears more than one time for more purpose.

3. OBIEE does not support Circular join in BMM layer, so we can overcome from this problem by creating alias table for one of the table in that circular join.

4. OBIEE does not support self join, so we can overcome from this problem by creating alias table.

5. If a fact table having relationship with dimension table on more than one key, then we can create aliases for the dimension table, then we can join fact table to these alias table on different keys.


1. Right click on the Physical layer of the table, New objectà Alias.

2. Give the name of the alias , automatically it assigns the columns of the physical table 

3. Even it inherits all the properties of the physical table.

Screen shots for your reference:
Create an Alias in OBIEE
Create an Alias in OBIEE

Create an Alias in OBIEE
Create an Alias in OBIEE

Create an Alias in OBIEE
Create an Alias in OBIEE

How do you create a DSN for OBIEE 11g

DSN – Data Source Name, the name which suggests connecting or relates the database.

Steps to Create DSN:

1. Open control panel and go to Administrative toolsà ODBC Administrator.

2. Go to the second tab “system DSN”.

3. Click on Add and select the driver of the database which you are using currently.

4. Give the Credentials first assign name for the Data Source Name.

5. Give the TNS entry to connect the data source.

6. Database User name and then click test connection.

7. Give the credentials in test connection, user name and password of data base.

8. Check the connection is success or not if yes then click ok.

Screen shots for you reference:

DSN for OBIEE 11g
DSN for OBIEE 11g

DSN for OBIEE 11g
DSN for OBIEE 11g

DSN for OBIEE 11g
DSN for OBIEE 11g
DSN for OBIEE 11g
DSN for OBIEE 11g

Shared Log On option in OBIEE 11g

Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password which given in the connection pool.

If this option is selected, then all connections to the database that 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.

Screen shots for your reference:

Shared Log On option in OBIEE 11g
Shared Log On option in OBIEE 11g

How do you enable Cache in OBIEE 11g

1. Go to the location D:\11G\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

2. and find the NQSConfig.INI file, move down to the section “Query Result Cache Section”

Enable =yes.

3. Login to Console (http://localhost:7001/EM)

4. Go to Business Intelligence -> Core Application -> Select Capacity Management tab -> Select Performance tab below capacity management tab -> Click 'Lock and Edit Configuration' -> Deselect the 'Cache enabled' option -> Click Apply -> After that Click 'Activate Changes'

5. Restart the services.

Screen shots for your reference:

Cache in OBIEE 11g
Cache in OBIEE 11g

Cache in OBIEE 11g
Cache in OBIEE 11g

What is Connection Pool in OBIEE 11g

Connection pool is defined as the connection between the data source and Oracle bi server.

Connecting database using the interface like OCI, odbc, etc. to the Admin tool RPD to serve the information of data.

Screen shots for Your Reference:

Difference b/w Logical join and Physical Join in OBIEE 11g

1. Relationship between the logical tables was expressed by logical joins in BMM layer.

2. Logical joins express the cardinality relationships between logical tables and are a requirement for a valid business model. 

3. Specifying the logical table joins is required so that Oracle BI Server can have the necessary metadata to translate logical requests against the business model into SQL queries against the physical data sources. Logical joins help Oracle BI Server understand the relationships between the various pieces of the business model. 

4. When a query is sent to Oracle BI Server, the server figures out how to construct physical queries by examining how the logical model is structured. 

5. Examining logical joins is an integral part of this process. 

6. The Administration Tool considers a table to be a logical fact table if it is at the “many” end of all logical joins that connect it to other logical tables.

Physical Joins

1. Relationship between the physical tables and columns are expressed by physical join in the physical layer.

2. A primary key and foreign key relationship defines a one-to-many relationship between two tables. 

3. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. 

4. The primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table. 

5. Keys and joins help Oracle BI Server determine the fact–dimension relationships between tables.

6. The Physical layer typically uses foreign key joins to define relationships, whereas the Business Model and Mapping layer uses logical joins to define relationships.

complex/logical join in the BMM layer in OBIEE

1. Complex Join in the BMM layer represents the relationship between the logical tables and its columns.

2. It makes the best decision about sql request to generate based on the logical query request.

3. Use of complex joins, can create one to many relationships and not the logical foreign key.

4. We can’t use the expression builder in the complex join because it will be grey out. You can only use the types of joins like inner or outer.

5. Logical joins are automatically created if both of the following statements are true:

6. You create the logical tables by simultaneously dragging all required physical tables to the Business Model and Mapping layer.

7. The logical joins are the same as the joins in the Physical layer

8. Logical tables in a BMM layer can have multiple logical tables sources (LTS). Complex join actually is an intelligent join between LTSs of two logical tables in BMM Layer.

Normalized and De-normalized in Data warehouse


It is the process of effectively organize the data in to the database.

Eliminating redundant data storing the same data for more times.

Stores only related data into the database.

It is the process of structuring relational database schema such that most ambiguity is removed.

De – Normalized:

It is process to increase the database performance.

It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table.

Difference Between OLTP and OLAP

OLTP: Online transactional transaction processing

It is characterized by large number of rows of data’s per second.

For each and every transaction of data will get store for time to time.

In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).

All the transaction in OLTP normally will consider as Normalized.

OLAP: Online Analytical Processing

It is characterized by very low volume of transactional data.

Normally OLAP will consider as De-normalized.

All the transactions are aggregated to view in very less rows of data.

In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

What is Measure in Data warehouse

Measure is normally a numeric value represents business values.

Can have multiple measures in a fact table.

Measures unlike dimensions give the fact numeric value instead of the detailed analysis.

Different types of measures are listed below:

1. Additive measures are measures that can be added across all dimensions. For example customer count in numbers can be added across all dimensions.

2. Semi-additive measures are measures that can be added across some, but not all dimensions. For example the bank account balance is simply a snapshot in time and cannot be added over time. However you could add multiple accounts of the same customer to get the total balance for that customer.

3. Non-additive measures are measures that cannot be added across any dimensions. For example the procurement is simply a snapshot in time and cannot be summed over time. Also you cannot combine procurement for various items.

What is a dimension table in data warehousing

A category of information.

1. It relates the context of text of information for a fact table measure.

2. Dimension table have Surrogate key, one or more other fields that compose the natural key (nk), primary key and set of Attributes.

3. Size of Dimension Table is smaller than Fact Table.

4. In a schema more than one number of dimensions is presented than Fact Table.

5. Surrogate Key is used to prevent the primary key (pk) violation (to store historical data).

6. Values of fields are in numeric and text representation.

What is a Fact Table in Data warehousing

A fact table that contains the measure of values (Numeric Data of business).

For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.

In Oracle Business Intelligence Fact table have foreign key to get connect with the dimension tables.

1. It provides measure of an entity.

2. Measurement is the amount (aggregate values).

3. Structure of Fact Table - foreign key (fk), Degenerated Dimension and Measurements.

4. Size of Fact Table is larger than Dimension Table.

5. In a schema less number of Fact Tables observed compared to Dimension Tables.

6. Compose of Degenerate Dimension fields act as Primary Key.

7. Values of the fields always in numeric or integer form.