| < Day Day Up > |
|
When you place a condition on a measure object that uses an aggregate function, BusinessObjects does not generate a straightforward WHERE clause, but rather, it generates a HAVING clause. The RDBMS performs the aggregations and GROUP BY first, then returns only those results that satisfy the HAVING condition. In the following example, the query returns rows for which the SUM of SHOP_FACTS.MARGIN is less than or equal to 0:
SELECT Calendar_year_lookup.Year, Calendar_year_lookup.Month, Article_lookup.Family_name, sum(Shop_facts.Margin) FROM Shop_facts, Calendar_year_lookup, Article_lookup WHERE ( Article_lookup.Article_code=Shop_facts.Article_code ) AND ( Shop_facts.Week_key=Calendar_year_lookup.Week_Key ) GROUP BY Calendar_year_lookup.Year, Calendar_year_lookup.Month, Article_lookup.Family_name HAVING ( sum(Shop_facts.Margin) <= 0 )
The problem here is that many users are deceived into thinking this is a simple query, as it returns few results. It is true that there may not be many article families/ lines that have a negative margin for a particular month. However, to answer the query, the database must do a full table scan on the fact table. In the sample database, the fact table is quite small; however, in real-world databases, the fact table can be millions of rows of data. To minimize the risk of this, consider adding conditions on any other dimension objects that will generate a WHERE clause. For example, a condition on Year in addition to Margin will allow the database first to select only those rows for a particular year. The database then performs the GROUP BY and HAVING on a smaller set of data (possibly retrieved via an index).
Tip | If you use measures as conditions, ensure you include other conditions on dimension objects to improve the query processing time. |
| < Day Day Up > |
|