Skip to main content

Cubes

This section is about Timbr SQL Data Definition Language (DDL) queries to create and edit cubes in an ontology.

You can create a cube in the Timbr UI (Ontology Views -> Add new cube) or in directly in SQL.

Introduction to Cubes in Timbr​

Cubes in Timbr enable users to define multidimensional data structures directly in SQL, improving query performance and supporting complex analytical operations. Unlike traditional OLAP cubes, which rely on external modeling languages like DAX or MDX, Timbr Cubes are natively defined in SQL and leverage the semantic relationships and measures within the ontology. This allows for dynamic aggregation and flexible queries without requiring predefined GROUP BY clauses, making analysis more intuitive and adaptable.

A Cube in Timbr allows you to define measures (aggregations like SUM, COUNT, MAX) at the modeling level, without pre-aggregating data. This means that when you query the cube, you can dynamically choose which dimensions to group by at query time, giving you flexibility in analysis.

In contrast, an ontology view requires pre-aggregating data by specific columns using GROUP BY when defining the view. This means that the aggregation is fixed, and you cannot change how data is grouped when querying the view.

With a cube, you define measures inside the cube definition, and aggregation happens dynamically based on the dimensions selected in a query. This makes cubes more flexible and powerful for analytics compared to traditional SQL views.

You can't use GROUP BY when creating a cube as it is computed dynamically at query time based on cube measures and dimensions.

By defining cubes, users can slice, dice, drill-down, roll-up, and perform other multidimensional operations on their data efficiently in SQL or directly from their BI tool.

A Timbr Cube can be created from:

  • Timbr concepts or ontology views – leveraging the semantic layer
  • Datasource tables directly – using the raw data as a base

Create an Ontology Cube​

Create an ontology cube which can be based on the ontology or the underlying datasources.

Required information for create an ontology cube (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {cube_name} - The name of the cube you want to create
  • {datasource_name} - (Optional) The datasource that will be used for the cube (the default is the current active datasource)
  • {sql_for_cube} - The SQL statement for the cube definition
CREATE ONTOLOGY CUBE `{cube_name}` [USING {datasource_name}] AS SELECT {sql_for_cube}

A Timbr Cube can use ontology measures or ad-hoc aggregations:

Creating ontology cube using existing measures:


CREATE OR REPLACE ONTOLOGY CUBE `order_cube` AS 
SELECT 
    `order_region` AS `order_region`, 
    `order_status` AS `order_status`, 
    `order_date` AS `order_date`, 
    `order_country` AS `order_country`, 
    `order_city` AS `order_city`, 
    `market` AS `market`, 
    `in_shipment[shipment].shipping_date` AS `shipping_date`, 
    `of_customer[customer].customer_segment` AS `customer_segment`, 
    `includes_product[product].department` AS `department`, 
    `includes_product[product].category` AS `category`, 
    `includes_product[product].product_name` AS `product_name`, 
    `measure.total_sales` AS `measure.total_sales`, 
    `measure.total_revenue` AS `measure.total_revenue`, 
    `measure.maximum_product_price`, 
    `measure.count_of_order`, 
    `measure.count_of_customer`
FROM `dtimbr`.`order`

Creating a Cube with existing measures

Measures must be prefixed with measure.

The alias is not mandatory for ontology measures:

measure.total_revenue AS measure.total_revenue is the same as measure.total_revenue

Creating ontology cube using ad-hoc measures:


CREATE OR REPLACE ONTOLOGY CUBE `order_cube` AS 
SELECT 
    `order_region` AS `order_region`, 
    `order_status` AS `order_status`, 
    `order_date` AS `order_date`, 
    `order_country` AS `order_country`, 
    `order_city` AS `order_city`, 
    `market` AS `market`, 
    `in_shipment[shipment].shipping_date` AS `shipping_date`, 
    `of_customer[customer].customer_segment` AS `customer_segment`, 
    `includes_product[product].department` AS `department`, 
    `includes_product[product].category` AS `category`, 
    `includes_product[product].product_name` AS `product_name`, 
     SUM(`sales`) AS `measure.total_sales`, 
     SUM(`sales`) - SUM(`order_item_discount`) AS `measure.total_revenue`, 
     MAX(`product_price`) AS `measure.maximum_product_price`, 
     COUNT(distinct `order_id`) AS `measure.count_of_order`, 
     COUNT(distinct `customer_id`) AS `measure.count_of_customer`
FROM `dtimbr`.`order`

You can specify creating a cube with tags by default in the CREATE statement.

  • {tag_name} - The name of the tag to be associated with the cube
  • {tag_value} - The value for the tag associated with the cube
CREATE ONTOLOGY CUBE `{cube_name}` WITH TAGS (`{tag_name}` = `{tag_value}`) AS {sql_for_cube}
Create or replace ontology cube with or without previous tags

Instead of running CREATE ONTOLOGY CUBE ... command, some users prefer running CREATE OR REPLACE ONTOLOGY CUBE
overwriting the previous ontology cube. In those cases, a user may choose to maintain the previously set tags of the ontology cube or remove them.

  • If you run a CREATE OR REPLACE command omitting the WITH TAGS (...) parts, then the previously set tags will be maintained
  • If you wish to remove the tags as well, then you can add to the CREATE OR REPLACE STATEMENT the part of WITH TAGS()
    and then the previously set tags will also be removed.

Show SQL create ontology cube statement​

Required information for showing the create ontology cube statement (The curly brackets {} should not be an input, they are used only as a variable substitution):

  • {cube_name} - The name of the ontology cube you want to show a create statement for.
SHOW CREATE ONTOLOGY CUBE `{cube_name}`;

Change Ontology Cube Description​

Changes the description of an ontology cube.

Required information for changing the description of an ontology cube
(The curly brackets {} should not be an input, they are used only as a variable substitution):

  • {cube_name} - The name of the ontology cube you want to associate the description with.
  • {cube_description} - A string representing the description associated with the ontology cube.
ALTER ONTOLOGY CUBE `{cube_name}` SET DESCRIPTION='{cube_description}';

Create or Update a Tag of an Ontology Cube​

Create or update a tag of an ontology cube.

Required information for creating or updating a tag of an ontology cube
(The curly brackets {} should not be an input, they are used only as a variable substitution):

  • {ontology_cube_name} - The name of the ontology cube you want to add the tag to.
  • {tag_name} - The tag key (name) to be added or updated to the ontology cube.
  • {tag_value} - The tag value to be associated with the tag key of the ontology cube.
ALTER ONTOLOGY CUBE `{ontology_cube_name}` UPDATE TAG `{tag_name}`='{tag_value}';

Remove a Tag from an Ontology Cube​

Remove a tag from an ontology cube.

Required information for removing a tag from an ontology cube
(The curly brackets {} should not be an input, they are used only as a variable substitution):

  • {ontology_cube_name} - The name of the ontology cube you want to remove the tag from.
  • {tag_name} - The tag key (name) to be removed from the ontology cube.
ALTER ONTOLOGY CUBE `{ontology_cube_name}` DROP TAG `{tag_name}`;

Remove an Ontology Cube​

Removes an ontology cube from an ontology.

Required information for removing an ontology cube from the ontology
(The curly brackets {} should not be an input, they are used only as a variable substitution):

  • {cube_name} - The name of the cube you want to remove.
DROP ONTOLOGY CUBE `{cube_name}`;

Query an Ontology Cube​

The Cube schema is exposed under the vtimbr schema. The Cube Schema exposes the dimensions as columns and measures as columns with prefix of measure.

Querying Cube measures behave the same as querying concept measures. The measures can be queried using the AGGREGATE function or standard SQL aggregate functions.

Using AGGREGATE function:​

SELECT `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 vtimbr.`order_cube` 
GROUP BY `customer_segment`;

Using standard SQL aggregate functions:​

SELECT `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 vtimbr.`order_cube` 
GROUP BY `customer_segment`;

Filtering Measures​

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

SELECT `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 vtimbr.`order_cube` 
GROUP BY `customer_segment`;