[Oracle 12c] Find Default and TemporaryTablespaces and size of Database Schemas

This is an example on how to find the default and temporary table space details of a particular schema. This comes in handy when debugging tablespace exceeded errors like ORA-01536 and ORA-00059. This types of errors are often common in data warehouses full base load scenarios.

To find the Default Tablespace and Temporary Tablespace of a particular database schema, you need to have an user with DBA/SYS privileges. After you have logged in with this particular user, run the following SQL query,

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME IN('Schema Name Here')

Temporary Tablespace Size

Next to find the size of the temporary tablespace, To find the size of the temporary tablespace we need to query the system table DBA_TEMP_FILES, Find a sample query below

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME IN ('Schema Name Here')
GROUP BY TABLESPACE_NAME

Temporary Tablespace Size.

Default Tablespace Size

To find Default Tablespace size you need to query the system table DBA_DATA_FILES. See a sample query syntax below, 

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('EXAMPLE')
GROUP BY TABLESPACE_NAME

Default Tablespace Size.

Data warehouse basics: What is Incremental Data Load ?

So we are back again to brush the basics. I have always felt that for a BI/DW developer to become successful he/she should have a very strong base about the basics. Here is one basic concept we hear often, Incremental load.

So what exactly is Incremental data load ?

Incremental data load is a type of data load scenario in a Data warehouse environment where the data that is updated/new are inserted to the target data warehouse with each scheduled run.

Imagine a sales person table as below which will be our source table in this case.

On the first day of loading to an EDW table, all the above three records would be moved.  The target table would be same as above. 

Now a day after they have added two new employees and also made a change to first name of a previous employee. Refer the screenshot below, 

Here with incremental update the two new records are considered and written to the target table. Also checks the source if the already existing records in EDW has any changes in source. There is the first record where FST_NAME was changed from Steve to Steven, hence this record is also considered and the change is updated in the target table. 

The advantages of incremental loads are the following,

1. Reduces a major chunk I/O operations between source and target. 
2. Faster than a full base load.

Find Indexes created on Oracle Database Schema/Table

Hey everyone, here is a small tip on finding and managing Oracle indexes in a database or schema. Below are a set of queries that can be used for the same. Note that you need to have SYS/DBA privilege on the database to perform the same. This is intended for people who are new to Oracle databases. 

All information related to indexes on an oracle database are stored in the DBA_INDEXES table that belongs to SYS schema. Below are a few common scenarios when related to querying from this table. You may replace the predicates accordingly. 

Find all indexes defined on a database,

SELECT * FROM DBA_INDEXES

DBA_INDEXES table in Oracle 12c. 

Find all indexes on a particular schema, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR')

Find Indexes on Schema.

Find the indexes defined on a specific table, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR') AND TABLE_NAME IN ('EMPLOYEES')

Indexes defined on a table.

Find Unique indexes in a database,

SELECT * FROM DBA_INDEXES WHERE UNIQUENESS IN ('UNIQUE')

Find number of indexes defined on a database schema by index type,

SELECT OWNER, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES GROUP BY OWNER, INDEX_TYPE

Find the number of Indexes on a particular table,

SELECT OWNER, TABLE_NAME, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES
WHERE OWNER IN ('HR') 
AND TABLE_NAME IN ('EMPLOYEES')
GROUP BY 
OWNER, 
TABLE_NAME,
INDEX_TYPE

Indexing Strategy in DW: What are Indexes, Types and Significance ?

We are starting off a new series on Indexing strategies in an Oracle Data warehouse. Before going into detail let us first see what an Oracle Index is. 

A database index is a database object that is created to optimize the performance of a read/select statement on a particular table. They greatly reduce the I/O operations on a table by reducing the rows processed. 

Oracle defines Indexes as below, 

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

'Indexing' is a term coined for the strategy used for indexing in a particular relational DBMS. Let us see the advantages of indexing

1. Avoid full table scans and there by reducing the cost and time to retrieve from a database table.
2. They avoid sorting of oracle queries. which in turn reduces the time for retrieval of data. 

There are different kinds of Indexes available in Oracle and they are the following, 

• B-Tree or Normal Indexes
• Bitmap indexes
• Function based indexes

B-Tree Indexes are the most common indexes in oracle database. by default a B-Tree index is generated. Here values are stored as leafs. There are branch blocks and leaf blocks. Branch blocks contain the leaf blocks where actual pointer values are stored. Here one entry in an index points to one row.

Bitmap indexes are usually created for low carnality columns like Gender, flags etc. In Bitmap indexes one index entry points to multiple rows. 

Function based indexes are indexes created on columns with a function. If one was to apply calculations on a particular column then function based indexes are created on that particular column. 

OBIEE Analysis View Display Error Maximum cells exceeded Error EY692ZW9 [Solved]

Issue: When trying to export a OBIEE Analysis to 

View Display Error Maximum total number of cells exceeded (Configured Limit: 50000). Error Codes: EY692ZW9

Cause: Max rows parameter and tag not set at instanceconfig.xml file.

Solution: In the instance config file at 

/user/app/oracle/product/fmw/12.2.1/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS add a sub tag under <pivot> and <table> tags.

Set this parameter accroding to your requirement. We wanted to take some very big excel files and hence set the same as below,

<MaxCells>Value</MaxCells>

For the same to get effected you must once restart the BI Presentation service from Enterprise Manager. 

What are Columnar Databases and it's significance in Analytics/Data warehousing ?

I was recently watching a session on Amazon Redshift from Youtube, when I came to know that Redshift is a columnar database. Which Amazon boasted optimizes performance and storage to a great deal. This ended up in me wanting to know more and hence the post.

So what is a columnar database ?

Typical databases like Oracle, mySQL etc store information as rows, i.e for example the details of a particular students is stored in a row.  In columnar databases the information is stored as columns. Data in a particular column is grouped together unlike other databases where row wise data is grouped. 

Say for example I have the following raw data, 
Name: Athul
Age: 26
Sex: Male 
Location: Trivandrum

Name: Rahul
Age: 43
Sex: Male
Location Bangalore

In typical databases the data would be stored as below, 

Athul26MaleTrivandrum
Rahul43MaleBangalore

However when it comes to a columnar database, data is grouped in Columnar fashion, i.e

AthulRahul|2643|MaleMale|TrivandrumBangalore

Each column data here is stored in a separate blocks. The advantage they say is that data of same datatype are grouped together and hence storage and retrieval is easier. This makes it great for Analytical data storage. However operations like insert and update are costlier. Aggregations again are great because of this columnar structure. Data compression also is greatly increased in columnar architecture.  This being the case it is very advisable to consider columnar databases if you are dealing with huge volumes of data. Another advantage is that your databases need parse whole rows to read data as most queries are limited to a subset of the table's actual number of columns. 

Examples of Columnar databases are Apache HBase, Amazon Redshift etc. Below is a good youtube video explanation for columnar database.

   Columnar Databases

courtesy: StackOverflow 

ODI Performance Tuning - Array fetch and Batch update size parameters

There are two parameters in Oracle Data Integrator topology that can be tweaked to improve performance of loading. These two parameters are, 

• Array Fetch size 
• Batch Update size

The Array Fetch size parameter can be tweaked for the source data server. This defines the number of records read from the source at a time and this data is stored in the agent. 

The Batch Update size parameter may be tweaked for the target data server. This defines the number of records fetched in one batch to be written to be server stored in the agent. 

When you increase number of records per batch, the time taken to read and write the total records will be less. This would mean you have lesser batches in total, hence improving performance. Since more data is stored in RAM, a good RAM size would be a necessity. 

Oracle recommends the following, 

• For good networks with little latency values less than 30 can be kept. However with a poor network the parameters have to be set as greater than 100. 
• The typical values as per oracle is between 50 and 500. 

Recommendation from Oracle ODI Performance tuning documentation. 

Here is a very interesting read on how this parameter if optimally set improves performance. 

BI Publisher Excel Template Output Date Format Error - 0-Jan-00 [Solved]

Issue: We were facing an issue where a date column in an excel output was shown as 0-JAN-00 instead of the original date. 

Cause: The reason is that when you create the excel(xls) BI Publisher template you have given the corresponding cell format as a date. Now in our case we had to convert the date to a TO CHAR format as MM/DD/YYYY. This meant that excel was not able to identify this as a date and hence defaulted it to 00-Jan-00

Resolution: You can resolve the issue by changing the type of the cell to 'General' from the the date format by going to format cells(right click).

Try exporting excel output again and check. Let us know if this did not solve your issue. 

Read more on dates and Oracle BI Publisher at the Oracle blog. 

Significance of nohup command for Weblogic/OBIEE in Unix/Linux ?

So you must be knowing that you can start an OBIEE server on linux with the following two commands, 

• ./start.sh

• nohup ./start.sh > start_1.log

So why use the nohup command when you can directly run the shell script without it. The basic difference between the two is that when you run a shell script with nohup, even after logging out from the terminal it runs in the background. This is not the case with direct execution, where when you close the terminal the process is also aborted. 

For an example, if we startNodeManager.sh file with direct  execute command, you will be able to see the log of how it is starting up. The nodemanager will eventually startup. The problem with this is that when you close the terminal, the nodemanager will go down. Inorder to avoid this what you can do is to run the shell script with nohup command. 

With nohup command the linux process is not aborted when the terminal is closed. So when you run nodemanager with a nohup appended command. It tells the server to run the process in background.  

There is two ways to write the nohup command for executing scripts.

• nohup ./start.sh

The above command will create a file named nohup.out in the same folder where the logs of the execution will be written to. 

• nohup ./start.sh > start_29112016.log

The above command writes to a file named start_29112016.log . 

I am new to linux, so any suggestions are welcome. Please feel free to write your comments below. 

[Solved] BI Publisher 12c - oracle.xdo.memoryguard.XDODataSizeLimitException - Report data size exceeds the maximum limit

Issue: oracle.xdo.memoryguard.XDODataSizeLimitException: Report data size exceeds the maximum limit.

Cause: The report was having a large number of records. Which exceeded the 300 MB default data size limit for BI Publisher. In-order to overcome this we required to change the memory guard limit in BI Publisher Runtime configuration.

Resolution:

• Log in to Oracle BI Publisher - http://hostname:9502/xmlpserver/
• Go to Administration > Runtime Configuration > Memory Guard

And Administration > Runtime Configuration > Data Model

Note: The values should be in bytes and not in Megabytes.If you want to give in MB or GB make the parameter values as 2GBb instead of 2 GB etc. Remember to remove the space in this case. 

• Here increase the size for maximum report data size from default to increased value as seen in the above screenshot.

Change/Increase the parameters

1. Maximum Report Data Size for online reports  
2. Maximum report data size for offline (scheduled) reports 
3. Maximum data size limit for data generation 

• Apply the changes. 
• Wait for confirmation that configuration has been changed successfully. 
• Run the report again to see whether the changes have been reflected. 

courtesy: Oracle Blog

Print Tick Mark in Oracle SQL Query [Solved]

Issue: Client wanted a column to have a condition in a way that if it is a 'Y' flag then a tick mark should be shown. This was a flat file report.

Resolution: You can't exactly call this a resolution but here is what you can do, As a workaround we searched for a character that matched, we found that in the Western Europe (DOS/OS2-437/US) character set. The 251th character is a symbol that resembles a tick mark.

SELECT CHR(251) FROM DUAL 

You will have to check in a DOS/OS2-437/US characterset database to find an exact match. This character will not be available in the most commonly used WE8ISO8859P1 and AL32UTF8 character sets.

Since our report was based on a case condition we wrote it inside a case statement,

SELECT CASE WHEN 1=1 THEN '√' ELSE NULL END

Another alternative is to use the symbol in query below,

SELECT '✓' FROM DUAL

We wrote the case statement and output data to flat file. The change reflected in Flat File as well. This is a work around, make sure the workaround is properly committed to client to avoid last minute issues.


Note: You will not be able to insert the data to a table with character set other than DOS/OS2-437/US.

Kindly comment if you have any other work-around which would work better. 

BI Publisher CSV Ouptut showing number incorrectly as Exponential E+ [Solved]

Issue: We were trying to export a data set as CSV output using BI Publisher. The problem we faced is that when we take the output, One column which was having a large number (close to 16 digits) was showing up as an Exponential (E+). You can see a screenshot of the output below when opening the CSV file in a notepad.

Exponential values 

Cause: The issue is because the datatype was double. Double in BI Publisher gets changed to Exponential at time of output generation. 

Resolution: The solution is fairly simple. You go to your data model and change the column datatype to Long instead of double. 

Now try again generating the CSV output. This worked well for us. 

Banking Domain - Types of Banking - Retail, Commercial and Investment explained

Currently working in a banking domain project and being new to the domain I felt very odd among my peers. This is an attempt at learning banking domain from an Analytics/Data warehouse perspective. 

The first and foremost question to start is with what is bank and banking ? Below is how google puts it for you.

Banking is a broad term that includes any function that is performed by a bank.  

There are mainly three types of Banking

• Retail/Consumer Bank
• Investment Bank
• Commercial Bank

Types of Banking - Explained

Retail or Consumer banking 

are financial institutions that deal with individual customers and SME's. 

1. Cater to Individuals and Small/Medium enterprises. 
2. Target audience is mostly individuals and SME's. 
3. The amount of money involved here is comparatively less.
4. Low risk banking model.

Investment Banking 

1. Caters to Corporations, Government organizations and financial situtations.
2. Target audience is corporations, financial institution
3. The Amount of money involved is very high compared to retail banking.
4. High risk banking model

Commercial Banking

A commercial bank primarily works with businesses

1. Caters to individuals, small investors as well as large enterprises.
2. Amount of money involved is high

OBIEE 12c Analytics Login Authenitcation Failure - NQODBC nQSError: 10018 [SOLVED]

This is one of the issues that took up a lot of time of ours.

Issue: OBIEE throws the following error when trying to login to analytics using weblogic username and password.

[OBIPS] [NOTIFICATION:1] [] [saw.securitysubsystem.checkauthentication.runimpl] [ecid: 667216e2-5177-4c25-86d2-b97ad8d56fa7-000002b2,0:1:1] [tid: 1830131456] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: ] Authentication Failure.

Odbc driver returned an error (SQLDriverConnectW).

State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

 (08004)

State: HY000.  Code: 43113.  [nQSError: 43113] Message returned from OBIS.

 (HY000)

State: HY000.  Code: 43126.  [nQSError: 43126] Authentication failed: invalid user/password. (HY000)[[

File:checkauthentication.cpp

Line:1534

Location:

saw.securitysubsystem.checkauthentication.runimpl

saw.threadpool.asynclogon

saw.threads

]]

Cause: We spent a lot of time trying to find the root cause of the issue. We checked metadata schemas, boot.properties, multiple restarts, created new users and many others. The actual cause was that we have two LDAP's defined, one default weblogic LDAP and the other client LDAP server. 

The problem was that the Client LDAP server was throwing a connection error while trying to connect. The LDAP server administrator credentials were changed.  

The authentication process first checks credentials against client LDAP. Which refused the connection and hence the error,

Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

Resolution: Update the Client LDAP providers with the latest credentials and then restart the server. 

Another work around is to change the client LDAP provider control flag to optional.

OBIEE dashboard prompt not working after passing correct data value [Solved]

Issue: OBIEE Dashboard prompt not filtering report correctly despite passing same data value.

Cause: The issue was that the actual database column say for eg: X_STATUS was having trailing spaces in database. In OBIEE when you create a dashboard prompt from this column, OBIEE automatically does a trim of the data and presents it to you.

To make things simple, imagine I am having a status 'Accepted     '  in database. Note that there are 5 trailing spaces in this column in the physical table. When you create a dashboard prompt the trailing spaces are removed and shown inside prompt. So the data in the prompt is 'Accepted' instead of  'Accepted     '. Now what happens when you prompt is that the filter passed in physical SQL would be as below

SELECT X_STATUS FROM TABLE_NAME WHERE X_STATUS='Accepted'

Now when the query reaches the table, X_STATUS column is searched for 'Accepted' instead of the original data with 5 trailing spaces. Hence a match is not found.

Resolution: Issue debugging was followed in steps:

First we checked if the dashboard prompt presentation column and report filter column was the same. It was the same.

Next we checked the physical query log and found the query passed to database. Checked for a cache metadata hit. Next we took the query and executed in database. This gave as no result as we explained earlier.

Next we did a DISTINCT on the X_STATUS column. Which gave us all distinct values, from the value we copied the Accepted data column and pasted it inside two single quotes (''). If there are spaces in the data the quotes will not end after the last letter.

The solution is to do a trim on the column in OBIEE report filter or the better solution is to do a trim on the actual physical column in Data warehouse.

Lesson Learned: The lesson learned was that when you create a dashboard prompt. OBIEE automatically does a trim function on it.

Replace NULL with Decimal 0.00 in OBIEE Pivot Table

Issue description: Replacing null code for pivot table not showing 0 with decimal places. 

Solution: As you all know the solution is very widely known to all. It is as simple as going to the corresponding measure, click on properties, go to Data Format, select custom format and paste in the following code,

#,##0;-#,##0;0

The problem with the above code is that it will not help you show decimal places, to show decimal places all you have to do is to change the code as below,

#,##0;-#,##0.00;0

note that I have replaced the second 0 as 0.00 which will make sure that data in the column will have two decimal places. Again places where there are null this doesn't apply and will show up as 0 instead of 0.00. So to correct this all you have to do is to replace the value after the second ; as 0.00 as below,

#,##0.00;-#,##0.00;0.00

This will ensure that null values are replaced by 0.00 and not 0. Also not null values will have two decimal points. 

Let us know if this worked out for you. Works well for OBIEE 12c. 

Connect to a Pluggable Oracle 12c Database

Scenario: Trying to connect to a pluggable database in Oracle 12c from a Client like SQL Developer. To connect from a remote client, we would require the following details,

Host name: This is the host name of your database server

Service name:  This is the service name of your database. 

Port number: The port number to connect to database.

The host, port and service name can be found by going to the app folder. That is you have to navigate to app > oracle > product > 12.1.0 > dbhome_1 > NETWORK > ADMIN > tnsnames.ora

Inside the tnsnames.ora file you can find a entry to your server installation as below, 

Click to Enlarge

from this you can copy details to your SQL Developer or other connections, 

This connection remember will be your container database. To find the names of your pluggable database you can run the following query,

SELECT NAME, OPEN_MODE FROM V$PDBS;

Now if the pluggable database is Open status, you can use the same connection details replaced with pluggable database name in the connection as below,

i.e only the service name differs from the container database. 

Start Oracle 12c pluggable database : PDBORCL

So with a default installation of Oracle 12c, you get a container database as well as a pluggable database. The default name for the pluggable database is 'PDBORCL'.

For those of who you have installed Oracle Business Intelligence 12c would know that the metadata schemas for 12c cannot be installed on a container database. You will need a pluggable database to store these metadata schemas. With default installation you get a pluggable database by the object name 'PDBORCL'. So if you haven't written a database trigger statement to start your pluggable database on Oracle 12c what happens is that only the container database gets started.  The result is that the web service is not able to connect and gather metadata from these two schema's and startup fails. 

So here is how to check the status of your pluggable database and how to mount/open it. To check the status of the pluggable database, first open sqlplus,

once you have opened sqlplus login as the SYS user and once connected run the following query,

SELECT NAME, OPEN_MODE FROM V$PDBS;

Status of Pluggable databases.

This will give you the list of pluggable databases on your 12c installation. To startup or mount the same, you have to run the following command,

ALTER PLUGGABLE DATABASE ALL OPEN;
or
ALTER PLUGGABLE DATABASE PDBORCL OPEN;

Pluggable Database open command.

Now you can see that the status has changed to read write.

DW Concepts: What is a Factless Fact table ? Usage and Examples

So any new BI/ETL developer starting out his career would come across this term, Factless Fact. So what is a factless fact, Let us see with the help of a few examples,

A Factless fact table is defined by Ralph Kimball as,

Fact tables that have no facts but captures the many-to-many relationship between dimension keys.

A simplified definition would be

A table in Data warehouse capturing the relation between two or more entities and doesn't have any measurable quantities or facts.

Imagine the example of a school- class - student - stream enrollment scenario where one row captures information of a student enrolled to a particular class.

ROW_WID	CLASS_WID	STUDENT_WID	STREAM_WID
1	30	25	1
2	31	31	1

As you can see from the above table structure, this table captures or the grain( lowest level of detail ) is enrollment details of a particular student assigned to a class and a particular stream. The WID columns are nothing but surrogate keys of individual dimensions and ROW_WID column is the surrogate key of the fact. 

This table as you can see doesn't contain any measurable entities like Marks. This table acts as an entity to identify the students enrolled in a particular class. and stream We can create derived measures from this fact by taking count of individual ROW_WID items, for eg:

1. Taking count of students(STUDENT_WID) grouped by Class will give you the number of Students assigned to a class. 
2. Taking count of students(STUDENT_WID) grouped by Stream gives you the number of students under a single stream. 
3. Taking the count of classes(CLASS_WID) grouped by Stream gives you the number of Classes under a particular Stream.

The star schema diagram for this factless fact is below,

Student Enrollment Factless Fact Schema Diagram

Naturally you will doubt that same information can be obtained from other fact tables such as marks, This is true, however imagine a scenario where a student no longer attends school and has not attended exams. In such a case there are chances that his mark details are not present in the marks fact table. Hence you will not be able to get accurately the information of all students assigned to a class or stream. This is where a factless fact table is significant in a Data warehouse.  

Other similar examples of factless fact tables are, 

• Insurance - Coverage - Membership
• Sales - Product Promotion Details 

Starting OBIEE 12c from Command Prompt

You can directly start the OBIEE 12c services by going to the Middleware home folder and to the path below, 

Oracle_FMW/user_projects/domains/bifoundation_domain/bitools/bin

Now open a command prompt window, Shift + Righ Click and then type the filename start.cmd. This will start the execution of OBIEE 12c services. 

Alternatively you can run each component individually as it was done previously for OBIEE 11g.

OBIEE 12c Client Administration tool OCI DB connectivity issue

Issue: We were trying to make a connection to Oracle database to import metadata to the OBIEE repository.

Cause: The tnsnames.ora file not present in MW_HOME/network/admin

Resolution:  Even if you have tnsnames.ora in your app folder in your database client folder, you need to have a copy of the same tnsnames.ora file at network/admin folder inside MW_HOME folder created by OBIEE.

So copy the tnsnames.ora file to this location or create a new one and try restarting the Administration tool before trying again. Let us know if you are still facing issues in the comments. 

OBIEE/Weblogic 12c Node Manager Startup Error - DemoIdentity.jks file missing [Solved]

Here is an issue we faced while trying to startup the Node manager in Oracle Business Intelligence 12c when we trying to start the nodemanager service. The issue is caused by a java keystore file missing from a location inside middleware home folder. 

Issue: DemoIdentity.jks keystore file missing from the security folder inside nodemanager folder, i.e at the path

 FMW_HOME/oracle_common/common/nodemanager/security

weblogic.nodemanager.common.ConfigException: Identity key store file not found: Middleware/Oracle_Home/oracle_common/common/nodemanager/security/DemoIdentity.jks Cause: The DemoIdentity.jks file missing from the security folder for nodemanager.

Resolution:

The DemoIdentity.jks file would be available at the folder,

FMW_HOME/user_projects/domain/bifoundation/security

Copy the file from this folder to inside the secuirity folder for NodeManager. Once you have copied the file, try restarting the nodemanager. It worked well for us.

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 does the term RPD file refer to in OBIEE 11g ?

If you are new to OBIEE, you might be wondering what the term RPD stands for. Most developers refer to RPD as the repository file, while some people refer to it as Rapid File Database. There are also other names like Repository Design, Repository Dictionary etc. The truth is that there is no definition for the abbrevation in any Oracle documentation. However the most commonly used definition would be Rapid File Database.

Sample App Lite RPD that comes default with OBIEE Installation.

The RPD file or the repository file is the physical metadata file for OBIEE server where relationship between Datawarehouse tables and their related objects are stored. 

ODI - Different schemas for Target and Staging(Work) Explained

It's a very common practice to keep the ODI Target and Work Schema to reside in one database. All the temporary objects created by the Knowledge modules are thus created inside the schema as the Target data store. Is this a good approach ? 

It is Oracle recommended to keep the temporary objects created by the Knowledge Modules in a separate schema rather than keeping them along with the target schema. A dedicated work schema would help you organize and distinguish your temporary objects from actual target tables. Bringing the temporary objects under one schema means that you can easily drop the objects from the work schema. 

Points to remember

• Always better to define Work Schema separately in the Topology rather than for each Interface. 

Work Schema definition in Topology.

• Always define the work schema separately from target schema for ease of organizing and managing your work schema.

You can see it here in this Oracle Data Integrator Best Practices - White Paper that clearly says to define a dedicated schema for Work/Staging.