Hi, I hope some one will be able to explain why BusinessObjects is working in this manner.
Instance 1: Version 4.1.1, Rdb 6, flat file schema, @prompt, not allowing multiple SQL statements for each measure. The universe was designed with identical @prompt statements on 5 measures. BusinessObjects would generate SQL with one @prompt statement for each measure used in the query. This resulted in queries with more than one measure running for 7+ hours. The @prompts were removed from the objects and replaced with one @prompt statement on the principle class (within Supervisor). The same queries which took 7+ hours before this change now run in 4 minutes. Why is BusinessObjects generating unnecessary SQL statements (ie: repeating duplicate where clauses)?
Instance 2: Version 4.1.3, Access97, flat file schema, universe contains aliases, allowing multiple SQL statements for each measure, no limit set on maximum number of rows, 10 minute time limit set. The universe contains 2 aliases. The main table holds the “total” (a combination of total area1 and total area2) and the load (a combination of load area1 and load area2). The database houses a relatively small number of records (393,000). Six measures where built as follows: * TOTAL: select “total” from main table
- LOAD: select “load” from main table
- TOTAL AREA1: select “total” from alias1, where area from alias1 = “1” * LOAD AREA1: select “load” from alias1 where area from alias1 = “1” * TOTAL AREA2: select “total” from alias2 where area from alias2 = “2” * LOAD AREA2: select “load” from alias2 where area from alias2 = “2” When all six measures where used in a query, the SQL generated by BusinessObjects repeated duplicate where clauses. EG. Select 1: WHERE alias1.area = “1” AND alias1.area = “1” Select 2: WHERE alias2.area = “2” AND alias2.area = “2” This query ran for 20 minutes before I cancelled it.
I then tried creating 4 aliases instead of 2 and built measures as follows: * TOTAL: select “total” from main table
- LOAD: select “load” from main table
- TOTAL AREA1: select “total” from alias1, where area from alias1 = “1” * LOAD AREA1: select “load” from alias2 where area from alias2 = “1” * TOTAL AREA2: select “total” from alias3 where area from alias3 = “2” * LOAD AREA2: select “load” from alias4 where area from alias4 = “2” When all six measures where used in a query, the query ran for 1 minute and returned partial results.
The only way I could get queries to run on this universe within an acceptable amount of time and to return full results was to create 2 aliases and have only 4 measures (as follows):
- TOTAL: select “total” from main table
- LOAD: select “load” from main table
- TOTAL AREA1: select “total” from alias1, where area from alias1 = “1” * LOAD AREA1: select “load” from alias2 where area from alias2 = “1”
Why is BusinessObjects returning partial results when no row limits are set? Why is BusinessObjects generating duplicate SQL (for identical where clauses)?
Any help you can offer would be greatly appreciated. Thanks in advance.
Best regards,
Crystal Golding
Senior Systems Analyst
Management Information Section
University of Queensland
Brisbane Australia 4072
E-mail: c.golding@mailbox.uq.edu.au
Phone: +61 7 336 57289
Fax: +61 7 336 58202
Listserv Archives (BOB member since 2002-06-25)