Different results with same queries

building reports desktopintelligence
1

Viet-Toan,
It is possible that the number of rows is different from a limit that you have set on your database. If your database has a ‘TIME’ limit that is hit before a ‘Row limit’ then there is the most likely culprit.

The fetch size will aid in determining how many rows can be returned in the period of time before BO just stops accepting the data. If you have a large fetch size set and the time limit is hit before all of the rows for that fetch are filled, the data from that set is not returned.

How does this relate to your issue? This can be a problem if there is heavy network traffic or other data transfer delays that would cause the fetches to take more time to return data.

Try removing the time limit and seeing how many rows are returned. Any time the number of rows is hit before the time limit, you have the chance of having different result sets. The best thing you can try for is getting all of the data back and making sure you do not have the ‘Partial Results’ warning flashing. This will get you the correct results, every time.

Thanks!
-rm

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

2

Hi,

Some of our users reported us a strange behaviour of BO v4.1.2. on MS SQL Server v6.5
I have run two exact same queries in a report: One brought me back 24163 rows
The second one 24764.

As a result, I got two different revenues for identical queries!!!

I have tested it also with WebIntelligence and had a 3rd different result…

Has anyone experienced that? Can this come from BO or from the SQL Server?

Thanks
Toan Nguyen

Viet-Toan Nguyen nguyent@sugar-land.dowell.slb.com Schlumberger Dowell
SHPC-D DSC/MIS Software
CSNE Engineer - Ecole Centrale Paris
Tel: (281) 285-7814
Fax: (281) 285-4706

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

3

Toan Nguyen wrote:

Some of our users reported us a strange behaviour of BO v4.1.2. on MS SQL Server v6.5
I have run two exact same queries in a report: One brought me back 24163 rows
The second one 24764.
As a result, I got two different revenues for identical queries!!! I have tested it also with WebIntelligence and had a 3rd different result…
Has anyone experienced that? Can this come from BO or from the SQL Server? --------------------------------------

Did you verify that the SQL was the same or different for each query. If the SQL was the same then it may be more with your database server than BusinessObjects. If the SQL is different you may want to make sure that both sites are using the same version of the universe. It seems that on occasion, BusinessObjects won’t automatically update the universe like it should. Also it was previously mentioned you may have hit the time limit in BusinessObjects. This can be verified one by checking to see if you have a yellow triangle in the lower right hand corner of the screen. This means a partial result was returned. Also if you go into view data, and choose the definition tab it will tell you how long the query ran, this can be compared to the limits, also on that same screen.

Glenn Fredericks
Data Warehouse Specialist
glenn_fredericks@aal.org
(920) 730-4700 x4236 or 1-800 CALL AAL

Aid Association for Lutherans
4321 N. Ballard Road
Appleton, WI 54919-0001
Visit our Web site at www.aal.org or e-mail us at aalmail@aal.org

AAL… Financial services. Lutheran heritage. A powerful combination.

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

4

I am in the middle of fighting that problem right now. What I have discovered was if I have my BusObj connection set to asynchronous mode, I see what you are seeing.

If I set the connection to synchronous mode, all is well. I get the correct results- consistently.

Now the bad news. Running a query, and my mode is set to synchronous mode, I can NOT start MS ACCESS! I get an hour glass, than it goes away. In the task list I see it running, but I can NOT get at it. As soon as my query finishes- up pops Access!

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

5

Viet-Toan Nguyen schrieb:

Hi,

Some of our users reported us a strange behaviour of BO v4.1.2. on MS SQL Server v6.5
I have run two exact same queries in a report: One brought me back 24163 rows
The second one 24764.

As a result, I got two different revenues for identical queries!!!

I have tested it also with WebIntelligence and had a 3rd different result…

Was somebody working with the database??

W.

DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

6

I tried different fetch sizes, that made no difference. I am now testing using a difference ODBC driver- one from Intersolv, and that works! I will be calling on our goods friends at BusObj Tech support this afternoon.

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

7

Yes, Duke, I have set my connection to synchronous mode and it looks like it solved my problem.
How can it be explained? Is it related to the fetch size? Concerning your Access problem, I had not similar one but sometimes it locks the database when accessing it and noone else can connect to it…

Thanks.
Toan

Viet-Toan Nguyen
Schlumberger Dowell
SHPC-D DSC/MIS Software
CSNE Engineer - Ecole Centrale Paris
Tel: (281) 285-7814
Fax: (281) 285-4706

Sent: Friday, July 24, 1998 9:12 AM

I am in the middle of fighting that problem right now. What I have discovered was if I have my BusObj connection set to asynchronous mode, I
see what you are seeing.

If I set the connection to synchronous mode, all is well. I get the correct
results- consistently.

Now the bad news. Running a query, and my mode is set to synchronous mode,
I can NOT start MS ACCESS! I get an hour glass, than it goes away. In the
task list I see it running, but I can NOT get at it. As soon as my query
finishes- up pops Access!

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

8

Toan and Duke,
what are your configurations? BusinessObjects version, database type and version? Also , is this problem easily repeatable? The reason I am asking is because I recently switched our connections to asynchronous, for the reason you mentioned about starting other applications. Also running in asynchronous mode allows you to cancel the query at any point not just in the fetch phase. I would like to test this on our site to see if it restricted to a particular database or if it is more generic. Thanks

Glenn Fredericks
Data Warehouse Specialist
glenn_fredericks@aal.org
(920) 730-4700 x4236 or 1-800 CALL AAL

Aid Association for Lutherans
4321 N. Ballard Road
Appleton, WI 54919-0001
Visit our Web site at www.aal.org or e-mail us at aalmail@aal.org

AAL… Financial services. Lutheran heritage. A powerful combination.

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

9

I am using the latest ODBC SQL server driver- 2.65.0252. I had the same problem using BusObj 4.1, and now with 4.1.2a. The database is on a NT server, SQL Server is 6.5. I would test a query and view the results in the data manager window. I can see there the date, time and rows information. I then close BusObj, open Supervisor, and change the mode in the connection. I then re-start BusObj, open the previous saved report go into data manager, and do a re-fresh. With Async, I get the wrong results (verified with another application). In sync- I always get the correct results, or using a 3rd party ODBC SQL Server driver I also get the correct results.

I hope you are not getting wrong results since the switch! Let me know what you find out!

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

10

I am using the same configuration as Duke, except that I do not have the 3rd party ODBC SQL Server driver…

Duke, did you get any answer/explanation from BO Tech support?

Toan

Viet-Toan Nguyen
Schlumberger Dowell
SHPC-D DSC/MIS Software
CSNE Engineer - Ecole Centrale Paris
Tel: (281) 285-7814
Fax: (281) 285-4706

Sent: Friday, July 24, 1998 2:41 PM

I am using the latest ODBC SQL server driver- 2.65.0252. I had the same
problem using BusObj 4.1, and now with 4.1.2a. The database is on a NT
server, SQL Server is 6.5. I would test a query and view the results in the
data manager window. I can see there the date, time and rows information.
I then close BusObj, open Supervisor, and change the mode in the connection.
I then re-start BusObj, open the previous saved report go into data manager,
and do a re-fresh. With Async, I get the wrong results (verified with another application). In sync- I always get the correct results, or using a
3rd party ODBC SQL Server driver I also get the correct results.

I hope you are not getting wrong results since the switch! Let me know what
you find out!

Sent: Friday, July 24, 1998 2:22 PM

Toan and Duke,
what are your configurations? BusinessObjects version, database type
and version? Also , is this problem easily repeatable? The reason I am
asking is because I recently switched our connections to asynchronous, for
the reason you mentioned about starting other applications. Also running
in asynchronous mode allows you to cancel the query at any point not just
in the fetch phase. I would like to test this on our site to see if it
restricted to a particular database or if it is more generic. Thanks

Glenn Fredericks
Data Warehouse Specialist
glenn_fredericks@aal.org
(920) 730-4700 x4236 or 1-800 CALL AAL

Aid Association for Lutherans
4321 N. Ballard Road
Appleton, WI 54919-0001
Visit our Web site at www.aal.org or e-mail us at aalmail@aal.org

AAL… Financial services. Lutheran heritage. A powerful combination.

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’

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)

11

YES!
BusObj Tech support told me that this is a known (would be nice to let us know) bug with the MS SQL Server ODBC driver. The fix is to set the fetch to 1. It works, but it takes a bit longer to return data.

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