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:
-
Navigate to:
-
Select the ‘Manage’ tab on the top black header next to your workspace name and buttons for KPIs, Analyze, and Load.
-
This should take you to a ‘Data’ page. From here, select ‘Metrics’ on the left side panel under ‘Data’.
-
Click on the ‘Create Metric’ button on the top right corner.
-
-
Click Custom Metric:
-
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:
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/free/en/dashboards-and-insights/maql-analytical-query-language/
For more information on MAQL formula definitions visit here:
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.