Getting Started with MAQL as a Freemium User

  • 22 July 2020
  • 0 replies
  • 39 views

Userlevel 1

Metric Creation for KPIs and Analytical Dashboard

 

MAQL (Multi-Dimension Analytical Query Language) is used to create robust metrics for KPIs and Analytical Designer. MAQL is a query language that you can use to build metrics (aggregations of the data). Before you get started, you should know that:

  • Metrics always return numerical values.

  • Metric can only return value based on some dimensionality/context in which it is executed

  • Metrics in MAQL start with the keyword SELECT.
    A simple aggregation function looks like the following where Quantity is a fact:
    SELECT SUM(Quantity)

 

Custom Metric Editor

The Custom Metric Editor is a graphical user interface tool that guides you through creating your own metrics in MAQL. Consider this the “control center” for making, editing, and testing MAQL functionality.

 

How to Access the Custom Metric Editor

You can access the Custom Metric Editor through the Manage tab. On the Manage Page, select Metrics on the left hand panel under ‘Data’. 

Steps:

  1. Navigate to:

    1. Select the ‘Manage’ tab on the top black header next to your workspace name and buttons for KPIs, Analyze, and Load.

    2. This should take you to a ‘Data’ page. From here, select ‘Metrics’ on the left side panel under ‘Data’.

    3. Click on the ‘Create Metric’ button on the top right corner.

  1. Click Custom Metric:

         5QtGr5u2A5JwpnGGojjid27nZID0CdXFyEY47bhHEdyPAn2O70g6tHDy58DpNqrc0ZnjOSNDGScjFr-TcYt2a8f1AvMVQKEQtqu2Ibbtl6h186qqG084exZi9WHbHjE9XVD9Pzls

  1. Define your metric by entering MAQL statements directly into a text field or selecting them from the right-hand side menu

 

Working with Custom Metric Editor

 

You can type MAQL syntax manually into the main text field, or you can select MAQL functions from the bottom section of the Advanced Metric Editor.

 

Grouped in Aggregation, Numeric, Granularity, Logical, and Filters tabs, there are MAQL operators, functions, and keywords that you can click to use in your metric definitions. Each function also features a usage example to help you create your metrics:

 

-pV2mMK9arArvwGHlKqLbl5zOtWkDvddHO7vj3ndDMQvIhh6f2dDISSj32KSquc5YsaKgKhscy1Yt9QtfWf2MKy7FBcw9TyL5g3OMQrgFK9BQtQDoUlYREMLi3tfjLPgCfGrqVuI

 

On the right-hand side, you will find the Project Element Sidebar that lists the project's elements (facts, metrics, attributes, attribute values, and variables) - all in the form of snippets that you can insert directly into your metric's definition.

 

To add the element into your metric, simply expand the desired element type, select the element, and double-click it or click Add Selected.

 

For more information on MAQL visit here:

https://help.gooddata.com/doc/en/reporting-and-dashboards/maql-analytical-query-language

 

For more information on MAQL formula definitions visit here: 

https://help.gooddata.com/doc/en/reporting-and-dashboards/maql-analytical-query-language/maql-expression-reference

 

Getting Started with Metrics 

Project elements in your MAQL definitions are automatically color-coded so you can keep track of which elements you included in your metric and ensure that your MAQL syntax is valid:

 

Below is the color coding of the MAQL elements: 

  • Facts

  • Metrics

  • Attributes

  • Attribute Values

Write Your First Metrics

 

The following examples will show you a simple progression path to building your own metrics. Each example is followed by syntax description.

 

MAQL always works in the context of data available in a given logical data model which determines the available data and utilization of MAQL statements.

 

Example 1 - Quantity

 

The following sample uses the SUM aggregation function on a fact (quantity) to return total all-time quantity of products sold.

Using the template SELECT SUM(Fact), we can alter the statement to:

This will result in a report featuring a single number, total units sold, in a single row.

 

Example 2 - Applying Arithmetic

 

To find out what was the revenue of the total all-time sales, first multiply two facts (quantity and price) and then aggregate to the context.

Using the template SELECT SUM(Fact*Fact), we can alter the statement for metric Order Amount:

 

This will result in a report featuring a single number, revenue for total units sold, in a single row.

 

Example 3 - Conditioning/Filtering and Reusing metrics

 

The following example uses an existing metric (Revenue) and combines it with the conditional statement WHERE defined by the Attribute (Product Category) and Attribute Value (Clothing).

Using the template SELECT Metric WHERE Product Category IN (Clothing), we can alter the statement for metric Order Amount:

When constructing complex metrics, your choice of attributes and attribute values is determined by the context of available data.

 


0 replies

Be the first to reply!

Reply