m3terDocumentation

On this page

Performing Usage Queries and Reviewing Results

You can use the Usage data explorer to configure and perform queries against the metered usage data collected for your Organization in m3ter. You can quickly set up and submit a query that returns the metered data set needed to serve your current analytical purposes:

  • To build a usage data query, you can select a time period. Any Meters will be available for selection that have numeric Data Fields configured - MEASURE, INCOME, or COST types - and have had data submitted during the time period for those numeric fields.

  • Select an Aggregation function to apply to the numeric data that is returned by your query.

  • You can also filter the data returned by your query:

    • By Account - select only those end-customer Accounts you want data returned for.

    • Using other Meter dimensions, which are any string Data Field types also configured for the selected Meters - WHO, WHAT, WHERE, or OTHER types. For example, you might want your query to only return numeric data for usage data measurements submitted for specific WHO field values.

      • Note that METADATA string fields are not made available for filtering the data returned.

You can then review the data in either a flat Data Table or a Pivot Table format.

This topic explains how to work with the Usage data explorer to configure and perform queries against your usage data and review the returned data.

Important! Required Permissions for performing Usage data queries. If you create a Custom Permission Policy for your Organization Users to be able to perform Usage queries and view the results, ensure the Permission Policy allows:

  • Create and Retrieve action for the config:analyticsJob Resource.

  • Retrieve action for the analytics:usage Resource.

For more details on working with Permission Policies, see Understanding, Creating and Managing Permission Policies.

Important! Data retrieval from Parent/Child Accounts. When you query for usage data for a Parent Account or any of its Child Accounts, the usage data is treated as entirely separate. The data returned for a Parent Account will contain none of the data collected for any of its Child Accounts. If you want data from a Child Account, you must include the Account in the query you submit.

Building and Performing a Usage Data Query

To submit queries for metered usage data and review returned data:

1. Select Metering>Usage data explorer. You can use the Usage query builder to build a query for metered usage data collected for your Organization.

2. In the Select period section, define a period to retrieve usage data for:

  • Time period. Use the drop-down list to select a period for which you want to retrieve usage data. Today is the default. If you want to select a specific period, you can use Custom:

    • Today. All of today - that is, the 24 hour period from midnight to midnight. This saves you from having to reset the query time period to catch other new pieces of data submitted later that same day.

    • Yesterday. All of yesterday - that is, the 24 hour period from midnight to midnight yesterday.

    • Week to date/Month to date. Period covering the current week/month to date. For example:

      • Selecting Month to date on April 14th, 2024 means your query returns data for the time period starting April 1st, 2024 and including the entire current day - April 14th - and ending at midnight.

      • Weeks run Monday to Monday, so selecting Week to date on a Thursday means your query returns data for time period starting Monday of that week and including the entire Thursday.

      • In both cases, this saves you from having to reset the query time period to catch other new pieces of data submitted later that same day.

    • Last 7/30/90/120 days. Period covering the selected number of whole days leading up to today. For example, selecting Last 30 days on April 8th, 2024 means your query returns data for the time period commencing 30 days prior - March 9th, 2024 - and ending midnight on April 7th, 2024.

    • Previous week/month/quarter. Period covering the entire previous week/month/quarter. For example, selecting Previous month on April 8th, 2024 means your query returns data for the whole of March 2024.

    • Custom. Define the time period for which you want your query to return data.

  • Start (inclusive) and End (exclusive). If you have selected Custom for the query Time period, these date/time fields will be enabled. You can use calendar popups to select the start and end dates/times for the period you want to query.

    • Start dates are explicitly labeled as inclusive and End dates as exclusive. For example, if you want data returned for the entire month of June 2023, you must set the Start at June 1st 2023 and the End at July 1st 2023. If you set the End at June 30th 2023, the Custom Time period for your query will end at midnight on June 29th 2023.

    • Note that when you select Custom for Time period and select the Start and End dates/times, the list of Meters available to filter your query by is restricted to those Meters for which data was submitted in that time period - see below Step 3.

Tip: Timezone for Query Start and End Dates/Time? The Start and End fields will show the timezone you've set for your Organization. See Managing your Organization.

3. In the Filter by meters and measures section, select the Meters and Measures you want to filter your query by.

  • Meters. Select the Meters for which you want to return collected data. The default is All Meters.

    • Note that when you select a Meter, the Filter by meter dimensions section adjusts to show only those fields on the selected Meter as dimensions to filter by - see below Step 5.

  • Measures. Select the numeric Data Fields on the selected Meter or Meters whose data you want to return. These will be any MEASURE, INCOME, or COST Data Fields configured for the Meters.

Warning! Note that the Meters list will show only those Meters whose numeric Data Fields have had data submitted/collected in the time period you've selected for your usage data query.

4. Use the Apply aggregations section to select:

  • Aggregation frequency. Select the frequency for the Aggregation data returned for the query. Note that if you select an Aggregation function, you must select a frequency other than None.

  • Aggregation function. If you want to aggregate the numeric Measures you've selected for your query, select how you want to aggregate the data returned. Default is No Aggregation. Seven options:

    • Count. Returns a count of the number of measure values.

    • Latest. Returns the latest measure value.

    • Max. Returns the maximum measure value.

    • Mean. Returns the arithmetic mean of the returned measure values.

    • Min. Returns the minimum measure value.

    • Sum. Returns the addition of the measure values.

    • Unique. Special case:

      • If you select this as the Aggregation function, it does not apply an aggregation method to the measure values but means your query will return a list of the Meters that have received usage data for the selected time period.

    • Note that if you also select to filter by a Meter dimension - see step 6 below - then this returns a list of Meters and the dimensions used.

5. In the Filter by meter dimensions section, use the Account filter to select the Accounts for which you want to return usage data submitted for the selected Meters:

  • Any Account. Usage data measurements for all Accounts will be returned. An Account Code column will be shown in the Data and Pivot Tables.

  • Select Accounts. Usage data measurements for the selected Accounts will be returned. An Account Code column will be shown in the Data and Pivot Tables.

  • No filter. Default setting. If you leave the Account filter blank, all usage data measurements for the Meter will be returned, regardless of whether an Account code was submitted with the measurement. No Account Code column will show in the Data and Pivot Tables.

6. In the Filter by meter dimensions section, use any string Data Fields on the selected Meters to filter your query by:

  • The string Data Fields available for filtering will depend on the Meters you've select for usage data querying.

Tip: Which Meter string Data Fields are included for filtering? Note that as you start to set up your query and select a Time period, a check is made to determine the Meters which have had numeric data submitted during that period. The Filter by meter dimensions section then automatically adjusts to include any string Data Fields configured for those Meters. This is because the default selection for Meters is All Meters. However, if you then select only some of those Meters to query for submitted usage data, the Filter by meter dimensions section might auto-adjust again to remove any string Data Fields that do not belong specifically to the Meters you've selected.

In this example we have:

  • Set a Custom time period of one month. The Organization Timezone is set to UTC.

  • Selected to filter our query by two Meter numeric Measures: file_submits for number of files submitted and gb_store for gigabytes stored.

  • Chosen not to aggregate any of the numeric measures returned for the query.

  • Filtered for any relevant Account codes to be shown as a column in the returned data.

  • Selected to filter our query by the Customer_name Meter string field to return data for all values for that field.

7. Select Perform Query. The query executes and the resultant data is loaded into the data tables for you to review.

Important: Limits imposed on response payload size and number of rows!

  • Response too Large: If the response payload for your query is >10MB, this exceeds the limit and you'll receive an error message to inform you. You can either use the available filters to further limit the query result set and re-run your query or use the Export button to download the current results in a .csv formatted file - see Exporting Query Results.

  • Number of Rows Returned for Query: If the number of rows returned by your query exceeds 5001 but the payload size limit is not exceeded, then only the first 5001 are loaded. You will not receive an error and if you suspect your query return contained more rows, we recommend you use the Export button to download the results in a .csv formatted file and check.

  • Number of Rows for Export: If you download the query results in a .csv formatted file, the number of rows is limited to 20001.

Reviewing Usage Data Query Results in a Data Table

You can review the usage data returned for your query in a Data table:

You can make adjustments to the Data Table to:

  • Show/Hide data element columns.

  • Reorder the columns.

To Show/Hide columns:

1. Select the columns icon at top-right:

A panel appears showing the columns available for the table and you can use switches to select your Show/Hide preferences:

2. Select Update - the panel closes and the Data Table refreshes for your Show/Hide selections.

  • Note that if you've run a query that doesn't aggregate the usage data it returns, you'll be able to Show/Hide a UID column:

  • This shows the UID used for the original usage data measurements when submitted:

To reorder columns:

1. Select the columns icon at top-right. A panel appears listing the available table columns.

2. Drag-and-drop these into a top-bottom ordering to correspond to the left-right ordering you want for the Data Table:

2. Select Update. The panel closes and the Data Table refreshes for the selected column order:

Tip: Collapse Query Builder. If your query returns a large results set, to make it easier to review the data loaded into the tables, you can collapse the Query Builder by selecting the arrow at top-right next to the Saved Queries button.

Reviewing Usage Data Query Results in a Pivot Table

You can review the usage data returned for your query in a Pivot table:

  • To review returned data using the Pivot Table, you can drag-and-drop data elements to show the preferred arrangement. For example:

Important: Returning Aggregated Data for Pivot Table Review and Analysis! In the example given, we chose not to apply any Aggregations to the data returned for the query. If you want to work with raw usage data returned for the selected Meter Measures, you must de-select any Aggregation functions and select None for Aggregation frequency before you perform the query. In the Pivot table, you can then apply your own aggregations, without risking a distorted view of your usage data by aggregating already-aggregated data.

Tips:

  • Saving Usage Queries? You can save, update, and re-use Usage queries. You can also load pre-configured Standard Queries directly into the Usage query builder. See Saving, Updating, and Re-Using Queries.

  • Exporting Usage Query Results? You can export the results of a Usage query. See Exporting Query Results.

Next: Performing Billing Queries and Reviewing Results


Additional Support

Login to the Support portal for additional help and to send questions to our Support team.