5 Replies Latest reply on Sep 7, 2018 2:28 PM by skull

    Bringing data from 2 different data sources






      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?



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

        • 1. Re: Bringing data from 2 different data sources
          Gianni Ceresa


          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?

          1 person found this helpful
          • 2. Re: Bringing data from 2 different data sources
            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.

            1 person found this helpful
            • 3. Re: Bringing data from 2 different data sources

              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).


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



              • 4. Re: Bringing data from 2 different data sources

                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.

                • 5. Re: Bringing data from 2 different data sources

                  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 _/\_