Pre-Aggregation and Post-Aggregation Calculations

Perform pre-aggregation and post-aggregation calculations in Insights:

  • Pre-aggregation—This calculation, also known as a physical query, is executed by the database. This is used for custom measures and custom attributes. Report expressions can use pre-aggregation if the Push Down Expressions to Database feature is enabled.

  • Post-aggregation—This calculation, also known as a logical query, is executed by the Insights logical layer using BQLClosed Business Query Language. This is used for ETL scripts and report expressions.

Note: Please note that the syntax used for post-aggregation calculations (report expressions, ETL scripts) and pre-aggregation calculations (custom measures and attributes) is different.

Pre-Aggregation Calculations

Custom attributes and custom measures are “pre-aggregation” calculations. Pre-aggregation expressions refer to the physical columns in the raw tables of a database, while post-aggregation expressions or logical queries refer to logical constructs that may exist in physical tables. When entered expressions are turned into SQL and delivered to the database, it is referred to as "function shipping". The database performs calculations row-by-row, aggregates the results, and returns a highly aggregated, summarized result to the Insights logical layer.

Insights is an Online Analytic Processing (OLAP) tool that generates queries based upon the column sets the end user specifies. Non-OLAP tools require end users to execute a query themselves; the tool then performs calculations on the resulting data.

Database calculations are limited. A database does not understand joins, dimensions, or hierarchies. With large databases, it is impractical to move billions of records to the client for aggregation or summation purposes. However, some calculations must be done within the database; this particularly applies to metrics that must be done row by row and cannot be done on aggregated results.

Example: Consider a [Sales] metric which is Quantity*Price. To calculate total sales, the metric is applied to each database row and then the row results are summed. The database must calculate this metric; if this calculation is performed at a report level, you receive a sum of Quantity multiplied by the average of Price. This is incorrect and misleading.

In Insights, when you type a formula for a custom attribute or measure, you create a pre-aggregation expression. Positional calculations, ranks, lookups, and so on, must occur in the logical layer of Insights post-aggregation. These attributes and measures occur after the database summarizes the records into a 100 row result set. Logical expressions manipulate those 100 rows to make even more complex calculations.

Post-Aggregation Calculations

The Insights engine performs calculations on dimension, measure, position, and hierarchy constructs while the end user maintains control over data manipulation. However, the data is post-aggregation, and any logical queries that return result sets to the middle tier are restricted to 100,000 returned records. Insights sets this upper limit in order to maintain system performance and speed.

Example: The custom metric [Sales] is defined as Quantity*Price. Another base metric [Quantity] and the base attribute [Year] help narrow and define the results when calculating for average unit price.

To calculate average unit price, the following logical query is executed:

SELECT [Time.Year],[Sales]/[Quantity] AS 'Average Unit Price' FROM [ALL]

Both pre-aggregation and post-aggregation calculations are present: one is executed in the database and one is executed in Insights.

That query is transformed into physical SQL that looks something like:

SELECT DW_DM_TIME_DAY.Year$, SUM(FACT.quantity*FACT.price),SUM(quantity) FROM FACT INNER JOIN DW_DM_TIME_DAY on FACT.DayID=DW_DM_TIME_DAY.DayID

The calculation for [Sales] is compiled directly into SQL.The syntax that supports this is restricted only to things that can be sent to the database. When the result set comes back, Insights takes each row and divides the [Sales] value by the [Quantity] value to get an average unit price by year. The expression syntax at the logical layer can use BQLClosed Business Query Language functions.

ETL Services

The language for report-level (post-aggregation) expressions uses the same engine as the ETL scripting engine. Therefore, BQL is used for ETL as well as report-level expressions. This allows users to embed scripts in reports. Please note that some BQL statements only function when running in a script (e.g. WRITERECORD) and others are only allowed when working in a report (e.g. lookups).

Where possible, Insights makes the syntax for the two different types of expressions the same.

Example: The operators (+,-,/,*) are the same. Some functions (e.g. DATEADD) are the same syntax because underlying databases support that syntax.

See Also