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
