Cube definition reference¶
This section provides technical reference information for cube definitions: the SQL tags and a complete unified example.
It is intended for developers working with cube definitions. Server configuration parameters are documented separately — see settings.json reference.
Unified example¶
All tags listed in the table above are used together in a single cube definition example below.
This example demonstrates how SQL tags are embedded into a cube SQL script and how they describe cube structure, measures, dimensions, security rules and visibility settings.
The script represents a complete cube definition and can be used as a reference when creating new OLAP cubes XLTable for ClickHouse.
CREATE OR REPLACE TABLE db.olap_definition
ENGINE = MergeTree() ORDER BY id AS
SELECT 'myOLAPcube' AS id,
'
with calendar as (
SELECT * FROM db.Times where year_str in (''2023'', ''2024'', ''2025'')
)
--olap_cube
--olap_calculated_fields Calculated fields
(sales_sum_qty/stock_avg_qty) as calc_turnover --translation=`Turnover` --format=`#,##0;-#,##0`
--olap_jinja
{{ sql_text | replace("salesly.date_sale", "addYears(salesly.date_sale, 1)") }}
--olap_source Sales
SELECT
--olap_measures
sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity` --format=`#,##0;-#,##0` --description=`Units sold, pcs` --synonyms=`quantity;units;pieces`
,sum(sales.sum) as sales_sum_sum --translation=`Sales Amount` --format=`#,##0.00;-#,##0.00` --hide
FROM db.Sales sales
LEFT JOIN db.Stores stores on sales.store = stores.id
LEFT JOIN db.Models models on sales.model = models.id
LEFT JOIN calendar times on sales.date_sale = times.day_str
LEFT JOIN db.Currencies curr on sales.currency = curr.id --relationship=`part-source`
--olap_drillthrough
stores_name, regions_name, models_name, times_day_str, sales_sum_qty, sales_sum_sum
--olap_source Sales last year
SELECT
--olap_measures
sum(salesly.qty) as salesly_sum_qty --translation=`Sales last year Quantity` --format=`#,##0;-#,##0`
,sum(salesly.sum) as salesly_sum_sum --translation=`Sales last year Amount` --format=`#,##0.00;-#,##0.00` --hide
FROM db.Sales salesly
LEFT JOIN db.Stores stores on salesly.store = stores.id
LEFT JOIN db.Models models on salesly.model = models.id
LEFT JOIN calendar times on salesly.date_sale = times.day_str
--olap_source Stock
SELECT
--olap_measures
avg(stock.qty) as stock_avg_qty --translation=`Average Stock Quantity`
FROM db.Stock stock
LEFT JOIN db.Stores stores on stock.store = stores.id
LEFT JOIN db.Models models on stock.model = models.id
--olap_source Stores
SELECT
--olap_dimensions
stores.id as store_id --translation=`Store ID`
,stores.name as stores_name --translation=`Store` --folder=`Distribution`
FROM db.Stores stores
LEFT JOIN db.Regions regions on stores.region = regions.id
--olap_source Regions
SELECT
--olap_dimensions
regions.name as regions_name --translation=`Region`
FROM db.Regions regions
LEFT JOIN db.Managers managers on regions.id = managers.region --relationship=`many-to-many`
--olap_source Managers
SELECT
--olap_dimensions
managers.name as managers_name --translation=`Manager`
FROM db.Managers managers
--olap_source Models
SELECT
--olap_dimensions
models.name as models_name --translation=`Model`
FROM db.Models models
--olap_source Dates
SELECT
--olap_dimensions
times.year_str as times_year_str --hierarchy=`Dates` --translation=`Year`
,toQuarter(toDate(times.day_str)) as times_quarter_str --hierarchy=`Dates` --translation=`Quarter`
,times.month_str as times_month_str --hierarchy=`Dates` --translation=`Month`
,times.day_str as times_day_str --hierarchy=`Dates` --translation=`Day`
FROM calendar times
--olap_user_role
--olap_user_groups
olap_users
--olap_calculated_fields_visible
all
--olap_measures_visible
sales_sum_qty, stock_avg_qty
--olap_dimensions_visible
all
--olap_access_filters
regions_name in (`North`, `South`)
--olap_description
Sales fact cube, one row per sale line.
Answers questions about quantity and stock by store, region, manager,
model and date.
--olap_ai_instructions
Compare years only over completed months.
' AS definition
Jinja context variables¶
The Jinja context object handed to cube templates — its cube / request
/ sql namespaces plus user and now — is documented in the
Jinja chapter. See The context object.
settings.json schema¶
Server configuration parameters — server behavior, authentication, caching,
system limits and the EXPORT section — are documented on the
settings.json reference page.