3 Replies Latest reply on May 2, 2016 1:51 PM by Fritz Skinner

    OBIEE 10g Help with query construction?

    Fritz Skinner

      Hello, experts.

       

      I'm hoping you would offer your ideas and suggestions around some interesting behavior we're seeing.

       

      It's OBIEE 10g; out of the box (with some mods) OBIA for Purchase and Spend and Financials.

       

      The behavior is this: we can create two analyses (maybe answers is the term in 10g) with the exact same fields.  Call them A and B.  The only difference between the analyses is the order of the columns.

       

      If the Time dimensional field leads, the query returns results successfully.

       

      If the Time dimensional field is not the lead field ("B" query), an incorrectly defined logical table source error is thrown.  It is: [nQSError: 15018] Incorrectly defined logical table source

       

      A (successfully returns):

      SELECT Time."Month" saw_0, "Supplier Account"."Supplier Account Name" saw_1, ' ' saw_2, '' saw_3, "GL Account"."GL Account Number" saw_4, Fact."Received Amount" saw_5, Fact."Received Quantity" saw_6 FROM "Procurement and Spend - Purchase Orders" WHERE (Time."Year" = '2015') AND ("Supplier Account"."Supplier Account Name" = 'XXX INC') ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4

       

      B (error generating):

      SELECT "Supplier Account"."Supplier Account Name" saw_0, ' ' saw_1, '' saw_2, Time."Month" saw_3, "GL Account"."GL Account Number" saw_4, Fact."Received Amount" saw_5, Fact."Received Quantity" saw_6 FROM "Procurement and Spend - Purchase Orders" WHERE (Time."Year" = '2015') AND ("Supplier Account"."Supplier Account Name" = 'XXX INC') ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4

       

      In "B" queries, OBIEE's "Navigator" decides to use a logical fact table which doesn't really make sense in the context of this query -- and results in an error.  In "A" queries, the navigator designs a physical query which successfully runs and fits our dimensional fields.

       

      So I'd like to see what's happening in OBIEE's "Navigator".  However, I'm not sure how I might trace or investigate the logic used to generate the query.

       

      Do you have any ideas of how I can do that?  Any help is greatly appreciated.

       

      I found the link below helpful in understanding more about this mysterious-to-me "Navigator" process.

      Inside the Oracle BI Server Part 2 : How Is A Query Processed? - Rittman Mead Consulting

       

      Excerpt which seems most relevant:

      4. If the cache can’t provide the answer to the request, the request then gets passed to the Navigator. The Navigator handles the logical request “decision tree” and determines how complex the request is, what data sources (logical table sources) need to be used, whether there are any aggregates that can be used, and overall what is the best way to satisfy the request, based on how you’ve set up the presentation, business model and mapping, and physical layers in your RPD.


      Thanks much.

      Fritz

        • 1. Re: OBIEE 10g Help with query construction?
          SriniVEERAVALLI

          Check the Content tab settings for that fact.

          • 2. Re: OBIEE 10g Help with query construction?
            Thomas Dodds

            It's OBIEE 10g; out of the box (with some mods) OBIA for Purchase and Spend and Financials.

             

            What does the same query do when you run against the no-mod original OBIA RPD for the same 2 queries .... in essence, what mods are done that causes this not to work?  OR it never worked OOTB this way, and now you want to build something that works this way.

            • 3. Re: OBIEE 10g Help with query construction?
              Fritz Skinner

              Thanks for weighing in, guys.  Specifically, I'd like to know if there is a way to trace through the decisions the navigator makes.  I doubt there is.

               

              Generally as it relates to this specific behavior we encountered, the logical level is set up corrrectly.  For example, the content level and logical joins are correct. However, the measures map back to a fact table which is not linked to a certain dimension. 

               

              That's a separate issue from the one I'm interested in -- which is how to troubleshoot/trace the decisions the navigator makes.  Maybe that's un-necessary and all my focus should be put on ensuring the logical and physical tables used in the analysis are related correctly.  :-)

               

              Cheers.

              Fritz