4 Replies Latest reply: Jan 17, 2013 5:59 AM by 985471 RSS

    Fact - dimension and Fact Join

    user12078402
      Hi ,

      I have a issue in OBIEE 11g here ,

      I am trying to query two columns from 2 different fact tables F1 and F2

      the join between these tables is F1 - D - F2 , where D is the confirmed dimension.

      so when i am querying for

      f1.c1 , d.c1 , f2.c1 , its throwing me up with error

      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request


      Can you please help me with this issue , am i doing the correct join ??, can we query two columns from two diff fact tables?

      Thanks in advance.
        • 1. Re: Fact - dimension and Fact Join
          MKKrishna
          do you have hierarchies in place - If so set content level for that.

          http://108obiee.blogspot.com/2009/08/joining-two-fact-tables-with-different.html

          As work around,

          Try applying agg like (sum, or AVG) for f1.c1 and f2.c2 in RPD or in answers & check it. If your requirement is that simple it works well Else follow the above link.
          • 2. Re: Fact - dimension and Fact Join
            user248025
            Hi,
            By using complex join in both physical and business model layer

            i.e :
            Better to create a single fact in your BMM layer with two facts (f1 and f2). after that join the fact to the dimension (complex join)
            http://kpipartners.blogspot.com/2009/08/when-fact-tables-do-not-join-to-all.html

            http://108obiee.blogspot.com/2009/08/joining-two-fact-tables-with-different.html

            Thanks

            Deva
            • 3. Re: Fact - dimension and Fact Join
              user12078402
              Hi All,

              Thanks for your responses , I have tried creating hierarchy dimension and assgned levels to the fact sources but i still see the error

              and

              Combining 2 facts will resolve the issue but its not the case here as each fact table has about 25 dimensions connected to it , but there are only 2 confirmed dimensions between both facts and the problem here is user wants to see both the fact tables in the same subject area

              I have been trying but unable to get the solution.

              Can you guys please suggested any other way of achiving this task

              Thanks a lot
              • 4. Re: Fact - dimension and Fact Join
                985471
                http://108obiee.blogspot.in/2009/08/joining-two-fact-tables-with-different.html

                Edited by: haranadh on Jan 17, 2013 3:58 AM