Measures
Measures are aggregations applied to every segment in your data. They can give you an overview of the segment, and help distinguish important segments from less important ones. Measures live in the measure panel.
You can edit them in the Measures
tab of the data cube edit view:
Creating measures
Measures can be created by clicking the New measure
button in the Measures
tab.
Simple measures consisting of a single aggregate function over a single column (with an optional filter) can be configured from the Simple measure
tab.
A measure can also represent some post aggregation or computation (see the specific measure types section below).
In that case you would use the Custom measure
tab, where you can enter any supported Plywood
expression as the measure's formula.
The measure's formula assumes that $main
is the Plywood reference to the data that the measure will be aggregating over.
Imply can also provide some suggestions about simple measures that you might want to add. This is done by scanning the schema of the underlying data source and automatically suggesting a measure, with the appropriate aggregate, for any column that is not already represented by one of the existing measures.
This is particularly useful if you have added a new column to your data source after creating the data cube and now want to represent it in your views.
Measure groups
You can group related measures into measure groups. This can be particularly useful for measures which come from the same basic attribute or are otherwise similar.
For example in a sales dataset scenario you might be interested in Total revenue
, Min revenue
, Max revenue
, Avg revenue
, Median revenue per user
, and more.
It might therefore make sense to group those measures.
To create a group simply click the ...
icon in one of the measures and select Add to a new group
.
Then drag the relevant measures into the newly created group.
Measure formatting
You can configure how a measure is formatted from the Format
tab when creating or editing a measure.
Here you can select from a list of abbreviation sets that can be applied to format the measure, and adjust the decimal precision that will be displayed.
Additionally, when applying time comparisons to measure values, you can configure the display coloring for increased and decreased values.
Measure fill options
You can specify how a measure should be filled on continuous visualizations (such as the line chart).
There are four possible fill options, demonstrated above by four measures that are configured the same, with the exception of different fill options.
The possible options are:
- Zero filled (default) – fill missing data with zeros. This is most suitable if the measure represents an additive quantity.
- No fill – leave missing data empty. This is suitable when missing values would likely indicate that the data was not collected.
- Previous value – fill missing data with last value seen. This is suitable for sensor type data.
- Interpolate values – interpolate the missing data between the seen value. This is suitable for sensor type data.
Measure transformations
In the Advanced
tab, you can transform a measure to be displayed as a Percent of parent segment
or as Percent of total
instead of the default measure display.
Specific measure types
In this section we will look at some of the many specific measure types supported.
Filtered aggregations
Filtered aggregations are very powerful. If, for example, your revenue in the US is a very important measure, you could express it as:
$main.filter($country == 'United States').sum($revenue)
It is also common to express a ratio of something filtered vs unfiltered.
$main.filter($statusCode == 500).sum($requests) / $main.sum($requests)
Ratios
Ratios are often useful to see the relationships in your data.
Here's one that expresses the computation of CPM:
$main.sum($revenue) / $main.sum($impressions) * 1000
Quantiles
A quantile can be a very useful measure of your data. For large datasets it is often more informative to look at the 98th or 99th quantile of the data rather than the max, as it filters out freak values. Similarly a median (or 50% quantile) can present different information than an average measure.
To add a quantile measure to your data simply define a formula like so:
$main.quantile($revenue, 0.98)
It is possible to fine-tune approximateHistogram based quantiles, allowing you to determine your trade-off between performance and accuracy.
Enter a 3rd parameter in the quantile formula of the form 'resolution=400,numBuckets=10,lowerLimit=0,upperLimit=1000'
to pass those tuning parameters to the underlying aggregator.
To understand how to tune the approximateHistogram parameters, see the Druid documentation
Rates
Sometime the rate of change (over time) of a measure is very important.
There is a magic constant available for expressions $MillisecondsInInterval
that corresponds to the number
of milliseconds in the time interval over which the aggregation (whatever it is) is running.
Therefore it is possible to define a measure like
$main.sum($bytes) / $MillisecondsInInterval * 1000
to give you the accurate rate of bytes per second regardless of your filter window or selected split (hour, minute, or whatever).
Double aggregation measures
It is possible to define measures that perform a sub-split and a sub aggregation as part of their overall aggregation. This is needed to express certain calculations which otherwise would not be possible.
The general form of a double aggregated measure's formula is:
$main.split(SUB_SPLIT_EXPRESSION).apply('V', $main.INNER_AGGREGATE(...)).OUTER_AGGREGATE($V)
Where:
SUB_SPLIT_EXPRESSION
is the expression on which the data for this measure would be first splitINNER_AGGREGATE
is the aggregate that would be calculated for each bucket from the sub-split and will be assigned the nameV
(which is arbitrary)OUTER_AGGREGATE
is the aggregate that will aggregate over the results of the inner aggregate, it should use the variable name declared above
Two examples of double aggregated measures are provided:
Netflow 95th percentile billing
When dealing with netflow data at an ISP level one metric of interest is 5 minutely 95th percentile, which is used for burstable billing.
To add that as a measure (assuming there is a column called bytes
which represents the bytes transferred during the interval), set the formula as follows:
$main.split($__time.timeBucket(PT5M)).apply('B', ($main.sum($bytes) * 8) / 300).quantile($B, 0.95)
Here the data is sub-split on 5 minute buckets (PT5M), then aggregated to calculate the bitrate (8 is the number of bits in a byte and 300 is the number of seconds in 5 minutes). Those inner 5 minute bitrates are then aggregated with the 95th percentile.
Average daily active users
When looking at users engaging with a website, service, or app, we often need to know the number of daily active users.
This can be calculated as:
$main.split($__time.timeBucket(P1D)).apply('U', $main.countDistinct($user)).average($U)
Here the data is sub-split by day (P1D), and then an average is computed on top of that.
Switching metric columns
If you switch how you ingest your underlying metric and can't (or don't want to) recalculate all of the previous data, you could use a derived measure to seamlessly merge these two metrics in the UI.
Let's say you had a metric called revenue_in_dollars
, and for some reason you will now be ingesting it as revenue_in_cents
.
Furthermore, right now your users are using the following measure:
$main.sum($revenue_in_dollars)
If your data had a 'clean break' where all events have either revenue_in_dollars
or revenue_in_cents
with no overlap, you could use:
$main.sum($revenue_in_dollars) + $main.sum($revenue_in_cents) / 100
If instead there was a period where you were ingesting both metrics, then the above solution would double count that interval. You can 'splice' these two metrics together at a specific time point.
Logically, you should be able to leverage Filtered aggregations to do:
$main.filter(__time < '2016-04-04T00:00:00Z').sum($revenue_in_dollars) + $main.filter('2016-04-04T00:00:00Z' <= __time).sum($revenue_in_cents) / 100
Custom aggregations
Within measures, you have access to the full power of Plywood expressions. If you ever find yourself needing to go beyond the expressive potential of Plywood, you can define your own custom aggregations. These aggregations can be any supported Druid aggregation.
For example, Plywood currently does not support the modulo operator. While Druid has no native modulo support either, it is possible to modulo a measure by using a JavaScript aggregator.
To do so, in the data cube options (Advanced
tab of the edit view), define:
{
"customAggregations": {
"addedMod1337": {
"aggregation": {
"type": "javascript",
"fieldNames": ["added"],
"fnAggregate": "function(current, added) { return (current + added) % 1337 }",
"fnCombine": "function(partialA, partialB) { return (partialA + partialB) % 1337 }",
"fnReset": "function() { return 0; }"
}
}
}
}
Then reference addedMod1337
in a measure's formula like so:
$main.customAggregate('addedMod1337')
This functionality can be used to access any custom aggregations that might be loaded via extensions.
{
"customAggregations": {
"fancyCustom": {
"aggregations": [
{
"type": "longSum",
"name": "a_{{random}}",
"fieldName": "added"
},
{
"type": "count",
"name": "b_{{random}}"
}
],
"postAggregation": {
"type": "expression",
"expression": "(\"a_{{random}}\" / \"b_{{random}}\")",
}
}
}
}
Note: that if a postAggregation
is defined then the intermediate aggregation names have to have a {{random}}
added to
them to ensure that at query times multiple instances of this aggregation can be resolved such as using this custom aggregation
in multiple measures or as a compare measure.