MAQL and Multidimensionality
Multidimensionality is a fundamental principle of GoodData’s MAQL.
Multidimensional modeling works with the concepts of facts and dimensions meaning that MAQL metrics are context aware. Metrics are sliced by the current context of filters and dimensions that do not need to be specified in the MAQL expressions themselves. This multidimensional capability is one of the features that sets MAQL apart from SQL.
Examples of Multidimensionality
In this section, we will use several examples to show you how MAQL works with multidimensionality.
Example 1
In this example, we created a SUM metric to display amount sold of a particular product.
SELECT SUM(quantity)
You can apply additional dimensions (in this case Product and Year) to this simple metric to break down the metric:
| Metric | Dimensionality | What it returns (business meaning) |
|---|---|---|
| Amount Sold | (empty) | total all-time sales (1 row) |
| Amount Sold | Product | all-time sales for each sold product |
| Amount Sold | Product, Year | # of items sold each year of each product |
Changing filters, dimensionality and nesting results in different returns.
Example 2
SELECT SUM(quantity) WHERE SUM(quantity) > 25
The dimensionality of the result and dimensionality of the filter are the same and defined by the dimensionality of the report.
| Metric | Dimensionality | What it returns (business meaning) |
|---|---|---|
| Amount Sold 25+ | (empty) | Total sales of everything every time (if the total is >25). |
| Amount Sold 25+ | Product | Sales for each product if sales for that product is >25. |
| Amount Sold 25+ | Product, Year | Sales of each product each year if it was sold at least 25 pieces of that product within that year. |
The filter is different for each report dimensionality.
Example 3
SELECT SUM(quantity) WHERE (SELECT SUM(quantity) BY Product ALL OTHER) > 25
The dimensionality of the result (outer) metric is different from dimensionality of the inner metric. The inner (filtering) metric is locked to Product.
| Metric | Dimensionality | What it returns (business meaning) |
|---|---|---|
| Amount Sold 25+ | (empty) | Total sales of products with more than 25 sold items. |
| Amount Sold 25+ | Product | Sales for each product if those sales are >25. |
| Amount Sold 25+ | Product, Year | Sales of each product with total sales >25 split also by year. |