OLAP cube definition¶
Concept¶
An OLAP cube in XLTable consists of measure groups and dimensions.
Measure groups contain measures (for example, sum, count, average)
Dimensions contain attributes (for example, regions, stores, time)
To make data available in Excel Pivot Tables, you must define the OLAP cube structure:
which measures will be included
which dimensions will be included
which attributes each dimension contains
which tables store data for measures and dimensions
If you have experience designing OLAP cubes in Microsoft SQL Server Analysis Services, the overall logic will feel familiar.
In Analysis Services, cube structure is designed in a graphical environment and then deployed. In XLTable, cube structure is defined using SQL scripts.
Anatomy of a cube definition¶
Before diving into individual tags, keep two ideas in mind.
Every block is a runnable SELECT. A cube definition is a sequence of ordinary
SQL SELECT statements annotated with tags inside comments. Any single block can
be copied into a database client and executed as-is — it returns real rows. This is
the key mental model: you are writing normal SQL first, and the tags only tell
XLTable how to assemble those queries into an OLAP cube.
A definition follows a fixed top-to-bottom order. The blocks always appear in this sequence:
WITH <cte> AS (...) ← 1. CTEs (optional, shared by the whole cube)
--olap_cube ← 2. cube-level block:
--olap_calculated_fields ... calculated fields and
--olap_jinja ... cube-level Jinja (optional)
--olap_source <MeasureGroup> ← 3. measure groups (one or more)
...
--olap_source <Dimension> ← 4. dimensions (one or more)
...
--olap_user_role ← 5. user roles / access rules (optional)
...
Blocks are separated by a blank line. Refer back to this map as you read the sections below.
Cube definition storage¶
XLTable stores cube definitions in a database table.
Each cube definition is a sequence of SQL scripts describing:
measure groups
dimensions
relationships
calculated fields
access rules
Jinja logic
These scripts are written sequentially and stored in the analytical database in a table named olap_definition.
Table olap_definition structure:
ID — cube identifier
Definition — SQL script defining cube structure
When a user connects from Excel:
XLTable reads cube definitions from this table
Displays available cubes
After selection, XLTable builds the list of measures and dimensions
Excel displays them in Pivot Table fields
Unified example¶
Follow this link for an example of creating an OLAP cube for a ClickHouse database: Unified example .
Cube definition rules¶
Cube structure is defined using SQL tags embedded in SQL scripts.
Examples:
olap_source
olap_measures
olap_dimensions
See the full list of tags: SQL tags
How to write tag values¶
A tag is written inside a SQL comment and starts with --. There are two ways a
tag carries a value, depending on the tag.
Block tags — value is the rest of the line. For tags such as olap_source,
the value is everything that follows the tag on the same line, taken literally up to
the end of the line. Spaces are part of the value, so no quoting is needed:
--olap_source Sales last year
Here the source name is Sales last year — all three words.
Inline tags — value after = in backticks. Field-level tags such as
translation, format, hierarchy and folder attach to a single field
and take their value after an = sign, wrapped in backtick characters:
stores.name as stores_name --translation=`Store` --folder=`Distribution`
The backticks mark where the value begins and ends, which is what lets a value
contain spaces (Sales Quantity) or punctuation (#,##0;-#,##0). Backticks are
used deliberately instead of single or double quotes so the value never clashes with
'...' and "..." string literals that may appear in the field expression
itself. Multiple inline tags can be placed on the same line, each separated by a space.
Measure group design¶
The first step is defining the data source for a measure group.
Example:
--olap_source Sales
SELECT
--olap_measures
sum(sales.qty) as sales_sum_qty
FROM db.Sales sales
The SELECT keyword is mandatory, not a stylistic choice: each
olap_source block must be a complete, runnable SELECT statement. This lets you
copy any block into a database client and execute it as-is to verify it returns the
expected rows before XLTable ever uses it.
The order of blocks within an olap_source section is mandatory:
--olap_source <Name> ← 1. source name
SELECT ← 2. SELECT keyword (on its own line)
--olap_measures ← 3. section type (or --olap_dimensions)
<field list> ← 4. fields with aliases and tags
FROM <table> <alias> ← 5. main table
LEFT JOIN ... ← 6. joins (optional)
Important rules:
table aliases must be unique across the cube
the same table may be reused with a different alias
Measure definition¶
A measure consists of:
source column
aggregation function
resulting column alias
Example:
sum(sales.qty) as sales_sum_qty
Naming recommendation:
<table_alias>_<aggregation>_<column>
Example:
sales_sum_qty
Dimension design¶
We suggest reviewing the definition of measure groups first, as they are very similar to dimensions.
Dimensions define the analytical context for measures.
Typical examples:
stores
regions
products
time
Example:
--olap_source Stores
SELECT
--olap_dimensions
stores.id as store_id
,stores.store_name as store_name
FROM db.Stores stores
Hierarchies¶
Hierarchy defines parent-child relationships between dimension attributes.
Example:
times.year as times_year --hierarchy=`Dates`
times.quarter as times_quarter --hierarchy=`Dates`
times.month as times_month --hierarchy=`Dates`
times.day as times_day --hierarchy=`Dates`
Relationships¶
Relationships connect measures and dimensions.
Example:
FROM db.Sales sales
LEFT JOIN db.Stores stores ON sales.store_id = stores.id
Rules:
always use LEFT JOIN
joins must be explicit
A dimension is linked to a measure group through a shared table alias: the
alias used in the measure group’s LEFT JOIN (for example LEFT JOIN db.Stores stores)
must match the alias of the dimension’s own source (--olap_source Stores ... FROM db.Stores stores).
XLTable connects the two on that identical alias, so keep aliases consistent across the cube.
Measure groups support both direct and indirect dimension relationships. Each link must be defined on a new line. Indirect connections occur when a dimension links to a measure group via an intermediary dimension.
Special relationship types¶
many-to-many:
LEFT JOIN db.Managers managers ON sales.store_id = managers.store_id --relationship=`many-to-many`
Many-to-many relationships follow the classic Analysis Services model, where dimensions lack a unique key. Instead, a single measure group value maps to multiple dimension rows. For example, multiple managers can be assigned to the same store, causing overlapping results when filtering.
one-table:
--olap_source Sales
SELECT ...
FROM db.sales sales
LEFT JOIN db.sales sales --relationship=`one-table`
For denormalized sources like ClickHouse, use the relationship=`one-table` tag to link measures and dimensions within a single table. This bypasses the unique alias rule and the LEFT JOIN operation. The OLAP server will query the flat table directly; no ON clause or join columns are required.
part-source:
--olap_source Sales
SELECT ...
FROM db.Sales sales
LEFT JOIN db.Currencies curr on sales.currency = curr.id --relationship=`part-source`
By default, a LEFT JOIN whose alias matches another olap_source is treated as
a relationship to that other source (see Relationships). Use
relationship=`part-source` when the joined table is not a separate cube source
but simply an extra table that belongs to the current source — a lookup table or a
helper join needed to compute its measures or attributes (for example attaching a
Currencies reference to convert amounts).
The join is then treated as part of the current olap_source block only: it does
not register the table as a cube-wide source and does not create a new join
path that other measure groups or dimensions could connect through. Use it whenever
you need an auxiliary table inside one source without exposing it to the rest of the cube.
Calculated fields¶
Calculated fields are virtual measures computed from other measures.
They are declared once for the whole cube, in a block that starts with the
olap_cube tag followed by an olap_calculated_fields tag (whose value is
the folder name shown in the Excel field list).
Example:
--olap_cube
--olap_calculated_fields Calculated fields
(sales_sum_qty/stock_avg_qty) as turnover --translation=`Turnover`
A calculated field may combine measures from different measure groups: the per-group results are merged with a FULL JOIN before the expression is applied, so any measure alias defined in the cube can be referenced here.
Note
Because the inputs come from different measure groups, a measure may be
NULL (no matching rows) or zero for a given cell. Always guard division
against NULL and zero, for example
(sales_sum_qty / nullIf(stock_avg_qty, 0)) as turnover.
Drillthrough¶
Drillthrough returns the raw detail rows behind an aggregated cell. When a user
double-clicks a value in an Excel Pivot Table, Excel sends a DRILLTHROUGH
request and XLTable answers with a flat table of detail rows — the individual fact
records that were summed into that cell.
You declare which columns those detail rows contain per measure group, with the
olap_drillthrough tag placed inside an olap_source block, after its
LEFT JOIN clauses and before --olap_jinja if the source has one
(everything after --olap_jinja belongs to the Jinja template — a drillthrough
tag placed there would be rendered into the SQL of every query and break it):
--olap_source Sales
SELECT
--olap_measures
sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity`
,sum(sales.sum) as sales_sum_sum --translation=`Sales Amount`
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
--olap_drillthrough
stores_name, models_name, times_day_str, sales_sum_qty, sales_sum_sum
The tag value is a comma-separated list of fields already defined in the cube.
Each entry may be the field’s SQL alias (stores_name) or its translation
display name (Store) — both resolve to the same field. Order is preserved: the
columns appear in the detail table in the order listed.
How it works:
Per measure group. Each measure group has its own list, because each has its own granularity. Drilling a
Salescell returns Sales detail; drilling anAverage Stock Quantitycell returns Stock detail.The clicked measure picks the group. The measure in the drilled cell selects which
olap_source(fact table) the detail rows come from.Measures are returned raw. A measure listed here is emitted as its underlying column with the aggregation removed (
sum(sales.qty)→sales.qty), so each row shows the individual fact value, not a total.count(...)becomes the literal1(one per row). This works only for measures that are a single aggregate over a plain expression: a compound expression combining several aggregates (sum(sales.sum) / sum(sales.qty)) or a conditional/uniq aggregate (sumIf,uniqExact, …) has no meaningful per-row value, so its column is skipped in the detail table (the syntax checker warns about such fields in theolap_drillthroughlist).Joins are resolved automatically. Columns from related dimension tables pull in the necessary
LEFT JOINchain, including multi-hop paths (Sales→Stores→Regions). Relationships taggedrelationship=`one-table`produce no join, as in regular queries — the dimension columns are read directly from the fact table. The measure group’srelationship=`part-source`helper joins are always included, so measures computed through a lookup table (for example a currency conversion) drill through correctly.Filters apply. The cell’s row, column and slicer context becomes the
WHEREclause, using the same row-level security and filtering as normal queries.CTEs and Jinja apply exactly as for regular queries (measure-group Jinja, then the cube CTE, then cube-level Jinja).
If a measure group has no olap_drillthrough tag, drilling a cell of that group
falls back to returning just the clicked measure as a single column.
Note
Calculated fields cannot be drilled through — they are computed from measures
that may span several measure groups and have no single set of underlying rows.
Drilling a calculated field returns a clear message instead of data, matching
Analysis Services behavior. A calculated field listed inside
olap_drillthrough is skipped the same way as a compound measure.
For the end-user experience in Excel, see Drill through to detail rows.
CTE¶
CTE scripts define temporary datasets used in cube SQL.
A CTE is declared once, at the very top of the cube definition (before the
olap_cube block). It is shared across the whole cube: every measure group and
dimension source can reference it, just like a real table.
Example:
WITH calendar AS (
SELECT ...
)
CTEs can serve as data sources for both measure groups and dimensions — reference
the CTE name in a FROM or LEFT JOIN clause and give it an alias as usual
(for example LEFT JOIN calendar times).
User roles¶
User roles control access to cube data.
Example:
--olap_user_role
--olap_user_groups
finance_users
--olap_calculated_fields_visible
all
--olap_measures_visible
sales_sum
--olap_dimensions_visible
region, store
--olap_access_filters
region in (`EU`, `NA`)
The olap_user_role tag marks the beginning of a role definition; multiple roles can be defined.
Under olap_user_groups, list the user groups that belong to this role.
Under the ..._visible tags, list the measure groups, dimensions, individual measures, or dimension attributes visible to this role.
Under olap_access_filters, define the row-level filters applied to this role.
Access filters are a security boundary: the server adds them to the WHERE
clause of every SQL query it builds for the cube — regular pivot queries,
filter member lists, Keep Only / Hide probes and drillthrough. When a query
also filters the same field explicitly, the two conditions are intersected,
so no MDX query (including a hand-crafted one) can return rows outside the
role’s allowed set. If a user belongs to several roles, their filters are
combined (the union of the allowed values).
Do not confuse the two visibility mechanisms: the --hide tag hides a field
globally, for everyone (typically a helper measure used only inside calculated
fields), whereas the ..._visible tags control visibility per role — each role
sees only the measures, dimensions and attributes listed for it.
SQL generation logic¶
In short, XLTable works as follows: when a user selects fields in an Excel PivotTable, Excel sends an MDX query to the OLAP server. The server parses the MDX and, based on the cube’s definition, generates several SQL queries to the database. To build efficient OLAP cubes, it is essential to understand how these SQL queries are constructed.
When a user selects fields in Excel:
Excel sends an MDX query
XLTable interprets selected measures and dimensions
SQL is generated only for selected elements
Queries are executed in the database
Results are returned to Excel Pivot Table
If multiple measure groups exist:
SQL is generated per group
results are merged using FULL JOIN
shared dimension attributes are used as join keys
Put simply, SQL generation follows a basic principle: the queries executed are exactly what is defined in the cube metadata. Enable logging in settings.json → WRITE_LOG to inspect generated SQL.
Validation and debugging¶
Two facilities help you confirm a cube definition is correct and inspect what XLTable actually runs against the database.
Check the definition before connecting — add the definition_check_on tag to
the cube definition. When present, XLTable performs a mandatory syntax validation of
the whole definition before connecting to data; if validation fails, the connection
is refused and an error is returned, so a broken definition never reaches users.
Inspect the generated SQL — set WRITE_LOG to true in settings.json.
XLTable then writes every generated SQL query to the log folder
(...\xltable\log), letting you see the exact statements produced for the user’s
field selection. This is the fastest way to debug unexpected results or performance
issues. Changes to settings.json are picked up automatically within a few
seconds — no restart is needed.
A practical workflow is: run each olap_source block on its own in a database
client (every block is a runnable SELECT), then enable definition_check_on and
WRITE_LOG to validate the full definition and review the final SQL.
Jinja scripts¶
Jinja templates let you modify the generated SQL dynamically — for performance
optimization, conditional SQL logic and advanced metrics — using the --olap_jinja
tag inside the cube. Scripts receive the current SQL text and a rich context
describing the request.
Jinja has its own chapter: see Jinja for scripts, the context object
reference and template debugging.
Some examples¶
This section contains examples of the most common cube configuration scenarios.
Cube from a single denormalized table¶
When all data resides in one flat table, use relationship=`one-table` to link measures and dimensions without a real join:
--olap_source Sales
SELECT
--olap_measures
sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity` --format=`#,##0;-#,##0`
,sum(sales.sum) as sales_sum_sum --translation=`Sales Amount` --format=`#,##0.00;-#,##0.00`
FROM db.Sales sales
LEFT JOIN db.Sales sales --relationship=`one-table`
--olap_source Stores
SELECT
--olap_dimensions
sales.store as sales_store --translation=`Store`
FROM db.Sales sales
Measures and dimensions from separate tables with one-table relationship¶
Use this pattern when measures and dimensions live in different tables but the dimension data
is fully repeated in each fact row — no separate key join is needed.
The relationship=`one-table` tag tells XLTable to query each table independently
and match dimension values directly from the fact rows, bypassing a traditional JOIN.
This is the recommended approach when filter value lookups (the lists of values shown in Excel slicers) should run against a small, fast dimension table, while the main aggregation query runs against a large denormalized fact table. Keeping the two queries separate avoids scanning the entire fact table just to populate a filter dropdown.
Note
The dimension field used for matching (store in this example) must exist in both tables —
in the dimension table for the filter lookup, and in the fact table for applying the filter to the main query.
--olap_source Sales
SELECT
--olap_measures
sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity` --format=`#,##0;-#,##0`
,sum(sales.sum) as sales_sum_sum --translation=`Sales Amount` --format=`#,##0.00;-#,##0.00`
FROM db.Sales sales
LEFT JOIN db.Stores stores --relationship=`one-table`
--olap_source Stores
SELECT
--olap_dimensions
store as sales_store --translation=`Store`
FROM db.Stores stores
Best practices for cube design¶
Naming conventions:
measures → <table_alias>_<aggregation>_<column>
dimensions → <table_alias>_<column>
Aliases must be unique.
Table alias rules:
every source must have a unique alias
aliases must remain stable
Dimension strategy:
use descriptive attributes
avoid high-cardinality fields
Hierarchy design:
build logical parent-child structures
maintain natural ordering
Join strategy:
always use LEFT JOIN
define joins explicitly
Measure design:
keep aggregations simple
avoid nested SQL
Calculated fields:
use only when required
keep readable and testable
Performance:
minimize joins
pre-aggregate data in database
reduce cube complexity
Cardinality:
avoid using IDs as primary dimensions
prefer grouped attributes
Jinja:
use for small SQL adjustments
avoid complex logic
Security:
define roles early
restrict sensitive measures
Maintainability:
separate blocks clearly
version control cube definitions
Design philosophy:
SQL first.
Everything in XLTable cubes is defined using SQL:
structure
logic
metadata
security