Measures

This section is about Timbr SQL Data Definition Language (DDL) queries to create, query, and edit measures.

What is a Measure?

A Measure in Timbr is calculated value that uses an aggregation function, such as sum, count, minimum, maximum, average or any aggregate function that is supported by the database connected to timbr.

Measure is similar to a property, but it is specifically used to create multi-dimensional cubes in timbr. Measures only support aggregate functions and are exposed in the dtimbr (denormalized) schema.

Create or replace measure

Create and edit measures in the knowledge graph.

Required information for creating or replacing a measure (the curly brackets {} should not be an input, they are used only as a variable substitution):

{measure_name} - The name of the measure you want to create or replace
{measure_type} - The type of the measure you want to create or replace
{description_text} - Optional. The description of the measure
{calculation} - The calculation you want to apply on the measure
{filter} - Optional. apply a filter on a specific measure.

CREATE [OR REPLACE] MEASURE `{measure_name}` {measure_type} 
DESCRIPTION '{description_text}' 
AS SELECT {aggregate_calculation} [ WHERE {filter} ];

Example:

-- Referencing properties or calculated properties:
CREATE MEASURE total_sales decimal AS SELECT sum(`sales`);
CREATE MEASURE total_discount decimal AS SELECT sum(`order_item_discount`);

When referencing other measures no need to specify aggregate function as it is already defined in the measure:

CREATE MEASURE total_revenue decimal AS SELECT total_sales - total_discount;

Create or replace measure with filter

You can create measures with filters to calculate values for specific conditions:

CREATE MEASURE total_revenue_europe decimal 
AS SELECT total_revenue
WHERE market = 'Europe';

Timbr translates the filter to CASE WHEN for the database in runtime:

SELECT SUM(CASE WHEN market = 'Europe' then `sales` else null end) - 
       SUM(case when market = 'Europe' then `sales` else null end) 

Filters can be applied on top of other measures with filters. Timbr will combine the final filter in run-time.

You can also apply a dynamic filter at run-time when querying the measure:

SELECT SUM(measure.total_revenue) FILTER (WHERE market = 'Europe')
FROM dtimbr.`order`

Create or replace temporal measures

You can create measures with time-based filters in timbr:

Basic Aggregation Measures

-- Basic Measures
CREATE OR REPLACE MEASURE Internet_Distinct_Count_Sales_Order int AS 
SELECT COUNT(DISTINCT Sales_Order_Number);

CREATE OR REPLACE MEASURE Internet_Order_Lines_Count int AS 
SELECT COUNT(Sales_Order_Line_Number);

CREATE OR REPLACE MEASURE Internet_Total_Units int AS 
SELECT SUM(Order_Quantity);

CREATE OR REPLACE MEASURE Internet_Total_Discount_Amount decimal AS 
SELECT SUM(Discount_Amount);

CREATE OR REPLACE MEASURE Internet_Total_Product_Cost decimal AS 
SELECT SUM(Total_Product_Cost);

CREATE OR REPLACE MEASURE Internet_Total_Sales decimal AS 
SELECT SUM(Sales_Amount);

CREATE OR REPLACE MEASURE Internet_Total_Margin decimal AS 
SELECT Internet_Total_Sales - Internet_Total_Product_Cost;

Temporal Measures

-- This measure calculates the total sales for the previous quarter of the current year
CREATE OR REPLACE MEASURE Internet_Previous_Quarter_Sales decimal AS 
SELECT Internet_Total_Sales 
WHERE YEAR(Order_Date) = YEAR(CURRENT_DATE) 
  AND QUARTER(Order_Date) = QUARTER(CURRENT_DATE) - 1;

-- This measure calculates the total sales for the current quarter of the current year
CREATE OR REPLACE MEASURE Internet_Current_Quarter_Sales decimal AS 
SELECT Internet_Total_Sales
WHERE YEAR(Order_Date) = YEAR(CURRENT_DATE) 
  AND QUARTER(Order_Date) = QUARTER(CURRENT_DATE);

-- This measure calculates the total margin for the previous quarter of the current year
CREATE OR REPLACE MEASURE Internet_Previous_Quarter_Margin decimal AS 
SELECT Internet_Total_Margin
WHERE YEAR(Order_Date) = YEAR(CURRENT_DATE) 
  AND QUARTER(Order_Date) = QUARTER(CURRENT_DATE) - 1;

-- This measure calculates the total margin for the current quarter of the current year
CREATE OR REPLACE MEASURE Internet_Current_Quarter_Margin decimal AS 
SELECT Internet_Total_Margin
WHERE YEAR(Order_Date) = YEAR(CURRENT_DATE) 
  AND QUARTER(Order_Date) = QUARTER(CURRENT_DATE);

-- This property calculates the number of days that have passed in the current quarter
CREATE OR REPLACE PROPERTY Days_Current_Quarter_to_Date int AS 
SELECT CURRENT_DATE - DATE_TRUNC('quarter', CURRENT_DATE) + 1;

-- This property calculates the total number of days in the current quarter
CREATE OR REPLACE PROPERTY Days_In_Current_Quarter int AS 
SELECT DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months' - DATE_TRUNC('quarter', CURRENT_DATE);

-- This measure calculates the proportion of the previous quarter's margin that would be expected at the current point in the quarter
CREATE OR REPLACE MEASURE Internet_Previous_Quarter_Margin_Proportion_to_QTD decimal AS 
SELECT Internet_Previous_Quarter_Margin * CAST(Days_Current_Quarter_to_Date AS float) / CAST(Days_In_Current_Quarter AS float);

-- This measure calculates the proportion of the previous quarter's sales that would be expected at the current point in the quarter
CREATE OR REPLACE MEASURE Internet_Previous_Quarter_Sales_Proportion_to_QTD decimal AS 
SELECT Internet_Previous_Quarter_Sales * CAST(Days_Current_Quarter_to_Date AS float) / CAST(Days_In_Current_Quarter AS float);

-- This measure compares the current quarter's sales to the expected sales based on the previous quarter's performance
-- A value greater than 1 indicates better performance than the previous quarter, while a value less than 1 indicates worse performance
CREATE OR REPLACE MEASURE Internet_Current_Quarter_Sales_Performance decimal AS 
SELECT Internet_Current_Quarter_Sales / Internet_Previous_Quarter_Sales_Proportion_to_QTD 
WHERE Internet_Previous_Quarter_Sales_Proportion_to_QTD > 0;

-- This measure compares the current quarter's margin to the expected margin based on the previous quarter's performance
-- It calculates the percentage difference between the current margin and the expected margin
-- A positive value indicates better performance than the previous quarter, while a negative value indicates worse performance
CREATE OR REPLACE MEASURE Internet_Current_Quarter_Margin_Performance decimal AS 
SELECT (Internet_Current_Quarter_Margin - Internet_Previous_Quarter_Margin_Proportion_to_QTD) / Internet_Previous_Quarter_Margin_Proportion_to_QTD 
WHERE Internet_Previous_Quarter_Margin_Proportion_to_QTD > 0;

Querying Measures

Measures can be queried using the AGGREGATE function or standard SQL aggregate functions.

Using AGGREGATE function:

SELECT `of_customer[customer].customer_segment` as customer_segment, 
       AGGREGATE(`measure.count_of_order`) as number_of_orders,
       AGGREGATE(`measure.count_of_customer`) as number_of_customers,
       AGGREGATE(`measure.total_revenue`) as total_revenue,
       AGGREGATE(`measure.total_sales`) as total_sales
FROM dtimbr.`order` 
GROUP BY `of_customer[customer].customer_segment`;

Using standard SQL aggregate functions:

SELECT `of_customer[customer].customer_segment` as customer_segment, 
       COUNT(`measure.count_of_order`) as number_of_orders,
       COUNT(`measure.count_of_customer`) as number_of_customers,
       SUM(`measure.total_revenue`) as total_revenue,
       SUM(`measure.total_sales`) as total_sales
FROM dtimbr.`order` 
GROUP BY `of_customer[customer].customer_segment`;

Filtering Measures

You can use the FILTER clause to apply conditions to your measures:

SELECT `of_customer[customer].customer_segment` as customer_segment, 
       AGGREGATE(`measure.count_of_order`) as number_of_orders,
       AGGREGATE(`measure.count_of_customer`) as number_of_customers,
       AGGREGATE(`measure.total_revenue`) FILTER (WHERE market = 'Europe') as total_sales_europe,
       AGGREGATE(`measure.total_revenue`) FILTER (WHERE market = 'US') as total_sales_us
FROM dtimbr.`order` 
GROUP BY `of_customer[customer].customer_segment`;

You can filter a measure that already has a filter in the measure definition. Timbr will combine the filters in run-time:

-- Create a measure with filter
CREATE MEASURE total_revenue_europe decimal 
AS SELECT total_sales - total_discount 
WHERE market = 'Europe';

-- Add a filter ad-hoc in the query:
SELECT `of_customer[customer].customer_segment` as customer_segment, 
       AGGREGATE(`measure.total_revenue_europe`) FILTER (WHERE brand = 'MyBrand') as total_sales_europe_mybrand
FROM dtimbr.`order` 
GROUP BY `of_customer[customer].customer_segment`;

FILTER clause translation

Timbr automatically translates FILTER clauses into CASE/WHEN statements for databases that don't support the FILTER clause directly.

Add / Update a measure description

Add or update a measure description:

ALTER MEASURE `{measure_name}` SET DESCRIPTION = '{description_value}';

Add / Update / Remove measure tag

Adding, updating, or removing a tag of a measure:

Add a tag

ALTER MEASURE `{measure_name}` ADD TAG `{tag_name}` = '{tag_value}';

Update a tag

ALTER MEASURE `{measure_name}` UPDATE TAG `{tag_name}` = '{tag_value}';

Remove a tag

ALTER MEASURE `{measure_name}` DROP TAG `{tag_name}`;

Show SQL create measure statement

SHOW CREATE MEASURE `{measure_name}`;

Rename a measure

ALTER MEASURE `{current_name}` RENAME TO `{new_name}`;

Remove a measure

DROP MEASURE `{measure_name}`;

Removing a measure

Removing a measure from the ontology model will have the measure removed from all instances and concepts it is assigned to.

Measures

What is a Measure?​

Create or replace measure​

Create or replace measure with filter​

Create or replace temporal measures​

Querying Measures​

Using AGGREGATE function:​

Using standard SQL aggregate functions:​

Filtering Measures​

Add / Update a measure description​

Add / Update / Remove measure tag​

Show SQL create measure statement​

Rename a measure​

Remove a measure​