Page Comparison

...

Here is an example of a Physical Table Column Formula:

SUM([QUANTITYORDERED]*[PRICEEACH])

Aggregate functions are supported within physical table column formula definitions.  The references here specifically refer to the database column, not derived physical column definitions.  All operators and functions may be used in the definition of the physical table column.  One special note, in order for this formula to be recognized, the "isExact" property of the physical table column must be set to true. Also note, the referenced physical column must be explicitly defined in the metadata model.

In earlier versions of Pentaho Metadata Editor, Aggregation functions needed to be explicitly specified as well as selecting the Aggregation rule.  This is no longer necessary, the query generated will use the selected aggregation rule when executing.

Multi table expressions: Formulas can use any business column in the model

...

• Table: Orders (BT_ORDER_FACT)
• ID = BC_FACT_ORDER_TURNOVER
• Name = Turnover
• Formula = SUM( [BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS] * [BT_PRODUCT.BC_DIM_PRODUCT_PRICE] )
• Exact = Yes
• Aggregation Rule = SUM

The SQL generator is now going to replace the 2 business columns by their respective SQL variants.  As such, we have to make sure that the business columns on which we base ourselves are resolving correctly. In this specific case, this means we want the 2 columns to be non-aggregated.  If we now select the single business column BT_FACT_ORDER_TURNOVER, this is the SQL that is generated:

...

Aggregate functions may only be used in physical column definitions. In more recent versions of metadata editor, these functions are no longer required.  Instead, the query generator uses the Aggregation rule specified by the user.