InícioMetrics & MAQL
Tags relacionadas : maql reporting métricas
O Multi-Dimension Analytical Query Language (MAQL) é a nossa ferramenta flexível para descrever métricas no GoodData. Cada métrica consiste em duas partes principais: agregação e (opcional) filtros.
Agregação
Uma vez que os relatórios GoodData são tabelas dinâmicas multidimensionais (não apenas planilhas simples), cada métrica deve definir uma agregação. Desta forma, você vê sua métrica (digamos, as Vendas) não só para um item de linha individual, mas também agrega as vendas para todo o dia, semana e mês, para todos os vendedores de um departamento - ou mesmo em toda a empresa. A agregação é o que permite alterar a resolução do seu relatório.
MIN, MAX, SUM
Vamos começar com um exemplo simples e clássico: você é suporte / ticketing / bugzilla data contém um campo de fato chamado resolution_time . Você gostaria de definir uma métrica de tempo médio de resolução (durante um período de tempo, em um tipo de pergunta, etc.)
Embora a medição do tempo médio de resolução possa ser o cenário mais típico, o tempo de resolução mínima, o tempo máximo de resolução ou o tempo total de resolução serão semelhantes:
CONTAGEM
Para determinadas métricas, você pode querer contar o número de linhas que entram que estão sendo agregadas. Existe uma função COUNT precisamente para isso. Na sua encarnação padrão, ele irá contar o número de valores únicos em um atributo:
Se Ticket_ID é uma chave primária de seu conjunto de dados (chamamos isso de CONNECTION_POINT ), isso conterá o número de linhas desse conjunto de dados. Como você pode ver rapidamente, porém, é mais poderoso que apenas contando linhas de um conjunto de dados - você poderia contar o número de clientes únicos que apresentaram os tickets de suporte - e obviamente
If Ticket_ID is a primary key of it’s dataset (we call that a CONNECTION_POINT) then this will count the number of rows of that dataset. As you can quickly see though, it’s more powerful then just counting rows of a dataset - you could count the number of unique customers that filed support tickets - and obviously
?
1
COUNT(Ticket_ID) >= COUNT(Customer)
The issue gets a bit more complicated with multiple datasets. Let’s say we have a shared dataset - for example the Employees dimension. It is connected to both Tickets dataset (support tickets were handled by employees) and to Payroll dataset, containing salary data:
Shared Dimension
Now in this setup adding a metric SELECT COUNT(Employee) to a report will render both dataset Tickets and Payroll unusable. The reason is that COUNT will scan all lines of the Employee attribute, but only some of them might be actually connected to Ticket_ID or Invoice_ID. Thus, a second, optional (but very useful) parameter is used in the COUNT function:
?
1
SELECT COUNT(Employee,Ticket_ID)
This will count the number of unique employees that are listed in the Ticket dataset (connected to Ticket_ID). As a result, no employees out of the Ticket dataset scope are used and you can use all other attributes/metrics from the dataset.
Shared Dimension
BY
By default, each metric is aggregated over a scope of inherited by it’s position in the pivot table. The on intersection of Month(March) and City(Boston) will aggregate all rows that have those corresponding attribute values. This allows metrics to be easily drilled into (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 when showing how many hours were spend on resolving tickets in a particular month, out of all hours in a quarter - you need both the month and quarter aggregation levels in a metric:
?
1
SELECT (SELECT SUM(Resolution time)) / (SELECT SUM(Resolution time) BY Quarter)
To better illustrate how this works, let’s look at all three metrics side by side:
By Metric Comparison
You can see that 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’s aggregation is ignored. Continuing with this example, if we replace the Month attribute with a Year attribute, the differences between the metrics will be ignored:
By Clause Ignored
BY ALL
Let’s say we’d like to create showing percentage of Resolution time not just out of the Quarter or Year, but all time. Since the highest-level attribute in the date dimension is Year, we need a new construct: BY ALL YEAR. This fixes the aggregation for all years, but still can break down by attributes from other dimesions. This is best shown by adding a second attribute from a different dimension into our report - for example Customer:
?
1
SELECT SUM(Resolution time) BY ALL Year
By All Year
BY attr, ALL IN ALL OTHER DIMENSIONS
The opposite problem would be to create a metric that is fixed at highest aggregation (undrillable) in all dimension but the date dimension, where it is fixed at the Quarter level:
?
1
SELECT SUM(Resolution time) BY Quarter, ALL IN ALL OTHER DIMENSIONS
By Quarter, All Other
Another similar construct is BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR Quarter. The only difference between these two constructs is that EXCEPT FOR preserves natural aggregation in the dimension specified by the attribute (date dimension specified by Quarter in this case) but Quarter, ALL ... construct will never drill in the date dimension bellow the level specified by Quarter.
BY ALL IN ALL OTHER DIMENSIONS
Finally, the ultimate metric that aggregates over all data in all dimensions is plain and simply:
?
1
SELECT SUM(Resolution time) BY ALL IN ALL OTHER DIMENSIONS
By All In All Other
Further Reading
More documentation on MAQL metrics including filtering, time-based floating filters, year-to-date metrics etc. can be found in Reporting Section.
Related Content
Creating Analytical Project with MAQL DDL
Painless Disconnected Date Dimensions
Cross Data Set Filtering with Sub Metrics
Introducing the new RANKs
Flexible querying with a disconnected date dimension
https://developer.gooddata.com/article/metrics-and-maql
https://translate.google.com.br/translate?hl=pt-BR&sl=en&tl=pt&u=https%3A%2F%2Fdeveloper.gooddata.com%2Farticle%2Fmetrics-and-maql
Tags relacionadas : maql reporting métricas
O Multi-Dimension Analytical Query Language (MAQL) é a nossa ferramenta flexível para descrever métricas no GoodData. Cada métrica consiste em duas partes principais: agregação e (opcional) filtros.
Agregação
Uma vez que os relatórios GoodData são tabelas dinâmicas multidimensionais (não apenas planilhas simples), cada métrica deve definir uma agregação. Desta forma, você vê sua métrica (digamos, as Vendas) não só para um item de linha individual, mas também agrega as vendas para todo o dia, semana e mês, para todos os vendedores de um departamento - ou mesmo em toda a empresa. A agregação é o que permite alterar a resolução do seu relatório.
MIN, MAX, SUM
Vamos começar com um exemplo simples e clássico: você é suporte / ticketing / bugzilla data contém um campo de fato chamado resolution_time . Você gostaria de definir uma métrica de tempo médio de resolução (durante um período de tempo, em um tipo de pergunta, etc.)
- Código:
1
SELECT AVG(Resolution time)
Embora a medição do tempo médio de resolução possa ser o cenário mais típico, o tempo de resolução mínima, o tempo máximo de resolução ou o tempo total de resolução serão semelhantes:
- Código:
SELECT MIN(Resolution time)
SELECT MAX(Resolution time)
SELECT SUM(Resolution time)
CONTAGEM
Para determinadas métricas, você pode querer contar o número de linhas que entram que estão sendo agregadas. Existe uma função COUNT precisamente para isso. Na sua encarnação padrão, ele irá contar o número de valores únicos em um atributo:
- Código:
1
SELECT COUNT(Ticket_ID)
Se Ticket_ID é uma chave primária de seu conjunto de dados (chamamos isso de CONNECTION_POINT ), isso conterá o número de linhas desse conjunto de dados. Como você pode ver rapidamente, porém, é mais poderoso que apenas contando linhas de um conjunto de dados - você poderia contar o número de clientes únicos que apresentaram os tickets de suporte - e obviamente
- Código:
<span class="notranslate" onmouseover="_tipon(this)" onmouseout="_tipoff()"><span class="google-src-text" style="direction: ltr; text-align: left">COUNT(Ticket_ID) >= COUNT(Customer)</span> COUNT (Ticket_ID)> = COUNT (Cliente)</span>
If Ticket_ID is a primary key of it’s dataset (we call that a CONNECTION_POINT) then this will count the number of rows of that dataset. As you can quickly see though, it’s more powerful then just counting rows of a dataset - you could count the number of unique customers that filed support tickets - and obviously
?
1
COUNT(Ticket_ID) >= COUNT(Customer)
The issue gets a bit more complicated with multiple datasets. Let’s say we have a shared dataset - for example the Employees dimension. It is connected to both Tickets dataset (support tickets were handled by employees) and to Payroll dataset, containing salary data:
Shared Dimension
Now in this setup adding a metric SELECT COUNT(Employee) to a report will render both dataset Tickets and Payroll unusable. The reason is that COUNT will scan all lines of the Employee attribute, but only some of them might be actually connected to Ticket_ID or Invoice_ID. Thus, a second, optional (but very useful) parameter is used in the COUNT function:
?
1
SELECT COUNT(Employee,Ticket_ID)
This will count the number of unique employees that are listed in the Ticket dataset (connected to Ticket_ID). As a result, no employees out of the Ticket dataset scope are used and you can use all other attributes/metrics from the dataset.
Shared Dimension
BY
By default, each metric is aggregated over a scope of inherited by it’s position in the pivot table. The on intersection of Month(March) and City(Boston) will aggregate all rows that have those corresponding attribute values. This allows metrics to be easily drilled into (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 when showing how many hours were spend on resolving tickets in a particular month, out of all hours in a quarter - you need both the month and quarter aggregation levels in a metric:
?
1
SELECT (SELECT SUM(Resolution time)) / (SELECT SUM(Resolution time) BY Quarter)
To better illustrate how this works, let’s look at all three metrics side by side:
By Metric Comparison
You can see that 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’s aggregation is ignored. Continuing with this example, if we replace the Month attribute with a Year attribute, the differences between the metrics will be ignored:
By Clause Ignored
BY ALL
Let’s say we’d like to create showing percentage of Resolution time not just out of the Quarter or Year, but all time. Since the highest-level attribute in the date dimension is Year, we need a new construct: BY ALL YEAR. This fixes the aggregation for all years, but still can break down by attributes from other dimesions. This is best shown by adding a second attribute from a different dimension into our report - for example Customer:
?
1
SELECT SUM(Resolution time) BY ALL Year
By All Year
BY attr, ALL IN ALL OTHER DIMENSIONS
The opposite problem would be to create a metric that is fixed at highest aggregation (undrillable) in all dimension but the date dimension, where it is fixed at the Quarter level:
?
1
SELECT SUM(Resolution time) BY Quarter, ALL IN ALL OTHER DIMENSIONS
By Quarter, All Other
Another similar construct is BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR Quarter. The only difference between these two constructs is that EXCEPT FOR preserves natural aggregation in the dimension specified by the attribute (date dimension specified by Quarter in this case) but Quarter, ALL ... construct will never drill in the date dimension bellow the level specified by Quarter.
BY ALL IN ALL OTHER DIMENSIONS
Finally, the ultimate metric that aggregates over all data in all dimensions is plain and simply:
?
1
SELECT SUM(Resolution time) BY ALL IN ALL OTHER DIMENSIONS
By All In All Other
Further Reading
More documentation on MAQL metrics including filtering, time-based floating filters, year-to-date metrics etc. can be found in Reporting Section.
Related Content
Creating Analytical Project with MAQL DDL
Painless Disconnected Date Dimensions
Cross Data Set Filtering with Sub Metrics
Introducing the new RANKs
Flexible querying with a disconnected date dimension
https://developer.gooddata.com/article/metrics-and-maql
https://translate.google.com.br/translate?hl=pt-BR&sl=en&tl=pt&u=https%3A%2F%2Fdeveloper.gooddata.com%2Farticle%2Fmetrics-and-maql
