3 Replies Latest reply on Feb 21, 2013 8:35 AM by 991087

    RE: Complex Join

      Hi Experts,

      I have few questions regarding complex joins

      1> First when we import the tables into the Physical layer we will do the FK Joins for the tables according to our requirement then in BMM layer we will create new business model next again why we have to make complex join in BMM layer already there is joins between the tables in physical layer which scenarios we have to make the Complex join if we won't do the complex join what will happens.

      2> Also if we do FK join in the place of complex join in BMM layer what will be the result.

      Thanks in Advance,
        • 1. Re: RE: Complex Join
          Hi ,

          Can i have any updates on this post.

          • 2. Re: RE: Complex Join
            Robert Angel

            a physical table can be used in a number of different business instances.

            Hence, you may have to alias the a single table numerous times to create all of the results that you need in the business layer.

            A logical table in the business layer may consist of numerous physical table sources, hence when you create a join in the business layer it may reflect numerous join relationships in the physical layer, but subsumed into a relationship as if between two entities.

            This is the reason that you have to manually create joins in the business layer, as OBIEE is a complex and flexible tool and will not always reflect a simple table to table join relationship in the business model join.

            Hope this helps,

            1 person found this helpful
            • 3. Re: RE: Complex Join

              we have two joins in OBIEE one is foreign key join and other one is complex join.Mostly 99% cases we use foreign key join in physical layer.we use complex join in phycical layer mostly in three scenarios.

              1)when there is extended join conditions.
              2)when we join key column of one table to non key column of other table.
              3)when the operator is other than equal to operator.

              In these 3 situations we can use complex join in physical layer.

              Next one is we have already joins in physical layer then what is the need to create complex joins in BMM layer right.
              The answer for this question is whenever user runs a report that will generate logical sql query our OBIEE understands only logical sql query this logical sql query generated based on the logical joins in BMM layer.
              Based on this logical joins only it will create most optimized sql query.And another reasons for creating logical joins is

              if we want to specify driving table it is possible only thriugh complex join in BMM layer.
              and if we want to specify type of join it is possible only thriugh complex join in BMM layer
              and if we want to specify cordinality it is possible only thriugh complex join in BMM layer

              and another one is my tool is not able to identify facts and dimensions because it has no intelligence,we need to incorporate the intelligence by giving logical joins only it identifies the facts and dimensions.
              so we have to create logical joins in BMM Layer..

              Give me Like,,if you aresatisified with my post.


              Sai Pelluri