A metric is an expression which describes a computation that will be performed over the project's data. Metrics are written in a custom query language, which is fairly simple and doesn't require users to know SQL. A metric written in this language is then translated to SQL by a backend service and executed as multidimensional query on data warehouse.
...
All properties used in metrics are dataset properties, located in dwh.ref.properties
. On these properties, a set of functions can be applied. They can be nested, and the results of these functions can be combined and filtered. For better maintainability, metrics can be composed of other metrics.
Syntax
This is the simplest type of metric. It returns the sum (function_sum
) of all basket amounts (property baskets.amount
).
...
This is a metric which uses metric variables. The resulting sum of an exposure index is simply multiplied by the value substituted in the index_variable
. The variables are set in the variables
filter in the view object.
For better maintainability, metrics can be composed of other metrics. The following example shows a new offline_turnover_ratio_metric
that reuses two already defined metrics:
Code Block |
---|
{
"name": "offline_turnover_ratio_metric",
"type": "metric",
"content": {
"type": "function_divide",
"content": [
{
"type": "metric",
"metric": "/rest/projects/$projectId/md/metrics?name=offline_turnover_metric"
},
{
"type": "metric",
"metric": "/rest/projects/$projectId/md/metrics?name=turnover_metric"
}
]
}
} |
Key description
content
Key | Type | Optionality | Description | Constraints | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
type | string |
| dwh query property, dwh query function, or a number |
| ||||||||
content | array |
| array of dwh property definitions (see the | |||||||||
options | object |
| function options, allows to specify filters |
...
Key | Type | Optionality | Description | Constraints | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
id | string |
| choose a custom string identifier for the query property | (a-z0-9_-) | ||||||||
type | string |
| dwh query property, dwh query function, or a number |
| ||||||||
value | string long decimal |
| string identifier of a dataset property, which the function will be applied to (for string with variable name (for long or decimal value (for |
|
content.options
Key | Type | Optionality | Description | Constraints | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
aggregateBy | array |
| specifies datasets which the metric will be aggregated to if specified, always | [null] | ||||||||
acceptAggregateBy | array |
| specifies datasets which the metric is allowed to be aggregated to array of dataset names, or name prefixes (with the only one of |
| ||||||||
dontAggregateBy | array |
| specifies datasets which the metric is not allowed to be aggregated to array of dataset names, or name prefixes (with the only one of |
| ||||||||
withoutFilters | array |
| specifies dataset properties not to be explicitly joined into the final query array of dataset properties only one of |
| ||||||||
acceptFilters | array |
| specifies dataset properties to be explicitly joined into the final query array of dataset properties only one of |
| ||||||||
filterBy | object |
| object specifying the filter of the metric | |||||||||
places | integer |
| the number of places to round the metric result to only for a metric with |
...
Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.
Identifier | Description | Example |
---|---|---|
| integer ranging from 1 to the argument value, dividing the partition as equally as possible |
|
function_rank | rank of the current row with gaps |
|
function_percentile | value below which a given percentage of observations in a group of observations fall |
input values sorted = (105.9, 75.2, 61.1, 42.7, 35.2, 21, 10)
|
function_row_number | a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrary assigned. |
|
...
function_plus
,function_
minus
andfunction_
multiply
accept 2 or more argumentsfunction_divide
andfunction_modulo
accept exactly 2 arguments
Identifier | Example |
---|---|
|
|
function_minus |
|
function_multiply |
|
function_divide | 7 / 2 = 3.5 |
function_modulo | 7 mod 2 = 1 |
...
Identifier | Description | Example |
---|---|---|
function_round | a rounding function (round to specific number of places) | function_round(56.157094235, 1) = 56.2 |
Conditional functions
Identifier | Description | Example |
---|---|---|
| defines the default value to use if the result of aggregate function is null |
|
...
Identifier | Description | Example |
---|---|---|
eq | is equal |
|
ne | negated |
|
in | in range |
|
lt | lower than |
|
lte | lower than or equal |
|
gt | greater than |
|
gte | greater than or equal |
|
isNull | is null |
|
isNotNull | is not null |
|
inAttribute | is in attribute | see the usage in a complete metric example above |
notInAttribute | is not in attribute | complementary to the inAttribute example |
...