OBIEE Physical Layer Design Principles/Best Practices

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about

Today we will discuss the design principles for physical layers

Principle 1 :Import only needed tables in physical layer and do not create tables manually in physical layer.

Import Tables

Reasons to do that :Its easier to import the table then creating it manually.In that way we are avoiding the table name , column name , data type mis match possibility. Import only those tables which you need. If you need more tables at later stage in project, you can always import them.

You will be using connection pool to import the table and that helps to make sure that connectivity to source database system is working fine.

Principle 2 Create alias for the physical tables

Physical Layer Alias Table

Reasons to do that :

It helps in avoiding the circular joins.

It helps when we have confirm dimension(e.f time dimension) and we want to join dimension different way

Its easier to give the access of objects to users in case of Multi User Development Environment

Alias naming convention helps in easily finding the fact and dimension table in physical layer

We can import the key relationship in physical layer from database and maintain the relationship which is required among the alias tables

Principle 3 Set the cache  property of the physical table

Cache Property of the tables

Reasons to do that

Its better to set the caching at physical table level and not at the alias table level.This applies to most of the scenarios

It your data source is OLTP system make sure you disable to caching. As  that changing frequency would be high and you do not want to cache results.

For all the data warehouse sources have better cache persistence time set

Principle 4 connection pool(s) and its properties

Physical Layer Multiple connection Pool

Reasons to do this.

To have multiple connection pool for difference purpose. e.g for usage tracking, security implementation etc it better to have different connection pools.

COnnection Pool Property

Call interface: Use native call interface for the connection(e.g OCI10g) etc based on source database. Its faster then generic ODBC connection.

set connection pool , time out ,maximum connections etc based on server configurations and usage

connection pooling- it avoids the database connection overhead. If users run the same query it users the same connection from the pool. What does this mean is user sessions share the connection in this.

Maximum Connection :

Max what it should be :Approx 1024 kb of server memory is consumed in each connection.So tThis has to be set per server configuration and requirement.

Min what it should be: 25% of max users times the reports they execute.

e.g if you have 1000 users accessing around 10 reports  in dashboard that this number should 250 ( 25% of 1000) * 10 = 2500

Please leave me a comment if you have lean any other design principal as a hard lesson in your projects