Multiple SQL statements per one query

building semantic layer
1

Hi there–

I’m working as a Designer on a universe, and just added a new table to the universe structure. The rest of the universe is rather complicated (multiple aliases), but I’ve joined this new table, using a very simple join, to only one other table. It parses correctly. I saved the universe, went to test it using a query in Business Objects. I’ve started with a very simple query - just one object each from each table on either side of the join. But, when I look at the SQL, I see that Business Objects has generated two SQL statements. Is there any way to bypass this behavior? It doesn’t seem like I can edit the SQL, and I’d like to control this at the Designer level, if possible.

I checked the FAQ at tech.busobj.com, and found the following:

Q. Why does BusinessObjects generate two SQL statements for some queries?

A. BusinessObjects does this to ensure that the returned results are always correct. This technique is used to support complex queries. This can be explained using the following query on the demo database. Select Customer Name, Quantity Loaned, and Quantity Ordered. BusinessObjects will split the query into two independent queries and combine the results, i.e. Customer Name, Quantity Loaned and Customer Name, Quantity Ordered.

But, my query was simple, albeit in an otherwise complicated universe. Although, interestingly, if I create a brand new universe with just the two tables in question, the join works fine. Any ideas?

Thanks very much,
Amy Miller

====================================
Amy A. Miller, Data Analyst
University of Pennsylvania ISC
Office of Data Administration
3401 Walnut, Suite 265C /6228
Philadelphia, PA 19104-6228
Phone: 215-898-2171 Fax:215-898-0386
Email: milleraa@isc.upenn.edu

Listserv Archives (BOB member since 2002-06-25)

2

Amy,

Does your universe contain contexts?? If your universe contains contexts, that could be the culprit.

Maria D. Carter :slight_smile:
BusinessObjects Developer
(336) 279-2242
mcarter@gbncmail.ims.att.com

From: Amy A. Miller [SMTP:milleraa@ISC.UPENN.EDU] Sent: Tuesday, August 11, 1998 2:41 PM

Hi there–

I’m working as a Designer on a universe, and just added a new table to the
universe structure. The rest of the universe is rather complicated (multiple aliases), but I’ve joined this new table, using a very simple
join, to only one other table. It parses correctly. I saved the universe,
went to test it using a query in Business Objects. I’ve started with a
very simple query - just one object each from each table on either side of
the join. But, when I look at the SQL, I see that Business Objects has
generated two SQL statements. Is there any way to bypass this behavior?
It doesn’t seem like I can edit the SQL, and I’d like to control this at
the Designer level, if possible.

I checked the FAQ at tech.busobj.com, and found the following:

Q. Why does BusinessObjects generate two SQL statements for some queries?

A. BusinessObjects does this to ensure that the returned results are always
correct. This technique is used to support complex queries. This can be
explained using the following query on the demo database. Select Customer
Name, Quantity Loaned, and Quantity Ordered. BusinessObjects will split the
query into two independent queries and combine the results, i.e. Customer
Name, Quantity Loaned and Customer Name, Quantity Ordered.

But, my query was simple, albeit in an otherwise complicated universe. Although, interestingly, if I create a brand new universe with just the two
tables in question, the join works fine. Any ideas?

Thanks very much,
Amy Miller

====================================

Amy A. Miller, Data Analyst
University of Pennsylvania ISC
Office of Data Administration
3401 Walnut, Suite 265C /6228
Philadelphia, PA 19104-6228
Phone: 215-898-2171 Fax:215-898-0386
Email: milleraa@isc.upenn.edu

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
For old 3.1 info:
Penn: Page not found
Search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

3

Amy

Go to the Supervisor module, Universe tab, right mouse click on the universe in question and click on properties - under the SQL tab you will see a section for multiple Paths - you can uncheck these options and one SQL statement will be generated; However, multiple statements are suppossed to maximize processing. BO generates multiple statements if you have multiple measures or contexts in your query if these options are checked.

Julie

Listserv Archives (BOB member since 2002-06-25)

4

I have recently run across this same problem and it took a team of 4 people (newbies) about a day to solve. If you’ve had this problem, please patiently read on:

The issue here lies in contexts, as one other reply said (but didn’t elaborate). Contexts are rather poorly described in the help, so I thought it would be helpful to add a reply even after all these years.

As you may have read, contexts are BO’s way of telling how to drill in universes that have loops. When you describe a context in a loopy universe, you show BO a tree in an otherwise connected network.

Initially in a universe there may be no loops, so the default context is just the whole tree. As the universe gets more and more complex its author finally gets to the point, where loops form and some contexts must be described. He/she does this and leaves the universe in the new state.

Along comes a newbie and adds a table or two to the universe and even defines some useful joins, which he/she tested to work in SQL directly on the database. The problem is, that BO does not know to which context it should assign these new joins. So it does the dumb and easy thing: it DOESN’T include the joins in ANY context!!

Making a new report using the queries will now produce multiple select statements instead of one, because BO thinks that the new tables are part of a different context than what already exists. Remember, context=join-tree, so no context means no defined tree to drill. This only happens in universes with contexts already defined.

What you have to do to make the new join part of the context is to add it to the existing contexts… Here is where it gets tricky for me. I have no idea how to add the new joins to a context without just plainly creating a brand new context.

NOTE: You may have to include in the new context all or some of the new joins manually, as they may not be detected in the standard context-forming procedure.

The issue of making new contexts I leave as an exercise to the student :wink: It’s a mess even in the newest version of BO.

Janimalius (BOB member since 2004-11-22)

5

It does the only it thing it can, I’m sure you would be very upset if it randomly added new joins to existing contexts, and as you said context detection is far from perfect. We humans define silly data models some times that don’t always make sense to computers :stuck_out_tongue:

To alter a context go to list view in designer and select the context you want to change, right click the context name select “context properties…” then in the list of joins you can click individual joins in and out of that context.

ken.gaul :uk: (BOB member since 2002-06-18)

6

Make sure that you are not on 6.1a otherwise in List Mode the bug will end up deleting some joins :wink:

Kashif Saeed :pakistan: (BOB member since 2004-06-02)