• Home
/
• Working in m3ter - User Guides
/
• Pricing Metrics: Aggregations

Custom SQL Aggregations

Custom SQL Aggregations are a powerful new feature that offer you more flexibility than traditional simple Aggregations such as SUM, MAX, and COUNT when defining your billing metrics.

As an additional option in the existing Aggregation layer, Custom SQL Aggregations can be used in combination with Segmentation and any existing pricing model.

This topic explains how to create Custom SQL Aggregations and provides some examples:

• Creating Custom SQL Aggregations

• Creating Custom SQL Queries

• Custom SQL Aggregations - Examples

• Supported SQL Functions

Creating Custom SQL Aggregations

To create Custom SQL Aggregations in the m3ter Console you can follow similar steps as when you create other types of Aggregation using the standard aggregation methods, such as SUM, COUNT, MAXIMUM, and so on.

To create a Custom SQL Aggregation:

1. Select Metering>Aggregations. The Aggregations page opens.

2. In the Product drop-down, select the Product for which you want to create the new Custom SQL Aggregation.

3. Select Create aggregation. The Aggregations>Create page opens.

4. Configure Aggregation details and enter:

• Name. (Required)

• Code. (Required)

• Accounting product. Use the drop-down to select a Product. (Optional)

  • For accounting purposes, you can use this to link to a specific Product any usage line items on Bills that result from pricing a Plan using this Aggregation:

    • If you've also defined an Accounting product for a Pricing that uses this Aggregation, then the Pricing Accounting product takes precedence and is used.

    • If no Accounting product is defined for a Pricing and you omit an Accounting product for the Aggregation, then the Product the Plan belongs to is used.

5. Configure Meter settings:

• Meter. Select the Meter whose Data Field or Derived Field you want to use as the basis for the Aggregation. Note that only those Meters created for the selected Product will be available. If you're creating a Global Aggregation only Global Meters will be available.

• Target Field. When you select a Meter, the Target Field drop-down list automatically populates with the Codes of any fields set up on that Meter:

  • Note that you must select a measure target field on the Meter - that is, of type Measure, Income, or Cost.

6. Configure Aggregation settings:

• Aggregation. Select Custom SQL for the aggregation method. The page adjusts to show an SQL text entry box where you can enter your SQL query expression. For example:

For details on creating Custom SQL queries for use in your Aggregations, please see the following Creating Custom SQL Queries section.

• Unit. This will be used as a label for billing to indicate to your customers what they are being charged for.

• Quantity per unit. Enter the quantity by which you want to charge for the measured value.

• Rounding. Specifies how you want m3ter to deal with non-integer, that is fractional number, Aggregation values.

7. Select Create aggregation. The Aggregation details page opens:

On the Aggregation details panel, you can:

• Read-off the Aggregation's Name and Code.

• Use a hotlink text to open the details page of the Meter whose Data Field the Aggregation targets.

• Review the SQL and Copy this to your clipboard.

  • Note that for this example, because the selected Meter Target field for the Aggregation is gbyte_store, then it is this field that is accessible in SQL as a field called measure.

• Check the Unit configured for the Aggregation, which will be used on Bill line items to indicates what the charge is for.

• Copy the Aggregation's ID to your clipboard.

• Check the audit data to see who Created and who Last modified the Aggregation.

Creating Custom SQL Queries

This section provides details and guidance for creating queries for your Custom SQL Aggregations. Please review this section in preparation for creating Custom SQL queries:

• Measurements Table

• Key Points

• Limit on GROUP BY Clause

Measurements Table

Custom SQL queries should be run against the measurements table. This table is provided to your SQL query and is already limited to the appropriate Account, time period, and so on that the Aggregation needs to run over, so you don’t need to include anything in your own SQL for these factors.

Several columns of the measurements table are made available to your Custom SQL queries:

Key Points

When creating your SQL queries, please note the following key points:

• The result(s) of the query must be returned as a numeric column called value.

• Queries can return up to 1,000 rows.

• Any additional columns returned (apart from value) are treated as “group keys” and are used downstream – this can be useful if the query returns multiple rows to help identify each row.

• If there are multiple rows, each row will be rated independently and appear as separate line items on the bill.

• Dimensions can be accessed using map notation. For example, to access the value of a dimension called “region”, you would write dimensions['region'].

• The group key values can be accessed in line item descriptions using the syntax {group.columnName} (where columnName is the name of the column you returned from your custom SQL query).

Limit on GROUP BY Clause

The limit on the use of GROUP BY clauses for a Custom SQL Aggregation depends on whether or not the Aggregation is segmented. This is because the “unique keys” in the operation will implicitly include segments:

• An SQL query on an unsegmented Aggregation could process up to 50,000 unique keys from custom SQL.

• An SQL query on a segmented Aggregation will use some of those keys for the segment values (up to 1,000 depending on the data). In the worst case, if the data actually contained 1,000 different segment values, you’d only have 50 unique values for your own data that you are grouping by. This is because each of your own “keys” would be multiplied by (up to) 1,000 segment values,

• For example, if your data had keys of “a”, “b”, and “c”:

  • An unsegmented Aggregation would see these 3 keys.

  • However, if you segmented the Aggregation and each of your groups contained data for 3 segments: “1”, “2”, and “3”, then the total number of keys “seen” by the query will be:

    • “a”, “1”

    • “a”, “2”

    • “a”, “3”

    • “b”, “1”

    • “b”, “2”

    • “b”, “3”

    • “c”, “1”

    • “c”, “2”

    • “c”, “3”

Which is 9 keys in total.

Custom SQL Aggregations - Examples

This section offers some example billing use cases fulfilled using Custom SQL Aggregations:

• Example 1 - Monthly Billing, Daily Rating

• Example 2 - Group by Dimension

Example 1 - Monthly Billing, Daily Rating

In this example you have tiered pricing with a usage allowance that resets daily - but you bill monthly. This use case is simple to fulfill with Custom SQL Aggregations - we can group by the day based on the measurement timestamp:

1
SELECT SUM(measure) AS value, DATE_TRUNC('day', ts) AS date
2
FROM measurements
3
GROUP BY DATE_TRUNC('day', ts)

Example 2 - Group by Dimension

Suppose you price based on a simple total of the distance driven by vehicles each month. Each measurement has a dimension of vehicle_id on it, indicating which vehicle drove a certain distance.

Currently, we support taking a SUM over the entire dataset, regardless of the value of the vehicle_id field. We also support segmentation, but to configure segments you’d need to know and configure the vehicle ids ahead of time, and there’s a per-Organization limit of 1000.

This means that before Custom SQL Aggregations, for non-trivial pricing (such as tiered or volume pricing) the final cost is determined by the total distance driven by all vehicles combined.

Instead, we want to rate each vehicle independently, so that the first 100 miles driven by each vehicle is more expensive than subsequent miles. This would be possible by writing custom SQL which groups the total distance travelled by vehicle_id as follows:

1
SELECT SUM(measure) AS value, dimensions[‘vehicle_id’] as vehicle_id
2
FROM measurements
3
GROUP BY dimensions[‘vehicle_id’]

The Meter definition would (for example) have these fields on it:

• distance (a measure field)

• vehicle_id (a dimension field)

You would pick distance as the target field in the Custom SQL Aggregation configuration, which is then accessible in SQL as a field called measure.

Supported SQL Functions

The following functions are supported in Custom SQL Aggregations:

• AVG

• CAST

• CEIL

• COUNT

• DATE_TRUNC

• FIRST_VALUE

• FLOOR

• GREATEST

• LAST_VALUE

• LEAST

• MAX

• MIN

• ORDER_BY

• ROUND

• ROW_NUMBER

• SUM

Next: Reviewing Aggregation Details

Additional Support