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 BQL
Business Query Language. This is used for ETL scripts and report expressions.
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.
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.
To calculate average unit price, the following logical query is executed:
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:
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 BQL 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.