Multi-Dimension Analytical Query Language (MAQL) is a proprietary language for creating metrics and much more in your projects. Using MAQL, you can build flexible and powerful metrics to deliver unprecedented insights to your project's users.
Metrics are used for assembling reports. You can create basic metrics through the Report Editor in the GoodData Portal, which converts your selections into MAQL statements and stores them in the project.
For creating more sophisticated metrics, you can use the Custom Metric Editor where you can create your metrics in MAQL. The Custom Metric Editor features a glossary of MAQL terms, which you can reference as you build your metrics.
MAQL is a flexible database language, which can be deployed for a variety of purposes within your project. While sharing similarities with SQL, MAQL is significantly different in several key areas. Before you begin building any production projects, familiarize yourself with the basic structures of MAQL and the data model to which you are applying it.
The possibilities for building metrics in a project are constrained to the architecture of the project's data model.
Before you begin building metrics using raw MAQL statements, familiarize yourself with the data model of the project for which you are building them. You can review the model through the GoodData Portal. See View a Project Model.
A data model is the representation of the attributes and facts in your project's data and the relationships between them. You can create and publish data models within the CloudConnect LDM Modeler, an integrated component of CloudConnect Designer. See Transform and Load Data Using CloudConnect and Key Terminology.
MAQL DQL statements resemble SQL SELECT statements, as in the following:
See Basic MAQL Syntax.
MAQL includes a variety of functions, which can be applied to the facts and attributes of your project.
These functions provide basic aggregation capabilities, such as average, min and max, and summing of facts, as well as counting of attribute values. Aggregation functions are available for use through the Simple Metric Editor, too.
Math functions such as square root, log, and exponential.
|Running Total Functions|
You can apply many of the basic aggregation functions as running totals in your reports. These functions enable you to perform snapshot-type calculations constrained to the data range for the context of the calculation. For example, you can compute a RUNSUM for monthly sales for each day of the month, which enables users to review progress toward goals.
|Rounding and Truncation Functions|
For some calculations, it is helpful to round or truncate values to required levels of precision.
You can perform top, bottom, and rank calculations for a calculation and use them in conjunction with a WITHIN clause to constrain evaluation to a set of attribute values.
MAQL provides a number of specific transformations and functions for managing time periods in your metrics.
Operators and Conditionals
MAQL supports a range of arithmetic operations and conditionals common to most programming languages.
- For more information on data filtering through MAQL statements, see Filtering Data.
In MAQL statements, you can define explicit filters to be applied to the computed metric using a WHERE clause, as in the following.
- In your metric definitions, you can also configure overrides of filters applied in parent reports and dashboards. See PARENT Filters.
MAQL supports a variety of keywords to lock the aggregation levels within the metric itself under various conditions.
MAQL Expression Reference
For reference information on MAQL, see MAQL Expression Reference.
The above sections describe MAQL DQL, the data querying variant of MAQL.
In the GoodData Platform, however, MAQL has much wider usage. MAQL is available in the following flavors:
MAQL DQL - The Data Querying Language variant is used for defining metrics and querying for reports from the project.
Most users utilize MAQL DQL only. In this non-destructive variant, all MAQL DQL statements begin with SELECT. This section applies primarily to MAQL DQL.
- MAQL DDL - The Data Definition Language variant is used to define the logical data model in a project. CloudConnect Designer issues MAQL DDL statements when adding or updating a logical data model in the GoodData Platform. However, most users do not have direct interactions with this variant. See Transform and Load Data Using CloudConnect.
MAQL DML - In rare cases, you can use the Data Manipulation Language variant of MAQL to add or delete data from a project.
You can use MAQL DML to permanently delete data from a project. If the DELETE statement is poorly constructed, unexpected cascading deletions may be caused in the project. We do not recommend you use this method of data management. For more information on removing data from your project, see Deleting Data from a Project.