There's a huge number of things playing a role there
a) DENSE vs SPARSE
b) How and where is the LOOKUP used
c) Is the lookup table in the same physical layer parent as the calling object
d) Or is there even a jump across technologies necessary (e.g. Essbase cubes doing lookups in DB tables or XML files doing lookups in Hadoop)
e) What kind of functions and functionalities does the data source support (or data source*S*)
f) What DB features are enabled in the RPD (not what can the source do but what do you allow it to do)
g) Is there some post-calculation required
h) Does the model itself do things like snowflaking or framenting (and you basically kick in with the lookup after those facts)
Impossible to tell you precisely what is what without actually sitting in front of your solution
Hi Christian - Thanks for your reply, so is it fair to assume that based on the code/solution that we have implemented OBIEE engine will decide by itself how to generate the query for look up and it can't be influenced by any parameter settings in OBIEE (excluding point no f).
Just to add to the info the scenarios i was talking about were all within Oracle database and were simpler like fetching descriptions based on ID's from look up tables. There were no cross technologies reporting or fragmentation. I should have mentioned these in my original post.
My main intention was to confirm if there is any specific setting or parameter which can enforce separate/ stitched queries for lookups.
No "one parameter", no. Sorry.