| Panel |
|---|
Please see the Pentaho InfoCenter for the latest version of this document: http://infocenter.pentaho.com/help/topic/pme_user_guide/concept_pme_metadata_formulas.html |
Formula Overview
Formulas have two multiple uses in Pentaho Metadata.
...
The second use is in the definition of Physical Table Columns. In addition to Physical table columns mapping directly to a database table column, physical table columns defined in Pentaho Metadata may also be defined as a formula. This allows for combining of multiple columns into a single column, and also for doing more advanced aggregate calculations within aggregate table definitions.
The third use is in the definition of complex joins within business model relationships. This allows for multiple key joins as well as other logic when joining tables.
The fourth use is row level security.
Under the covers, Pentaho Metadata uses JFreeReport's libFormula package for interpreting formulas. The goal is to support OpenFormula syntax within the Metadata environment. Formulas are first interpreted by libFormula, and then within the Metadata system are converted to native SQL depending on the type of database used.
...
Here is an example of an MQL Constraint formula:
| Code Block |
|---|
OR([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars" OR; (([BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] * 2) / 3 > 1000)); |
We'll walk through this example to help explain the core components of MQL formulas. First note the OR function. This is a boolean function which has two parameters, separated by semi-colons. These parameters are boolean expressions.
...
Here is an example of a Physical Table Column Formula:
| Code Block |
|---|
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.
Recent Changes to Physical Table Column Formulas
In earlier versions of Pentaho Metadata Editor, (prior to the CITRUS release), aggregation functions had to be specified explicitly and the aggregation rule had to be selected. This is no longer necessary; the query that is generated will use the selected aggregation rule during execution. See Defining the Physical Column Aggregations for more information.
Multi-table expressions: Formulas can use any business column in the model
Since the latest versions (after 2008/03/14) it is possible to define formulas that use business columns from anywhere in the business model.
For example suppose we have 2 two business tables:
- Orders (fact table), ID=BT_ORDER_FACT
- Product (dimension), ID=BT_PRODUCT
...
- 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:
...
| Code Block |
|---|
[<PHYSICAL_COLUMN_NAME>] |
MQL Parameter References:
| Code Block |
|---|
[param:PARAM_NAME]
|
Supported Functions
Function Name | Parameters | Description | Example | |||
|---|---|---|---|---|---|---|
OR | 2 two or more boolean expression parameters | Returns true if one or more parameters are true | OR( | |||
AND | 2 two or more boolean expression parameters | Returns true if all parameters are true | AND( | |||
LIKE | 2 two parameters | Compares a column to a regular expression, using "%" as wild cards | LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%") | |||
CONTAINS | IN | 2 two parameters | Determines if a column contains a string. | CONTAINS([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH") | ||
BEGINSWITH | two parameters | Determines if a column begins with a string. | BEGINSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "JOE") | |||
ENDSWITH | two parameters | Determines if a column ends with a string. | ENDSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH") | |||
IN | two or more parameters | Checks to see if the first parameter is in the following list of parameters | IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian Jones") | |||
NOW | none | The current date | NOW() | |||
DATE | 3 three numeric parameters, year, month, and day | A specified date | DATE(2008;4;15) | |||
DATEVALUE | 1 one text parameter "year-month-day" | A specified date | DATEVALUE("2008-04-15") | |||
CASE | 2 two or more parameters | Evaluates the first, third, etc parameter, and returns the second, fourth, etc parameter value |
| COALESCE | 1 Note that when using this function, the formula needs to be set on a new column, not on the BT_CUSTOMER.BC_CUSTOMER_CUSTOMERNAME (using the example to the right) | CASE( |
COALESCE | one or more parameters | returns the first non null parameter | COALESCE( | |||
DATEMATH | one expression parameter | returns a date based on an expression. Important note - this does NOT return a timestamp irrespective of the implementation details mentioned in the description to the right.DateMath Javadoc for full syntax | DATEMATH("0:ME -1:DS") - 00:00:00.000 of the day before the last day of the current month | |||
ISNA | one parameter | returns true if the value is null | ISNA([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID]) | |||
NULL | none | returns the null value | NULL() | |||
TRUE | none | returns true | TRUE() | |||
FALSE | none | returns false | FALSE() |
- see below for aggregate functions
...
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.
Function Name | Description |
|---|---|
SUM | sums a specific columns values determined by grouping |
COUNT | counts a specific columns values determined by grouping |
AVG | averages a specific columns values determined by grouping |
MIN | selects the minimum column value determined by grouping |
MAX | selects the maximum column value determined by grouping |