Using the @Aggregate_Aware Function

Objectives

After completing this lesson, you will be able to:

  • Use the @Aggregate_Aware Function

The @Aggregate_Aware Function

Aggregate Awareness function

@FunctionDescriptionUsually used in object
@Aggregate_AwareIncorporates columns containing aggregated and dimensions data into objectsSELECT statement

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.

Aggregate Awareness Function Use

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.

Note

Aggregate awareness works on all tables, not just fact tables. It can be applied to all object types with all data types, not just numbers and measures.

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.

The @Aggregate_Aware Application Process

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

Steps in the Aggregate Awareness Procedure

  1. Insert one or more summary tables in the Data Foundation. If necessary, set joins and cardinality.
  2. Set the contexts if joins were added.
  3. Define the objects using @Aggregate_Aware.
  4. 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.

  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: Detect contexts

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

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.

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.

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

Note

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.

Use @Aggregate_Aware

In this short video, you will see the process for using @Aggregate_Aware and its benefit.

Use @Aggregate_Aware in the IDT

Management is finding that annual reports take a long time to run. They have requested that you produce these reports without any noticeable processing delay. You decide to use the aggregate tables that exist in the Motors database to take advantage of aggregated data.

Log in to track your progress & complete quizzes