Page tree
Skip to end of metadata
Go to start of metadata

The Multi-Dimension Analytical Query Language (MAQL) is tool for describing metrics in GoodData. Each metric consists of two main parts: aggregation and (optionally) filters.

Aggregation

Since GoodData reports are multi-dimensional pivot tables (not just plain spreadsheets), each metric must define an aggregation. The aggregation is what allows you to change resolution of your report.`

For example, you view your Sales metric not only for an individual line item, but also aggregate sales for the whole day, week, month, for all salesmen in a department or even across the whole company. 

MIN,MAX,SUM

For example, your support data contains a fact field called resolution_time.

To define a metric of average resolution time (over a time period, across a type of question):

SELECT AVG(Resolution time)

 To define the minimum resolution time, maximum resolution time, or total resolution time:

SELECT MIN(Resolution time)
SELECT MAX(Resolution time)
SELECT SUM(Resolution time)

COUNT

To count the number of rows that are being aggregated, use the COUNT function. The COUNT function counts the number of unique values in an attribute:

SELECT COUNT(Ticket_ID)

 If Ticket_ID is a primary key, or CONNECTION_POINT, of the dataset, the COUNT function counts the number of rows of this dataset. For example, you can count the number of unique customers that filed support tickets.

COUNT(Ticket_ID) >= COUNT(Customer)

Multiple datasets

You have a shared dataset. For example, the Employees dimension connects to both Tickets dataset (support tickets were handled by employees) and to Payroll dataset, containing salary data:


To count the number of unique employees that are listed in the Ticket dataset connected to Ticker_ID, use two parameters in the COUNT function:

SELECT COUNT(Employee,Ticket_ID)

 

BY

By default, each metric is aggregated over a scope of inherited by its position in the pivot table. The intersection of Month(March) and City(Boston) aggregates all rows that have those attribute values. This allows metrics to be easily drilled into. For example, you can drill into March to individual days and the metric starts showing numbers for individual days. You might want to override this behavior though when using the metric as part of some larger calculation.

For example, to show how many hours were spend on resolving tickets in a particular month, out of all hours in a quarter, add both the month and quarter aggregation levels in a metric:

SELECT (SELECT SUM(Resolution time)) / (SELECT SUM(Resolution time) BY Quarter)

The SUM BY Quarter metric displays the identical result for all month in the same quarter, since it’s computing results for all months that belong into that quarter.

If the report’s aggregation level is higher then the one defined in the metric BY clause, the metric aggregation is ignored. If you replace the Month attribute with a Year attribute, the metric aggregation (quarter) is ignored:

BY ALL

You want to display the percentage of Resolution time for all time. Since the highest-level attribute in the date dimension is Year, we need a new construct: BY ALL YEAR. that can break down by attributes from other dimesions, such as Customer:

SELECT SUM(Resolution time) BY ALL Year

 

BY attr, ALL IN ALL OTHER DIMENSIONS

You can also create a metric that is fixed at highest aggregation (undrillable) in all dimensions but the date dimension, where it is fixed at the Quarter:

SELECT SUM(Resolution time) BY Quarter, ALL IN ALL OTHER DIMENSIONS


The BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR Quarter metric preserves aggregation in the dimension specified by the attribute (date dimension specified by Quarter in this case) but it never drills in the date dimension beloow the specified by Quarter.

BY ALL IN ALL OTHER DIMENSIONS

To aggregate over all data in all dimensions:


SELECT SUM(Resolution time) BY ALL IN ALL OTHER DIMENSIONS