Oracle Analytics Cloud and Server

Bringing data from 2 different data sources

Received Response
22
Views
5
Comments

Hi,

OBIEE  11.1.1.9.0

I have 2 different Oracle databases.  APS database has the Employees information and O&M has the facts.  I have created a physical join between "F1 CR ALL DETAILS" to "Employees" on EMPLOYEE_NUMBER being the key.  In BMM, I brought both D10 Employees and F1 CR ALL DETAILS in a single BMM and so also in the Subject Area.  I verified the joins came over in BMM.  However when I run the analysis in OBI, the Employees view does not expand.  The circle next to it just keeps spinning.  I have no issues querying F1 CR ALL DETAILS.  Is this the correct way to bring data from multiple data sources?

pastedImage_1.png

As always, thanks for your help and the guidance you all provide.

Answers

  • Hi,

    In theory it's supposed to work, but keep in mind that the BI server is doing the join, so it's definitely slower than if the database was doing things directly.

    If you look at the logs what do you see? Do you see the 2 queries sent to the physical database and the results joined together by the BI server?

  • Thomas Dodds
    Thomas Dodds ✭✭✭✭✭

    Federate your data physically... using a logical layer for integration is good for POC/discovery work, but if in a production mode physically co-locate the data.  

    One of the issues with the logical cross-database joining is the fact that the predicate filtering will get done on the logical end, so physically you will move ALL the data to the BI server where it will stitch the data together - and it will do it row-by-row non-indexed on a data set larger than you really need. 

    That's inefficient.  You will always trade performance for flexibility.  If you want logical cross-database joining - you'll pay in performance.  If you want performance - federate enough of the data that the physical SQL on both databases will run the predicate filtering - then BI server will only have to stitch the final result-sets.

  • @Gianni Ceresa thanks for your reply.

    I don't even get to select the columns and see what the query is.  When I click on the Employee view, the circle next to the folder icon keeps spinning and never opens for selecting the columns (screen shot below).

    pastedImage_0.png

    So right now I am not much concerned about the performance - I just want to see it work :-)

    Thanks.

  • @Thomas Dodds thanks for your reply.

    Like you suggested, first I was thinking about creating a MVIEW in the O&M database but wanted to give this a try as we have couple more disparate sources and thought it would be helpful.

    Looks like from your & Gianni's reply, even if it works, wouldn't be a very efficient way to do it.

  • @Gianni Ceresa & @Thomas Dodds - Thank you both for confirming that it is supported & should work bringing data from 2 different data sources.  I just started all over again and was able to bring the data I wanted.  Not sure what is messed up with the RPD I was working in.  Appreciate the service you folks provide to OBIEE community.

    So that I can close this topic, I will mark Gianni's reply as the correct answer as he responded first   Please no hard feelings Thomas _/\_