03. Pentaho Metadata MQL Schema

MQL is the syntax Pentaho Metadata uses for generating SQL queries based off of metadata.  Normally a user would generate MQL via Pentaho's Design Studio or Pentaho's Metadata Editor.  Here is a description of the MQL XML format:

• <mql> - top level element for mql query
  • <domain_type> - text element that contains the domain type, currently only "relational" is supported
  • <domain_id> - text element that contains the domain id to query
  • <model_id> - text element that contains the model id to query
  • <model_name> - not required - text element with the model name
  • <parameters> - not required - element that contains a list of parameters for the query
    • <parameter> - element that contains information about an individual parameter
      • <name> - the name of the parameter (parameters are referenced in constraints as [param:PARAM_NAME])
      • <type> - The data type of the parameter, valid values include boolean, numeric,
      • <defaultValue> - The default value of the parameter as a string
  • <selections>- element that contains a list of business column selections
    • <selection> - business column selection element
      • <view> - text element that contains the id of the business table to select
      • <column> - text element that contains the id of the business column to select
      • <aggregation> - text element that contains aggregate type (none, min, max, sum, avg, count, distinct_count)
      • <alias> - a local alternate name to refer to this selection. Aliases maybe referenced in <constraint> elements just like you would do with a column id.
      • <formula> - Use this element instead of a <view> and <column> reference to specify a calculated field.
  • <constraints> - element that contains a list of constraints for the MQL query
    • <constraint> - constraint container element
      • <operator> - text element that describes how to join the constraint to the query.  The first constraint operator is ignored but required.  Valid entries are "AND", "OR", "AND NOT", and "OR NOT"
      • <condition> - text element that contains an MQL formula as defined in 02. Pentaho Metadata Formulas.
  • <orders> - element that contains a list of business columns to order by
    • <order> - order container element
      • <direction> - text element containing either "asc" or "desc"
      • <table_id> - text element containing the id of the business table to order by
      • <column_id> - text element containing the id of the business column to order by
      • <aggregation> - text element that contains aggregate type

Example

For an example of a MQL query, check out the MQL_Datasource.xaction. This sample action sequence is shipped with the BI server and sits in the datasources folder in the bi-developers solution. By way of example the MQL query used in that action sequence is shown below:

<mql>
  <domain_type>relational</domain_type>
  <domain_id>steel-wheels</domain_id>
  <model_id>BV_ORDERS</model_id>
  <model_name>Orders</model_name>
  <selections>
    <selection>
      <view>CAT_ORDERS</view>
      <column>BC_ORDERS_ORDERDATE</column>
    </selection>
    <selection>
      <view>CAT_ORDERS</view>
      <column>BC_ORDERS_ORDERNUMBER</column>
    </selection>
    <selection>
      <view>CAT_ORDERS</view>
      <column>BC_ORDER_DETAILS_QUANTITYORDERED</column>
    </selection>
  </selections>
  <constraints>
  
    <constraint>
      <operator>AND</operator>
      <condition>[CAT_ORDERS.BC_ORDERDETAILS_QUANTITYORDERED] >70</condition>
    </constraint>
    
    <constraint>
      <operator>AND</operator>
      <condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] > DATE(2003;12;31)</condition>
    </constraint>
  </constraints>
  <orders/>
</mql>

Support for Multiple Aggregate Types

Note: The feature described in this section will not be available until the CITRUS release (date pending).

You can now modify their Physical Columns to include multiple aggregate types. These aggregate types are available in the MQL Editor dialog boxes in Pentaho Metadata Editor, Report Designer, and Chart Designer.

The first change is the optional aggregation child element of selection, which may be one of the values selected in Pentaho Metadata Editor:

<selection>
	<view>bc_example</view>
	<column>BC_VALUE</column>
	<aggregation>sum</aggregation>
</selection>

The second change is an extension to the formula syntax when referring to business columns. Before, the syntax to reference the model only supported [<BUSINESS CATEGORY>.<BUSINESS COLUMN>], now we also have [<BUSINESS CATEGORY>.<BUSINESS COLUMN>.<AGGREGATION>]:

[bc_example.BC_VALUE.sum] >= 100

The last change impacts the order portion of the MQL; it's an identical change to the selection XML, with an optional aggregation child element:

<order>
	<direction>asc</direction>
	<view_id>bc_example</view_id>
	<column_id>BC_VALUE</column_id>
	<aggregation>SUM</aggregation>
</order>

See Second Use: Physical Table Column Formulas and Defining the Physical Column Aggregations for additional details.