Oracle Business Intelligence is a metadata driven BI tool and the main backbone of this tool is called Repository file that commonly known as RPD. The RPD file is clearly devided into three main layers based on the usage and design. They are -
The first object that comes in the physical layer is the Database. In OBI EE this Database object reflects an abstruct database instance and you can change its properties to fit this absturct instance to a specific database instance like Oracle Database, DB2 or SQL Server.
This can be changed using the database source defination from the general property tab. In most cases if you are importing the tables using the import utilities, this Database instance will be created automatically with all the proper values. But in some cases you may have to build it by yourself, in those times make sure you have selected the database source defination properly. The database features strongly depends on the database you selected. Wrong selection can lead to improper query and poor runtime performance.
Persist Connection Pool is needed in some CRM databases where the database itself can not meet the requirement of the query. In that case an intermediate table is created in the database to hold some information that can be used to meet the query requirement. Not generally used.
Allow Populate Query is a mechanism that stores some result (known as POPULATE SQL) in the database. Not generally used.
Direct Database Request is a mechanism to allow end users to directly fire physical sqls against the physical data sources. If not used properly it can cause security issues. Not generally used.
Name : Any logical name to identify the connection pool.
Call Interface : The approach through which the database will be connected. For most database if the native call interface (like OCI in case of Oralce) is available, use that one for better performance. If it is not available use the ODBC datasource.
Maximum Connection : OBI EE creates a set of connection during start up and use those connections in the pool. But that initial number of connections can not be sufficient to handle the pick load. In that case OBI EE will create new connections and add them to the connection pool set. This parameters limits the number of the maximum number of connections that can be creates in a pool. In case of production system the number of connection pool can be determined using this rule (Avg Number of Requests Per Dashboard X Avg Number of Concurrent Users) X 10%. The maximum number of connection allowed from a repository is 800.
Fully Qualified Table Name : This ensures that all the table name when added to the SQL automatically prefixed with the Schema name. Use only when you are building models involving more than one database schemas.
Shared Logon : Same log on will be used for all the users. If you are using different credentials for different users then this should be unchecked.
Connection Pooling : Connection will be kept alive for the timeout period and will be reused for other queries.
Other parameters are not generally used. keep the default values.
Sometimes you may need to create SELECT statements in physical layer, thay are called Opeque Views. But use them only when you do not have any way to create database views.
The last component that comes is called Alias. Alias is very important in Oracle BI physical layer design. You should create one alias for each of the roles for a table. As Fact tables has only one role and Dimensions might have more than one role, so in a good RPD you will find many alias created on the dimension tables. They improve the design by eliminating the chance of circular joins from physical layer and provides easy to understand logs during run time.
For every Table, Opeque View and Alias, define proper primary keys and caching. If you have a table that is refreshed very often then disable the caching for that table. For other table make cache never expires and use either Event Pooling or Manual cache purging (using nqCMD) to handle the cache. You can also provide database hints for each table you import.
- Physical Layer
- Business Model and Mapping Layer
- Presentation Layer
Database :
The first object that comes in the physical layer is the Database. In OBI EE this Database object reflects an abstruct database instance and you can change its properties to fit this absturct instance to a specific database instance like Oracle Database, DB2 or SQL Server.
This can be changed using the database source defination from the general property tab. In most cases if you are importing the tables using the import utilities, this Database instance will be created automatically with all the proper values. But in some cases you may have to build it by yourself, in those times make sure you have selected the database source defination properly. The database features strongly depends on the database you selected. Wrong selection can lead to improper query and poor runtime performance.
Persist Connection Pool is needed in some CRM databases where the database itself can not meet the requirement of the query. In that case an intermediate table is created in the database to hold some information that can be used to meet the query requirement. Not generally used.
Allow Populate Query is a mechanism that stores some result (known as POPULATE SQL) in the database. Not generally used.
Direct Database Request is a mechanism to allow end users to directly fire physical sqls against the physical data sources. If not used properly it can cause security issues. Not generally used.
Connection Pool :
After Database instance the next element that comes is call a Connection Pool. A connection pool as the name suggests is a pool of prebuild connections that will be reused for different query requests. We define the TNS NAMES (for Oracle Database) in this section. We also provide the user credentials as well as some pooling relared parameters like timeout period etc.Name : Any logical name to identify the connection pool.
Call Interface : The approach through which the database will be connected. For most database if the native call interface (like OCI in case of Oralce) is available, use that one for better performance. If it is not available use the ODBC datasource.
Maximum Connection : OBI EE creates a set of connection during start up and use those connections in the pool. But that initial number of connections can not be sufficient to handle the pick load. In that case OBI EE will create new connections and add them to the connection pool set. This parameters limits the number of the maximum number of connections that can be creates in a pool. In case of production system the number of connection pool can be determined using this rule (Avg Number of Requests Per Dashboard X Avg Number of Concurrent Users) X 10%. The maximum number of connection allowed from a repository is 800.
Fully Qualified Table Name : This ensures that all the table name when added to the SQL automatically prefixed with the Schema name. Use only when you are building models involving more than one database schemas.
Shared Logon : Same log on will be used for all the users. If you are using different credentials for different users then this should be unchecked.
Connection Pooling : Connection will be kept alive for the timeout period and will be reused for other queries.
Other parameters are not generally used. keep the default values.
Physical Layer Objects - Table, View and Alias :
The physical layer as the name prescribes hold the links for the physical database objects - Tables, Views and Aliases.
The tables can be imported directly from the database sources or it can be created manually. The easiest way is to import them from database. You can find the import option from menu File-> Import -> from database. Provide the database information and it will show you all the objects accessible using that database user. Select the set of tables, views and click ok. This will create a connection pool, a schema folder and import the selected tables.
Sometimes you may need to create SELECT statements in physical layer, thay are called Opeque Views. But use them only when you do not have any way to create database views.The last component that comes is called Alias. Alias is very important in Oracle BI physical layer design. You should create one alias for each of the roles for a table. As Fact tables has only one role and Dimensions might have more than one role, so in a good RPD you will find many alias created on the dimension tables. They improve the design by eliminating the chance of circular joins from physical layer and provides easy to understand logs during run time.For every Table, Opeque View and Alias, define proper primary keys and caching. If you have a table that is refreshed very often then disable the caching for that table. For other table make cache never expires and use either Event Pooling or Manual cache purging (using nqCMD) to handle the cache. You can also provide database hints for each table you import.
Physical LayerJoins :
Once all the required tables, views and alias are created you have to create proper joins between these objects. In physical layer the joins should be always primary key - foreign key joins. Do not use complex joins in physical layer. This will impact the performance of the application very much. You only need complex joins in physical layer if your datamodel is not proper. So if your data model needs complex joins in physical layer it is better to remodel the application than creating such joins.
Once the joining are complete check the consistancy of the model. Right click on the database schema and click the Check Consistancy on the popup menu.
