Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Cross Database Joins

Posted by Venkatakrishnan J on March 19, 2008

One of the advantages of BI EE is its ability to make cross database joins. Currently, BI Server component of BI EE supports database joins across any database. This feature is primarily meant for joins between a database and some lookup tables coming from Excel files. Though this would work for databases too, its not recommended to use them in a production environment since BI EE does the joins in memory by extracting the data from the source databases first. We will see how to go about creating a simple cross database join today. There is not much difference in the way we actually set up the repository. But still i thought this deserves its own blog entry since its a very unique feature of BI EE. We shall simulate this using 2 simple tables. One table (EMP table of the normal Oracle SCOTT schema) would be from Oracle and the other table would be from an Excel file. For demonstration purposes we shall be exporting the normal DEPT table from the SCOTT schema in the form of an excel file. So, basically we have 2 tables.

EMP Table in an Oracle Database
DEPT Table in an EXCEL file

Our final aim is to create a simple report in Answers with data coming in from both the EMP table and the DEPT table. In order to do that the first step is to import the EMP Table from the Oracle Database into the repository.

      

Similarly, import the Excel file (which is basically an export of the DEPT table) using ODBC.

      

      

So, our repository should basically have 2 tables in 2 different databases as shown below.

      

Once this is done, the next step is to create a join between these 2 tables in the physical layer.Typically, if you go to the physical layer diagram, it would allow joins only between tables in the same database. In order to achieve cross-database joins, the first step is to right click on the EMP table and then click on Physical Diagram -> All Objects and Joins.

      

In the same way (ensure that the physical Diagram window is open), right click and click on view physical diagram on the DEPT Excel table source. That will automatically bring both the tables inside the physical layer.

      

Now, create a database join between both the tables using DEPTNO as the join column.

      

Once this is done, save the changes made in the repository. Now, create a new Business Model Cross DB join. Drag and drop both the tables (DEPT and EMP) from the physical layer into the Cross DB join Business Model. Then right click on Cross DB Join and click on view the Business Model diagram.

      

Similar to what we did in the Physical layer, create a foreign key join between DEPT and the EMP table based on DEPTNO column.

      

Once this is done, drag and drop the Business Model into the Presentation layer and save the repository. Ensure that your repository is clear of any warnings or errors. Once this is done, go to Answers and try creating a simple report having DNAME and SAL as thecolumns.

      

As you see, we have now created a report that contains data from 2 different data sources. The BI Server does the joins in the background. To see what exactly the BI Server, is doing, lets look at the logs to get the exact SQL. You would basically see 2 SQLs. They are

select T7105.SAL as c1,
T7105.DEPTNO as c2
from
EMP T7105
order by c2

and

select T7088.”DNAME” as c1,
T7088.”DEPTNO” as c2
from
“‘Export Worksheet$'” T7088

      

So, what BI Server basically does is it fetches the data from both the databases using separate queries and then makes the joins in memory at run-time.

About these ads

One Response to “Oracle BI EE 10.1.3.3/2 – Cross Database Joins”

  1. Kubilay said

    Hi Venkatakrishnan

    Where is this interface in Answers?
    Where do I drag the tables in this mode. I have installed Oracle OBIEE on Unbreakable Linux and can see Publisher, Dashboard and Answers. They all work!. Whenever I go to answers I only see as source the Paint and Paint Exec. Do I have to create Connection Pools to connect to different databases and do cross joins? How?

    My situation is like this:

    I have two databases Oracle and MS SQL Server. I want to write a query statement which will retrieve data from both databases and publish this data into one report. A cross-join as you say. That is I want to join/merge data from tables in both database into one report with one SQL Statement something like:

    Select o.F1,o.F2, m.F1,m.F2
    from
    table@oracleServer o,
    table@microsoftSQLServer m
    where o.F1=m.F1;

    Can Oracle BI Publisher do this?

    Basically I want to query simultaneously two heterogeneous data sources in one SQL statement. Is there such a tool? I don’t want to build a warehouse by moving data from SQL Server into Oracle or vice-versa. I don’t want to duplicate data. I want to be able to query data at its source via one SQL statement.

    How would one go about doing such a thing?

    Oracle Forums people, kindly advised me to do it with OBIEE but to watch out the performance issues. My cross-joined tables will probably be big. Millions of rows and I will try to crossjoin 2 tables from Microsoft SQL Server and Oracle and create monthly reports. What do you think? Will OBIEE be performant enough or will I have to use ETL and proper datawarehousing in one database. I can answer these question myself once I crack Oracle BI Answers to do the crossjoin, but I thought your opinion would be very valuable to me.

    Please help me do the cross join. Your post is very good, exactly what I need but I can’t get to see how you start the import and where is the physical diagram tool.

    Thanks a million!

    Kubilay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: