What is the OBIEE RPD File and it's Significance ?

The RPD file or Repository is the heart and soul of an OBIEE instance. We discussed earlier in one of our previous posts that Oracle BI has two kinds of metadata, one that exist in database tables and the other which exists in file format. The RPD file is the one of the OBIEE metadata that exists in physical file format, the other being the presentation catalog. 

What does the OBIEE RPD contain ?

The OBIEE RPD is where all the metadata related to a data warehouse or a data source is stored. It basically contains all the OLAP tables which are used to populate graphs and charts, the physical joins between the data. The star schema design/BMM design which tells the BI Server how OBIEE server should fire the query to the data warehouse/database.

The Repository is mainly composed of three layers:

1.Physical Layer 

 This is where you import all your Data warehouse tables. You create your database connection here along with the credentials to connect to the warehouse schema. You can also specify options like connection pooling, timeout option and various other database parameters.

Here you create Alias Tables for all the tables and define the joins between these tables. The joins defined here are inner joins. You can safely say that things relating to the database resides here. 

2.Business Model and Mapping(BMM) Layer

The BMM layer or Business Model and Mapping Layer is where you define the relation between the tables that you had earlier imported to your Physical Layer. Here you define your BMM diagram,  create hierarchies for your dimensions define complex joins between your database tables, set aggregation for measures.  You can also define complex calculations to facts here, however it is recommended to push all complex calculations to database or during the data load to the data warehouse.

Inside the RPD, arguably this is the most important layer as this is where each business process is defined in a way that OBIEE server understands. It is very crucial that the BMM model is designed properly for a compact BI Analytics implementation.

3. Presentation Layer

The presentation layer is what the end user or the BI Author user would see when they log in to the Analytics. The BI Author or the report developer pulls data from this layer using the web UI. It is important that the dimensions and facts are properly organised in this layer so that somebody developing the report would be able to easily pull in columns and build a report.

RPD Location - You can find the RPD file under the Middleware Home folder in your OBIEE server. The exact location to find the RPD file is as below,

MW_HOME/instance/instance1/bifoundation/OracleBIServerComponent/Repository/

In this folder you will be able to find the Repositories that have been deployed over time. To get the currently used RPD name go to Enterprise Manager > CoreApplication > Deployment Tab.

Which component of OBIEE manages the RPD file ?

It is the OracleBIServer component which comes under OracleBI11g components that handles and communicates to the repository file. Every time a request comes from the Web UI(Presentation layer). The OBIEE server refers to the metadata residing in Repository file to convert the same to a Physical SQL query. The data from the SQL query is sent back to analytics for use in reports.

What is RCU in OBIEE 11g ?

The RCU or the Repository Creation Utility is a small application that comes with OBIEE 11g Installation files. The purpose of the RCU is to load the two Operational Metadata schemas for OBIEE Installation into an RDBMS system, 

• BIPLATFORM Schema
• MDS (Metadata Services) Schema

It basically runs a list of .SQL scripts that create new tables to store OBIEE operational metadata which will later be used by the Oracle BI 11g Instance. 

It makes life easier by running the table creation metadata so that user doesn't have to go through the pain of executing scripts in RDBMS one by one. 

OBIEE: What is a Driving Table and when is it preferred ?

A driving table is a feature available in OBIEE using which one can improve performance of cross database joins. However this feature is very limited and can be counter productive. 

Here is how Oracle defines a Driving Table,

Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large. Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined. 

When you specify a driving table, the Oracle BI Server will use it if the query plan determines that its use will optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows

This feature is available in the BMM(Business Model and Mapping Layer) of repository where you define a logical join between two tables, Here you will be able to see an option called Driving Table where you can select one of the two tables in the join. Make sure you are selecting the table with the less number of rows, usually preferred when data in one of the tables is very less ( <1000) and the other table is very large. 

Driving Table feature in Logical Join of OBIEE RPD.

Reference: Driving Table - Oracle 

What are Alias Tables and It's significance in OBIEE Physical Layer

An Alias Table or Alias in OBIEE is a physical table which references to a different physical OLAP/OLTP table as it's source.

Alias tables are often used for Repository development over Physical tables as they have the following properties,

1. Enables reuse of a physical table.
2. Avoids circular joins that can form when importing OLTP source tables.
3. Create self joins between tables, for eg: Manager is also an Employee.
4. Enables to easily identify source tables and adhere to data warehousing naming standards.
5. Makes physical query generated easier to understand.
6. Can override source table's caching properties like Cacheable, Cache never expires and cache persistence time. 

Alias Tables in OBIEE 11g - Example.

How are OBIEE Metadata files stored ?

The metadata for an OBIEE Instance is stored both as Database tables and as Physical files.

Metadata in Database tables are stored in the Metadata Schema's

MDS (Metadata Services Schema)

BIPLATFORM 

The Physical metadata files for OBIEE are the

• Repository (RPD)
• Catalog Files

The repository file can be found at the following location and will have the extension .rpd. 

MW_HOME/instance/instance1/config/OracleBIServerComponent/coreapplication_obips

The currently active RPD name can be checked from EM or from the instanceconfig.xml file. The repository file contains the details of Physical Tables, Physical and logical mappings, Presentation tables etc. 

The catalog files are located the physical directory below,

MW_HOME/instance/instance1/config/OracleBIPresentationServiceComponent/coreapplication_obips1

The catalog contains Metadata for reports,prompts, dashboards etc and their permissions. The physical directory for the latest catalog displayed can be found from the EM.

What is OPMN and it's functions ?

OPMN stands for Oracle Process Management and Notification System that acts as a management service for different oracle components. In an OBIEE instance, the OPMN is responsible for controlling the different Oracle BI 11g components.

Functions of OPMN are the following

• Start and stop the OBIEE components separately or at once. 

Oracle BI 11g system components

• Restart an OBIEE component upon failure.

The different Oracle BI components that OPMN works on are the following

1. BI Presentation Services
2. BI Server
3. BI Schedulers
4. BI Cluster Control
5. BI JavaHosts

The OPMN batch file can be accessed from the below location from the Middleware Home folder.

FMW_HOME/instances/instance1/opmnctl.bat

You perform the following operations using the opmnctl batch file,

1. Start All
2. Stop All
3. Start Procedure
4. Restart Procedure 
5. Stop Procedure
6. Status

eg CMD scripts:

• Start all - opmnctl startall
• Stop all - opmnctl stopall
• Startproc - opmnctl startproc ias-component=OracleBIServerComponent
• Restart process - opmnctl restartproc ias-component=OracleBIServerComponent
• Stop process - opmnctl stopproc ias-component=OracleBIServerComponent
• Status - opmnctl status You can also invoke the OPMN services from Enterprise Manager.

OBIEE Interview: Why is OCI preferred over ODBC in RPD ?

Question: Why is OCI preferred over ODBC in OBIEE RPD? 

Answer: 

OCI is preferred over ODBC only when the source database system is Oracle since OCI stands for Oracle Call Interface and is a native driver for Oracle database connections. You can go for an ODBC connection for the same as well because ODBC stands for Open Database Connectivity. 

Since OCI is a native driver for Oracle, using an OCI connection over ODBC improves performance while connecting to an Oracle datasource from OBIEE RPD. OCI also provides access to many native database features otherwise not accessible from ODBC.

Question: Is an OCI connection preferred when connecting to a Database other than Oracle?

Answer: 

No. It is not possible. Since OCI is a native driver to connect to Oracle Datasource it can't be used to connect to data sources other than Oracle.

Links to read more:

1. http://twobiee.blogspot.in/2012/10/odbc-or-oci.html
2. http://obieemanu.blogspot.in/2011/01/odbc-vs-oci.html