Measure ConditionsHaving Clause

 < Day Day Up > 



Measure Conditions/Having Clause

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:

click to expand

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 > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net