Robert Schmidt schrieb:
That is not what is happening here. The query generated when an object is
added to the Scope of Analysis dialog is much different than the query
generated by simply adding the object to the Results window, in this case
where the query contains a Having clause.When the object is added to the Scope of Analysis dialog, BusinessObjects
takes the existing Having clause and turns it into a correlated subquery,
using all of the dimensions in the original select in the where clause of
the subquery. You can see where this behavior is not desirable, because the
query may never finish executing.
Robert, and all SQL experts from BusinessObjects 
I found a way, which should do the job, but this must be done in the
SQL-generator. So I will put an enhancement request shortly.
For non-ODBC data sources (i.e. those which support SELECT statements in the
FROM Clause) I found a way to re-write the sql, so that the aggregated valus are
generated only once by the database. This should give you “linear” complexity
again…
Using Island Rresort with the following query:
Year, Service line, revenue
where revenue > 200000
will give the following SQL (and 4 rows: accomodation all 3 years, and food&drinks
in FY95)
SELECT
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line,
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price)
FROM
Sales,
Service_Line,
Invoice_Line,
Service
WHERE
( Invoice_Line.inv_id=Sales.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Service.sl_id=Service_Line.sl_id )
GROUP BY
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line
HAVING
(
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price) > 200000
)
Adding a “drill down 1 level” in the Analysis will give the following:
SELECT
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line,
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price),
Service.service,
‘Q’+Format(Sales.invoice_date,‘Q’)
FROM
Sales,
Service_Line,
Invoice_Line,
Service
WHERE
( Invoice_Line.inv_id=Sales.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Service.sl_id=Service_Line.sl_id )
AND (
200000 < (
SELECT
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price)
FROM
Invoice_Line,
Service,
Sales SubAlias__8,
Service_Line SubAlias__10
WHERE
Invoice_Line.inv_id=SubAlias__8.inv_id
AND Invoice_Line.service_id=Service.service_id
AND Service.sl_id=SubAlias__10.sl_id
AND ‘FY’+Format(SubAlias__8.invoice_date,‘YY’) =
‘FY’+Format(Sales.invoice_date,‘YY’)
AND SubAlias__10.service_line = Service_Line.service_line
GROUP BY
‘FY’+Format(SubAlias__8.invoice_date,‘YY’),
SubAlias__10.service_line
)
)
GROUP BY
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line,
Service.service,
‘Q’+Format(Sales.invoice_date,‘Q’)
What must be done now is: rewrite the SQL, so that the corelated subquery becomes
uncorrelated and is evaluated only once by putting it into the FROM clause of the
outer query and removing all “correlating” joins, and adding all “dimension”
objects (which can be found in the group by clause of the inner query) into the
SELECT part of it. Give a name to the measure in the inner query (e.g. revenue)
and the whole SELECT clause (e.g. Agg_tab1).
Then restructure everything in the following way: move the inner SELECT clause to
the outer FROM clause (it’s used there like an ordinary table), move the
connecting joins rom the inner query to the outer WHERE clause and color it red
You will end up with something like the following (unfortunately I could not test
it because ODBC does not support such SQL 
)
SELECT
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line,
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price),
Service.service,
‘Q’+Format(Sales.invoice_date,‘Q’)
FROM
Sales,
Service_Line,
Invoice_Line,
Service,
(
SELECT
‘FY’+Format(SubAlias__8.invoice_date,‘YY’) invoice_date,
SubAlias__10.service_line service_line,
sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price) revenue
FROM
Invoice_Line,
Service,
Sales SubAlias__8,
Service_Line SubAlias__10
WHERE
Invoice_Line.inv_id=SubAlias__8.inv_id
AND Invoice_Line.service_id=Service.service_id
AND Service.sl_id=SubAlias__10.sl_id
GROUP BY
‘FY’+Format(SubAlias__8.invoice_date,‘YY’),
SubAlias__10.service_line
) Agg_Tab1
WHERE
( Invoice_Line.inv_id=Sales.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Service.sl_id=Service_Line.sl_id )
AND (
200000 < Agg_Tab1.revenue
AND Agg_Tab1.invoice_date = ‘FY’+Format(Sales.invoice_date,‘YY’)
AND Agg_Tab1.service_line = Service_Line.service_line
)
GROUP BY
‘FY’+Format(Sales.invoice_date,‘YY’),
Service_Line.service_line,
Service.service,
‘Q’+Format(Sales.invoice_date,‘Q’)
The pre-aggregated (temporary) table will be generated by the atabase first, then
it is user like an ordinary lookup table for the rest of the query. Indices, etc.
are an issue here, because the joins are made on (sometimes) complex expressions.
Have FUN with it!
Walter
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)