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 PowerMetrics account, you’ll get access for 30 days to PMQL along with other premium features. When the 30-day trial ends, contact our Success Team to upgrade your plan.
- 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
- Setting the time zone
- The PowerMetrics catalog structure
- Meta schema
- Table: metrics
- Metrics schema
- Table naming convention
- Default columns
- Example table structure
- Query language reference guide
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 |
|
|
|
|
|
Klipfolio User ID |
|
Klipfolio API key |
To find your Klipfolio User ID and API Key:
- Click the 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 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):
- In DBeaver, click Database > New Database Connection.
- Select Trino and click Next.
- 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.
- 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.
Once you’re connected, you should see a service tree that looks like this:
- Click SQL Editor > Open SQL script.
You're ready to query your PowerMetrics data. (See below for an 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:
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:
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 |
|
|
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. |
|
|
The metric's name |
|
|
The metric's description |
|
|
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 |
|
|
The actual data value for the product category |
|
|
The time associated with the data value |
|
|
The metric’s product category dimension |
Query language reference guide
This section includes:
- Query structure
- WHERE clause conditions
- Supported operators on dimension filters
- Date math expressions
- Aggregation support
- Notes
- Example queries
Query structure
A query consists of the following parts:
-
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
).
-
- FROM clause: Specifies the data source, in this case, a metric name.
- 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 ascategory
andcountry
, 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 |
|
Current timestamp minus the specified time unit (e.g., |
|
Round to the beginning of the period by adding, for example, (e.g., If the current time is 5 PM and you write the query as |
|
Specific date (e.g., |
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')