You then re-define the Select statement using the @Aggregate_Aware function for all aggregate aware objects. The @Aggregate_Aware function directs an object to query first of all the aggregate tables listed as its parameters. If the aggregate tables are not appropriate, then the query is run with the original aggregate based on the non-aggregated table. For more information about @Functions see the section Using @Functions.
The Select statement for Sales Revenue using the @Aggregate_Aware function appears below.
The syntax of the @Aggregate_Aware function is as follows:
@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))
Where agg_table_1 is the aggregate with the highest level of aggregation, and agg_table_n the aggregate with the lowest level. You must enter the names of all aggregate tables as arguments. You place the names of tables from left to right in descending order of aggregation.
To re-define an object using @Aggregate_Aware:
1. Double
click an object.
The Edit Properties dialog box for the object appears.
2. Click
the >> button next to the Select box.
The Edit Select Statement dialog box appears.
3. Click
at the beginning of the Select statement.
Or
Click anywhere in the select box if the object does not yet have a Select
statement.
The cursor appears at the top left corner of the box.
4. Click
the @Functions node in the Functions pane.
The list of available @functions appears.
5. Double
click @Aggregate_Aware.
The syntax for @Aggregate_Aware is inserted in the Select statement. A
description of the syntax appears in the Description box at the bottom
of the dialog box. You can use this to help you type the parameters for
the @function.
6. Insert the aggregates within the brackets of the @AggregateAware function in order (highest to lowest level of aggregation data).
7. Separate each aggregate with a comma. For the example, the syntax for the Sales Revenue is:
@Aggregate_Aware(sum (AAYEAR.REVENUE),
sum(AAQTR.REVENUE), sum (AAMONTH.REVENUE),
sum(PRODUCTS.PRICE*ORDER_LINES.QUANT))
8. Click
the Parse button to verify the syntax.
The Edit Select page of the SQL editor for Sales Revenue is shown below.
9. Click
OK in each of the dialog boxes.
In the example, you also re-define the dimension objects Year and Quarter
with the @Aggregate_Aware function.