Skip to main content

Querying metric data using PowerMetrics Query Language (PMQL)

Updated

With PowerMetrics Query Language (PMQL) you can extend the value of metrics to external applications, like Enterprise BI, data apps, data orchestration tools, notebooks, and analysis tools, without the requirement for a deep integration or a plug-in.

PMQL exposes the metrics in PowerMetrics as tables, providing easy access to relevant data through an SQL-like interface.

Built on top of Trino, PMQL gives access to two schemas from the PowerMetrics metric catalog, the meta schema and the metrics schema.

Note:

  • When you sign up for a free PowerMetrics trial, you get access for 30 days to PMQL. To continue using this feature when your trial period ends, purchase a plan that includes “PowerMetrics Query Language” or one that allows you to buy the “PowerMetrics Query Language add-on”.
  • PMQL’s rate limit is 25 requests/minute/user AND 1400 queries/hour per company. These limits apply to individual queries in PowerMetrics. A complex SQL query may count as multiple requests. A simple query should count as one request. Note that rate limits may change in future versions.

This article includes the following sections:

Connecting to the PMQL interface

This article describes how to access PMQL via an SQL client application that supports the Trino interface. The following example of connecting to PMQL uses DBeaver, but you can use any SQL client app that supports Trino.

You’ll need to enter the following information when connecting to PMQL.

Parameter Value
Hostname pmql.klipfolio.com
Port 443
Username Klipfolio User ID
Password Klipfolio API key

To find your Klipfolio User ID and API Key:

  • Click the account and settings button located in the left navigation sidebar and select My Profile. Your User ID and API Key display on the General Information page.

To create a new Klipfolio API key:

  • Click the account and settings button located in the left navigation sidebar and select My Profile. Click the Edit button beside General Information. At API Key, click Generate New API Key > Generate.

To connect to PMQL (DBeaver example):

  1. In DBeaver, click Database > New Database Connection.
    create new database connection
  2. Select Trino and click Next.
    select trino
  3. Enter the Host (pmql.klipfolio.com), Port (443), Username (your Klipfolio User Id), and Password (your Klipfolio API key) (as described above). Then, click Test Connection… to verify the information you entered is correct.
  4. Click Finish.
    Note: The driver is usually automatically installed, however, depending on the SQL client you chose, you may be guided through the installation process.
    enter user settings
    Once you’re connected, you should see a service tree that looks like this:
    service tree format
  5. Click SQL Editor > Open SQL script.
    open SQL script

You're ready to query your PowerMetrics data. (See below for an example query.)
example query

Setting the time zone

To return data for the desired time zone:

  • Enter set TIME zone followed by the ‘desired time zone’; into the query, as shown below:
    syntax for setting the time zone

OR

  • Edit the connection settings (DBeaver example) by right-clicking the database (pmql.klipfolio.com) and selecting Edit Connection > Driver properties > timezone, and entering the desired time zone, as shown below:
    editing the connection to set the time zone

The PowerMetrics catalog structure

The PowerMetrics catalog comprises 2 schemas - the Meta schema and the Metrics schema.

Meta schema

The meta schema provides metadata information for each metric in the PowerMetrics system.

Table: metrics

Column Name Data Type Description
Id varchar

The metric's publicId

Note: This Id can be used in the SQL as the table name. If you have more than one metric with the same name, we recommend using the Id in the SQL query instead of the metric name.
name varchar The metric's name
description varchar The metric's description
owner varchar The metric owner's Id

Metrics schema

The metrics schema exposes each metric as a separate table, providing access to its actual data.

Table naming convention

Each metric is exposed as a separate table in the metrics schema. The table name corresponds to the name or the Id column value in the meta.metrics table. Note: Metrics are exposed by name or id. However, only unique names are supported. We choose the first instance (non-deterministic) when multiple metrics with the same name are found. In the case of duplicate names, we recommend using the metric Id instead.

Default columns

Each metric table has two default columns:

  • __value: A double precision value representing the metric’s actual data at a given time.
  • __time: A date-time value representing the time associated with the metric’s data.

Example table structure

As an example, let’s say you have a metric named "Average Order Value" with the dimension "Product Category". The corresponding table in the metrics schema would have the following structure:

Column Name Data Type Description
__value double The actual data value for the product category
__time datetime The time associated with the data value
product_category varchar The metric’s product category dimension

Query language reference guide

This section includes:

Query structure

A query consists of the following parts:

  1. SELECT clause: Specifies the columns to retrieve.
    • __value: A double precision value.
    • __time: A timestamp representing the time dimension.
    • Optional: Additional column names or dimensions can be specified (e.g. dimension1, category, country).
  2. FROM clause: Specifies the data source, in this case, a metric name.
  3. WHERE clause: Filters the data based on conditions.

WHERE clause conditions

The WHERE clause supports the following conditions:

  • __periodicity: Must be one of:
    • 'YEAR'
    • 'QUARTER'
    • 'MONTH'
    • 'WEEK'
    • 'DAY'

Note: This is a string value that only supports equality checks (=).

  • __startTime and __endTime: Defines a time range for the __time dimension using date math expressions.
    Syntax: '2024-07-01', or date math expressions like 'now-1y' (last year), 'now-6m' (six months ago), etc.
    Note: These are string values that only support equality checks (=).
  • Optional:
    [dimensionName [=|<>] 'value' | dimensionName [not] in ('values')]
    Supports multiple dimensions, such as category and country, using the same syntax.

Supported operators on dimension filters

The following operators are supported:

  • = (equal to)
  • <> (not equal to)
  • IN (member of a set)
  • NOT IN (not a member of a set)
  • NULL (checks for null values)
  • NOT NULL (checks for non-null values)

All other operators are not supported.

Date math expressions

Date math expressions can be used to calculate time ranges. The following expressions are supported:

Expression Description
now-<time_unit>

Current timestamp minus the specified time unit

(e.g., now-7d/d is last 7 days)
/<time_unit>

Round to the beginning of the period by adding, for example, /d, /M, or /y

(e.g., If the current time is 5 PM and you write the query as now-7d, you will retrieve data from 7 days ago at 5 PM. If you write the query as now-7d/d, you will retrieve data from 7 days ago at midnight.)
<year>-<month>[-<day>]

Specific date

(e.g., 2024-07-01)

The following time units are supported:

  • d - days
  • w - weeks
  • M - months
  • q - quarters
  • y - years

Aggregation support

No aggregations are supported. This means that you cannot use functions such as SUM, AVG, MAX, MIN, or COUNT in your queries.

Notes

  • Syntax is case-insensitive.
  • This interface may change in future versions without notice.

Example queries

These example queries retrieve the __value and __time columns for a specific metric, filtered by various conditions on multiple dimensions (e.g. dimension1, category, and country).

 

select __value, __time, dimension1 from "metric_name"

where

__periodicity = 'YEAR'

and __startTime = 'now-1y'

and __endTime = 'now'

and dimension1 = 'value'

 

select __value, __time, category, country from "metric_name"

where

__periodicity = 'MONTH'

and __startTime = 'now-6m'

and __endTime = 'now'

and category IN ('cat1', 'cat2')

Related articles