I have a pretty simple query, or so it seems, but WebI is doing the Full Outer Join with two queries, I don’t understand why, 1st screen shot.
Shown are the two queries - - - all the joins are there and such, but one is doing count(prospective_students.id) and the other does max(nominations). So not sure why it’s doing the two queries.
If I get rid of the Max(nominations.charged), and add in nominations.charged, it’s just one query. If I get rid of the count(prospective_students.id) and keep max(nominations.charged), again it’s one query.
then it does two queries, and the first COUNT query ignores the necessary join to get MAX(NOMINATIONS.CHARGED).
then if I don’t do MAX, I get one query using
NOMINATIONS.CHARGED and
COUNT(DISTINCT PROSPECTIVE_STUDENTS.STUD_ID)
and it DOES the necessary join to get NOMINATIONS.CHARGED.
So the only difference is MAX(NOMINATIONS.CHARGED).
The screen shots in my original post show the results.
If I take the WebI query with MAX(NOMINATIONS_CHARGED), go to Oracle SQL, and then add COUNT(DISTINCT PROSPECTIVE_STUDENTS.STUD_ID), the query works fine.
One tiny thing I noticed - in Universe Designer, MAX(NOMINATIONS.CHARGED) shows the associated table, whereas another object of simply NOMINATIONS_CHARGED does not.
And I’m stumped … I’m not asking for troubleshooting per se, but what is WebI thinking? oracle handles it fine but WebI for some reason is doing it with two queries.
There is one universe parameter (don’t remember its name) that, when used, causes that WebI generates one SQL statement for each measure. Check whether it’s used in your universe.
Although our team decided to keep it checked as “Multiple SQL Statements for each measure.” So we have the two SQL statements, but it provides the expected results and performance is ok / fine.
Technically, it’s “Multiple SQL statements for each AGGREGATE”. One is a measure and one is a MAX(dimension).
Carefully check the results. It seems to me that the 2 measures use 2 different tables. If the two tables are at a different level of details, you could get incorrect result for one of the measures.
If there are no contexts in your universe (and I think there should be contexts) then having the “Multiple SQL Statements for each measure” parameter turned on makes perfect sense.
I have a related question if you don’t mind, it’s relevant (to me) on this discussion of “two queries when there are two measures”.
The thought occurred to me today that the “two queries when there are two measures” is a construct of a multi-dimensional database / Universe (of which I have very little knowledge).
I am a old geezer relational developer using a relational Universe, and now with three years under my belt with WebI, Universe Design, etc.
So - since Oracle can quite easily handle two measures (aggregates) in the same query (and it makes perfect sense [logically and technically]), is my hunch that “two queries when there are two measures is a function of a multi-dimensional database” correct?
FYI - I’m interested in learning about multi-dimensional databases / Universe design but I a totally a newbie, but I have heard of them …
Basically, I’m trying to understand why WebI does this two query thing, it’s a new concept to me.
Grab a coffee and have a read through this blog post - it will explain about chasm traps and the potential to get very wrong results by not using contexts:
Mark P - great, thank you. I did study this when I first started with Business Objects, but it was all new to me. Now after some experience it might make more sense.
If I had to re-write our Universes, I would say “No contexts” and build a mini-data warehouse via Oracle views, thus (in my guesstimation) eliminating contexts. But I suppose I should look at the power of contexts first. Basically - I think we’re doing it wrong, and so it’s terribly confusing, because the contexts don’t make logical sense.
But thanks, I think we can call this a wrap and I’ll post other topics to further my knowledge.
Please, please, please, try and understand that contexts are one of the most powerful tools in your BO locker. They prevent users getting massively wrong results and writing incorrect reports.
Ok, Ok! All I know at the moment is that our contexts are confusing and very undisciplined in our implementation. So I need to study it more and hopefully it will click and then I’ll be off to the races.
The key thing to contexts is getting your cardinalities correct.
Set them all manually, then you can simply use the detect contexts button - it’s worked for years (since 5.1 in 1999/2000) but don’t detect cardinalities because that has proved less accurate.
(BOB member since 2015-06-18)
(BOB member since 2003-11-27)
(BOB member since 2015-06-18)
(BOB member since 2015-06-18)