BusinessObjects Perfromance Questions - warning, lots of t

building semantic layer
1

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)

2

Hi Crystal,

Just a number of remarks which might help you into the right direction:

First, you’re right that BO doesn’t check if a particular where clause for an object is identical to another where clause. That is because BO’s opinion is, the where clause for an object is for that object, so it just concatenates everything it finds. Leaving any moulding of the SQL statement into an optimal form to the optimizer of the database management system.

Looking at your description I get the feeling that the object where clause is not the right place for your where clause, but that you are looking for a way to restrict a table. There are two alternatives for that:
1: Restrict the table using the supervisor module. Supervisor, universe tab, properties, table-restrictions. This where clause gets added whenever the table is part of the query (and only once). 2: Restrict the table using a self-join. Don’t forget to add it to the context. This will give you the same effect as adding it in the supervisor.

Another thing about object where clauses is that they are not that easy to understand for a user. The effect might be like picking sales this year, sales last year and not getting back anything. In your case you use the aliases to avoid these restrictions, but this implies that you have to access the same table twice or more AND join the lot.

An alternative for measures like that is to use a function that sums under a certain condition, in oracle you would use a decode like:

sum(decode(,,))

I’m not familiar with rdb, but a function like this (in access you can use iif) might exist there. The benefit is that the user can now select both conditionless and other conditional objects from the same table in one go.

Hope this helpes,
Marianne Wagt
IDETA
The Netherlands

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