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
