Hitachi Vantara Pentaho Community Wiki
Child pages
  • 02. Pentaho Metadata Formulas

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0

...

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.

Wiki MarkupThe first boolean expression first references a business column from our Metadata model.     All references appear with brackets around them \ [\].    This reference first refers to the business table, and then to the business column.   This boolean expression first does some arithmetic and checks to see if the final value us larger than    This boolean expression first does some arithmetic and checks to see if the final value us larger than 1000.

In the second expression, we compare the business column BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME to EuroCars.  Note that we use double quotes when referring to text.  Double quotes are required.

...

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

...

Code Block
SELECT
           SUM( BT_ORDER_FACT.NRPRODUCTS  *  BT_PRODUCT.PRICE ) AS COL0
FROM
          FACT_ORDER BT_ORDER_FACT
         ,DIM_PRODUCT BT_PRODUCT
WHERE
          ( BT_ORDER_FACT.PRODUCT_TK = BT_PRODUCT.PRODUCT_TK )

Wiki MarkupNow, suppose we want to generate the multiplication of the 2 sums (different use-case), we define the formula as  "\formula as  "[BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS\] * \ [BT_PRODUCT.BC_DIM_PRODUCT_PRICE\]" (without the SUM) and specify an aggregation for the 2 used business columns.    The generated SQL will then be:

Code Block
SELECT
           SUM( BT_ORDER_FACT.NRPRODUCTS )  *  SUM( BT_PRODUCT.PRICE ) AS COL0
FROM
          FACT_ORDER BT_ORDER_FACT
         ,DIM_PRODUCT BT_PRODUCT
WHERE
          ( BT_ORDER_FACT.PRODUCT_TK = BT_PRODUCT.PRODUCT_TK )

...

  • ID = BC_FACT_ORDER_TURNOVER_TAXES
  • Name = Turnover Taxes
  • Wiki MarkupFormula = \ [BT_ORDER_FACT.BC_FACT_ORDER_TURNOVER\] * 7 / 100
  • Exact = Yes

 If we add that column to the selection, we get one extra column like this:

...

Function Name

Parameters

Description

Example

OR

two or more boolean expression parameters

Returns true if one or more parameters are true

OR(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="5dabf724-2808-4452-95bd-18be84453ffd"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="80e3b6de-093a-406a-8b3d-4dc994fdfcfa"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000
]]></ac:plain-text-body></ac:structured-macro>
)

AND

two or more boolean expression parameters

Returns true if all parameters are true

AND(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9348f4a4-41da-4082-b269-94d79e2c5770"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="99d7c265-c556-4d59-be86-3f2d22031843"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000
]]></ac:plain-text-body></ac:structured-macro>
)

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="39312719-1672-4a28-b8cd-8330cfc4ce77"><ac:plain-text-body><![CDATA[

LIKE

two parameters

Compares a column to a regular expression, using "%" as wild cards

LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9c3e898a-64e8-46bf-bdce-5cd5c77d6b16"><ac:plain-text-body><![CDATA[

CONTAINS

two parameters

Determines if a column contains a string.

CONTAINS([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="0fd6a288-77ab-4e35-aa07-3baa59ee897c"><ac:plain-text-body><![CDATA[

BEGINSWITH

two parameters

Determines if a column begins with a string.

BEGINSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "JOE")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8f936fc6-bc7f-48a8-821d-c99083ef72b8"><ac:plain-text-body><![CDATA[

ENDSWITH

two parameters

Determines if a column ends with a string.

ENDSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="4a052f82-d90a-43f0-842f-83c8ab01ff6f"><ac:plain-text-body><![CDATA[

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")

]]></ac:plain-text-body></ac:structured-macro>

NOW

none

The current date

NOW()

DATE

three numeric parameters, year, month, and day

A specified date

DATE(2008;4;15)

DATEVALUE

one text parameter "year-month-day"

A specified date

DATEVALUE("2008-04-15")

CASE

two or more parameters

Evaluates the first, third, etc parameter, and returns the second, fourth, etc parameter value
if there are an odd number of parameters, the last parameter is returned if no other parameter evaluates to true.
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(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8f023e0f-7763-4106-ba9b-672a5735cfac"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
"European Cars";
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ab56d5ff-ec9d-4527-86f8-6032e1dc59eb"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars";
]]></ac:plain-text-body></ac:structured-macro>
"Asian Cars";
"Unknown Cars"
)

COALESCE

one or more parameters

returns the first non null parameter

COALESCE(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="b48df33f-13d2-438b-bdda-bfa54657269b"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME];
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="d68bcad9-128e-48b4-82b6-4ba6bba29c4d"><ac:plain-text-body><! [CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID];
]]></ac:plain-text-body></ac:structured-macro>
"Customer is Null"
)

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
DATEMATH("0:MS  0:WE") - 23:59:59.999 the last day of the first week of the month
DATEMATH("0:ME") - 23:59:59.999 of the last day of the current month
DATEMATH("5:Y") -  the current month, day and time 5 years in the future
DATEMATH("5:YS") - 00:00:00.000 of the first day of the years 5 years in the future

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="dce26744-cfdc-4f3e-9e76-02e13815f492"><ac:plain-text-body><![CDATA[

ISNA

one parameter

returns true if the value is null

ISNA([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID])

]]></ac:plain-text-body></ac:structured-macro>

NULL

none

returns the null value

NULL()

TRUE

none

returns true

TRUE()

FALSE

none

returns false

FALSE()

...