Usage of the ":measurement_time" Bind Variable

The :measurement_time bind Variable can be used in the SQL statement to create multiple versions of a Dataset for different date values. This article describes how to use :measurement_time bind Variable.

These settings are specified at the Data tab of Dataset Editor.

1. Collecting Data

In this example, let's assume there is a Dataset with SQL fetch statement is the following:

SELECT Product, Sum(Sales) 
FROM Sales_Data

That would get an aggregate data for all time. To see results for a specific date, e.g., January 1, 2013, the query is modified like this:

SELECT Product, Sum(Sales) 
FROM Sales_Data 
WHERE Sale_Date = '2013-01-01'

2. Introducing the Bind Variable

To see results for an arbitrary date without specifying the date, modify the query to look like this:

SELECT Product, Sum(Sales) 
FROM Sales_Data 
WHERE Sale_Date = :measurement_time

The :measurement_time bind Variable is a substitute parameter whose value will be determined at run time.

3. Setting the Value of the Bind Variable

Scroll the Data tab down

  1. Snapshot Dataset?: Snapshot Datasets are associated with keeping Dataset history and having the ability to compare Datasets over time
    • If this field is set to "No", then only the most recent instance of the Dataset will be retained
    • If this field is set to "Yes", then an additional setting will be exposed below, namely, Can historical instances be backfilled? field
  2. Can historical instances be backfilled?: This field is shown for Snapshot Datasets only
    • If this field is set to "No", then only one instance of the Dataset will be computed at run time and it is required to set the value for the :measurement_time variable. It's important to note that while only one instance of the Dataset will be computed at run time, a new instance of the Dataset will be computed at each succeeding refresh interval. Since history is kept, all instances will be retained. This technique can be used to create 'snapshots' of your underlying data at fixed time intervals
    • If this field is set to "Yes", then multiple instances of the Dataset can be computed at run time and the :measurement_time variable is defined automatically by the system
  3. Set :measurement_time value: When it's time to collect data, Metric Insights will substitute one or more values for the :measurement_time bind variable added to a SQL query. The User defines what values will be substituted by making a selection from the Set :measurement_time value dropdown list. The range of choices depends on the Measurement Interval of a given Dataset

For each distinct value of the bind variable, Metric Insights will execute the fetch command and create a new Dataset instance.

NOTE: To learn how to set a custom substitute value in the Set :measurement_time value field check Add new Measurement Time Calculation article.

4. Specifying Data Retention Period

Open Advanced tab

This field is shown for Snapshot Datasets only. Define how many Snapshot Instances will be kept in the History of this Dataset.