Applying aggregate awareness to objects in a Business Layer involves a four-step procedure:
Steps in the Aggregate Awareness Procedure
- Insert one or more summary tables in the Data Foundation. If necessary, set joins and cardinality.
- Set the contexts if joins were added.
- Define the objects using @Aggregate_Aware.
- Define incompatible objects using Aggregate Navigation.
Step 1: Insert a summary table
Step 1 of the aggregate awareness process is to insert one or more summary tables to the data foundation. The procedure for inserting a summary table in the structure of the data foundation is the same as for any other table or view.
Insert the required summary tables in the structure using the Connections browser in your data foundation.
Position the table where it is convenient to make the joins.
Add joins from the summary table to the existing structure. You examine the foreign keys in the summary table to see where the summary table can be joined.
Set cardinality for the new joins.
Step 2: Detect contexts
Step 2 of the aggregate awareness process is to detect the summary context, if joins to the summary table were added.
Click Detect Contexts on the toolbar.
Accept the new context(s).
Step 3: Applying aggregate awareness to objects
The columns in the summary table containing aggregated and dimension data can be used to define object SELECT properties.
Before editing any objects, confirm exactly what levels of aggregation are available. In our example, we have the basic aggregation calculation and the precalculated annual data from the summary table. If you also have summary tables for quarterly figures and monthly figures, you would have four possible levels altogether.
When you apply the @Aggregate_Aware function, be aware of the available levels. There is a descending order of aggregation, for example, Annual Figures, Quarterly Figures, Monthly Figures, and basic aggregation calculation.
The syntax of the @Aggregate_Aware function:
- @Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))
You enter the names of all aggregate tables as arguments. Place the names of the tables from left to right in descending order of aggregation.
agg_table_1: Is the aggregate with the highest level of aggregation.
agg_table_n: Is the aggregate with the lowest level of aggregation
Each aggregation level SELECT statement is separated by a comma, and the entire expression is enclosed in brackets. The final SELECT statement must be valid for all queries.
The @Aggregate_Aware function is directing the query engine to use the sum of the measure value taken from the summary table where possible and, where not possible, to use the next segment in the SELECT statement. The last segment in the @aggregate_aware function is always a SELECT that does not use the summary tables.
@Aggregate_Aware(sum(ANNUAL_FIGURES.ANNUAL_SALE_VALUE),sum(SALE_MODEL.SALE_QTY *MODEL.MODEL_PRICE* ((100-SALE.SALE_DISCOUNT) / 100)))
Similarly, for dimension objects in the summary tables, the @Aggregate_Aware function simply selects the column from the summary tables first (in descending order of aggregation), and then from the non-summary source.
To apply aggregate awareness to an object's SQL definition:
Double-click the object whose properties you want to edit, or click Insert Object on the toolbar to create a new object.
Click Select Assistant of the Select field to open the Edit Select dialog box. The current
SELECTproperties for the object, if any, displays in the top panel of the dialog box.
Click at the beginning of the existing statement, if necessary.
Double-click the @Aggregate_Aware function in the @Functions list dialog box.
Insert the aggregate actions within the brackets of the @Aggregate_Aware function in order of highest to lowest level of aggregation data. Separate each argument with a comma.
Parse the object.
Repeat the process for all the appropriate objects.
Step 4: Define incompatible objects using Aggregate Navigation
1. Open the business layer in the editor by double-clicking the business layer name in the Local Projects View.
2. From the information design tool main menu, select Actions → Set Aggregate Navigation.
In the Aggregate Awareness dialog box, you specify which folders contain objects that are not compatible with the aggregate table.
3. Click an aggregate table in the left pane.
4. Either click on the Detect Incompatibility button or in the right pane, select the check box for each object that is incompatible with the summary table.
6. Repeat the previous steps for each aggregate table in the data foundation.
7. Click OK, when all incompatible objects for all the tables are specified.
A mark in the check box on a folder or an object or a filter, means that it is incompatible to the aggregation process.
To Test @Aggregate_Aware
Open a new query and select only objects that are aggregate aware. View the script.
Add an incompatible object to the query.
The aggregate table(s) is not used.