Writing a SQL Statement for a Simple (Undimensioned) Metric

This article describes the requirements of an SQL statement that fetches data for an Undimensioned Metric

For a step-by-step guide on creating a new Metric using the Metric Wizard, see Create a Simple (Undimensioned) Metric

Open the Metric Editor

Access +New > Metric to create a new Metric or Content > Elements to open an existing Metric and go to the Metric Editor

Open the Data tab to find the SQL Statement field.

SQL Statement Requirements

The SQL statement for an undimensioned Metric must return exactly two columns in the result set:

  1. Measurement date/time
  2. Measurement value (integer/float)

Measurement date/time must be returned in ANSI date format, with or without the time: "YYYY-MM-DD" OR "YYYY-MM-DD HH:MM:SS". These values in the data set are automatically normalized based on the Measurement Interval set for the metric.  For the most commonly used Intervals:

  • Daily: Set to the beginning of the day.  e.g. '2012-12-28 12:22:33' is truncated to '2012-12-28 00:00:00'
  • Weekly: Set to the last day of the calendar week based on the calendar week table in the Metric Insights database.  The default week is Sunday through Saturday.
  • Monthly: Converted to fall on the first day of the calendar month.  e.g. '2012-12-28' is converted to '2012-12-01'  

Fetching One Row per Measurement Period

It is important to ensure that only a single row is returned in the data set for a given normalized date.  If the data set contains multiple rows that, when normalized, fall on the same date, the last value collected is retained and all other values are discarded.  For example, the SQL Statement for a Daily metric returned the following 2 rows:

'2012-12-28 00:32:00' 25

'2012-12-28 08:12:00' 44

Both of the above date/time values would be normalized to '2012-12-28 00:00:00' and the first row in the result set would be discarded (only the value of 44 would be recorded for the specified date).

Similarly, for a monthly metric, the following results were returned:

'2012-12-15 00:00:00' 25

'2012-12-28 00:00:00' 44

Both of the above date/time values would be normalized to '2012-12-01 00:00:00'  (the normalized date for monthly periods) and the first row in the result set would be discarded (only the value of 44 would be recorded for the specified date).

Fetching Data Incrementally

In some cases, you may want to only fetch new values rather than re-collecting all historical data whenever the SQL statement is run. If you would like to fetch data incrementally, include the :last_measurement_time variable in your SQL statement. This variable is set at run-time to the measurement time of the most recent metric value collected in the Metric Insights repository

If you want to copy the required variable to your clipboard for ease in writing your statement, just click on the variable name.

If you do not include :last_measurement_time in your SQL statement, Metric Insights will re-fetch ALL historical values. It will add new metric values and update existing metric values based on the result set of your query. Once a value has been collected into Metric Insights, it will not be deleted if it is not in a subsequent data fetch.