Custom SQL Aggregations - m3ter Documentation

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
  • Example 1 - Monthly Billing, Daily Rating
  • Example 2 - Group by Dimension
  • Example 3 - Reserved Instances (RIs) with Overages
  • Example 4 - Custom SQL Descriptions
• Supported SQL Functions

Custom SQL Aggregations in Preview Release. Please note that the Custom SQL Aggregations feature is currently available in Preview release version:

• See Feature Release Stages for Preview release definition and guidance.

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.

5. Configure Meter settings:

   • Meter. Select the Meter whose Data Field or Derived Field you want to use as the basis for the Aggregation.
   • 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:

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:

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

   • 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.

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.

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.
• Dimensions can be accessed using map notation. For example, to access the value of a dimension called “region”, you would write dimensions['region'].

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.

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 3 - Reserved Instances (RIs) with Overages

Example 1 - Monthly Billing, Daily Rating

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

Example 2 - Group by Dimension

SELECT SUM(measure) AS value, dimensions['vehicle_id'] as vehicle_id
FROM measurements
GROUP BY dimensions['vehicle_id']

Example 3 - Reserved Instances (RIs) with Overages

SELECT SUM(
  CASE((measure - counters['ri']) > 0) WHEN 1 THEN (measure - counters['ri']) ELSE 0 END
) AS value
FROM measurements

Example 4 - Custom SQL Descriptions

If you are using Custom SQL Aggregations to price Plans, this feature offers you wide flexibility when setting up Billing line item descriptions.

Default Custom SQL Descriptions

If you do not define a description on the pricing you configure for a Plan using a Custom SQL Aggregation, then a default description is used that includes any key/value pairs used in the GROUP BY clause: (<key/value 1>,<key/value 2>, <key/value 3> …).

Supported SQL Functions

The following functions are supported in Custom SQL Aggregations:

• AVG
• SUM
• COUNT
• MAX
• MIN
• ROUND
• FLOOR
• CEIL
• CAST
• COALESCE
• LEAST
• GREATEST
• DATE_TRUNC
• FIRST_VALUE
• LAST_VALUE
• ROW_NUMBER
• ORDER BY