Using the :last_measurement_time bind parameter calculation dependency
Using the :last_measurement_time bind parameter can be perplexing especially for dimensioned metrics. Read on to understand how the system applies the bind parameter across dimensions during data collection.
For metrics, you must specify the time period you are collecting data for in the query. For example, if you state the exact time period in your where clause, like <where date_time between ‘2017-07-01’ and ‘2017-07-07’>, the system fetches data for the period specified. However, you cannot use exact dates when collecting data going forward at set intervals. To handle this dynamically, the :last_measurement_time (LM) bind parameter is used to structure a query in MI to collect data going forward, without specifying exact dates. This bind parameter represents the last date collected by MI that is stored in the system database.
For undimensioned metrics, the LM always represents the last date Metric Insights has collected data for. That’s simple enough. However, for a dimensioned metric, the last date collected for may vary across dimension values. The LM can either be the oldest date across all dimension values (OLDEST LM) or it can be the latest date across all dimension values (LATEST LM). The timeline below represents visually what this looks like in reference to the current date.
How the system determines which LM to use.
There are several variables and additional user settings the system considers when identifying which LM to use:
- single-fetch and batch-fetch variables
Two interval options set in the metric editor:
- On data collection also re-run last
- Batch load data going back no more than.
- Manual data collection via the Recollect Data button or scheduled data collection
<Single fetch> is used to determine a specific dimension value the system should update and collect data for. <batch-fetch> allows the system to perform a bulk collection of all values with one query.
On data collection also re-run last and Batch load data going back no more than are two interval options the system considers when identifying a specific date to collect data from. These settings can be configured in the Metrics Editor > Data tab
These intervals represent the period the data is to be re-fetched for. They can be expressed in number of days, weeks or months – depending on the measurement interval chosen while creating the element.
· For instance, if 5 is set in On data collection also re-run last interval, the data collector will also check for updates going back 5 days from the LM.
· Batch load data going back no more than interval is ALWAYS disregarded when it comes to single-fetched data.
Now, with a dimensioned metric, please refer to the attached file to see how the LM date is selected depending on the conditions involved.