Pentaho metadata formulas

Pentaho Metadata Editor

Part Number

Formulas have multiple uses in Pentaho metadata. The first use of formulas in Pentaho metadata is in the constraint definition of a Metadata Query, also known as MQL. A constraint function references business table columns and uses various comparison operators to determine which subset of data the business user is interested in.

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 in the metadata environment. Formulas are first interpreted by libFormula, and then in the metadata system are converted to native SQL depending on the type of database used.