Using the @Aggregate_Aware Function

Some databases contain summary tables. These tables are created by the Database Administrator (DBA). They contain figures such as revenue, aggregated to a high level (year, for example), rather than to the fact or event level. The summary tables tend to be populated and updated regularly by an automated program that runs SQL against the fact or event data at transaction level.

This method of population means that there are two methods that you can use to return aggregated data:

• Run a SELECT statement for the fact or event data.

• Run a SELECT statement for the summary data.

Where possible, choose the latter method, as the statement processes quicker. In the Information Design Tool, you can use a function called @Aggregate_Aware in the SELECTstatement for an object. This references both methods. This function directs a query to run against aggregate tables whenever possible. If the data in the aggregate table is not calculated at the level of granularity required to run the query, the object directs the query to run against the tables containing the non-aggregated data.

A business layer that has one or more objects with alternative definitions based on aggregate tables is said to be aggregate aware.

Each row in a summary table is made up of columns containing:

• Aggregated data:

  Numeric event data aggregated to a higher level. In the ANNUAL_FIGURES table, columns containing aggregated data are Annual_Sale_Value, Annual_Sale_Cost, Annual_Sale_Number, Annual_Rent_Value, and Annual_Rent_Number.

• Dimension data: Attributes defining the level of the aggregated data.

  In the ANNUAL_FIGURES table, there is only one column containing dimension data: FP_Year.

• Foreign keys (optional): Joins to other tables. If foreign keys exist, queries can be made using summary table aggregated data. This information is based on dimensional data held in other tables of the database, as opposed to only data contained within the summary table. Foreign keys are used to set joins in the structure of the data foundation.

Where possible, it is prudent to use summary table data, because the processing required to return the aggregated data is far quicker.

As shown in the Figure Row Count without Aggregation, making a query based on event data at a transaction level requires a more complex SELECTstatement and the processing of more database rows than one based on summary data.

Summary tables can be added to a database that hold data at a higher level of aggregate. As shown in the Figure Total Rows Used with Aggregation, using summary table data speeds up response times because:

• There are fewer rows to process.

• Fewer, if any, joins are required.

Applying aggregate awareness to objects in a Business Layer involves a four-step procedure:

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.

1. Insert the required summary tables in the structure using the Connections browser in your data foundation.

2. Position the table where it is convenient to make the joins.

3. 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.

4. Set cardinality for the new joins.

Step 2 of the aggregate awareness process is to detect the summary context, if joins to the summary table were added.

1. Click Detect Contexts on the toolbar.

2. Accept the new context(s).

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.

For example:

@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.

For example:

@Aggregate_Aware(ANNUAL_FIGURES.FP_YEAR,FINANCE_PERIOD.FP_YEAR)

To apply aggregate awareness to an object's SQL definition:

1. Double-click the object whose properties you want to edit, or click Insert Object on the toolbar to create a new object.

2. 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.

3. Click at the beginning of the existing statement, if necessary.

4. Double-click the @Aggregate_Aware function in the @Functions list dialog box.

5. 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.

6. Parse the object.

7. Repeat the process for all the appropriate objects.

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.

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.