Reference¶
This section provides technical reference information for XLTable configuration, SQL extensions and runtime variables.
It is intended for administrators, integrators and developers working with cube definitions and system configuration.
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`
,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`)
' 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¶
This section describes the main configuration parameters available
in the settings.json file.
These parameters control server behavior, authentication, database access, caching and system limits.
Parameter reference¶
Parameter |
Description |
Default value |
|---|---|---|
SERVER_DB |
Defines the primary database used by the XLTable server for internal operations. |
— |
CREDENTIAL_DB |
Defines credentials used for accessing the server database. |
— |
CREDENTIAL_DB.query_timeout |
Maximum execution time of a single database query, in seconds. A query running longer is cancelled and an error is returned to Excel. Supported by all connection types. |
60 |
WRITE_LOG |
Enables debug logging of XLTable operations (MDX, generated SQL, Jinja
diffs, result preview). Log files will be located in the folder
|
false |
DUMP_XMLA |
Dumps every raw XMLA request and response to a separate file in the
|
false |
LOG_RETENTION_DAYS |
Files in the |
14 |
SERVER_PORT |
TCP port the server listens on. Applies to the standalone deployment
(Ubuntu / |
5000 |
SERVER_THREADS |
Number of worker threads of one server process (standalone deployment only). Threads waiting on the database do not block each other, so this is how many queries one process keeps in flight towards the warehouse; CPU-intensive result building still runs one report at a time per process — for parallel heavy reports run several worker processes (see Linux). Requires a service restart. |
16 |
USERS |
Defines the list of users for local authentication. |
— |
USER_GROUPS |
Defines user groups used for role-based access control. |
— |
MAX_CELLS |
Limits the size of the pivoted result returned to Excel, measured in
cells: unique row combinations × column combinations × measures.
Queries exceeding the limit are rejected with a message suggesting
filters, the same way SSAS cancels oversized results
( |
100000 |
MAX_FILTER_MEMBERS |
Caps the member list the server enumerates when Excel applies
Keep Only / Hide Selected Items on a field. If the dimension level
has more members than the cap, the resulting filter keeps only the
first |
100000 |
OVERLOAD_GUARD |
Rejects data queries while the server host is out of resources, instead
of forwarding them to the database. When any threshold is exceeded —
|
disabled |
AUTH_CACHE_TIMEOUT |
Defines the lifetime of a cached authorization in seconds, for both
local ( |
3600 |
LDAP_CACHE_TIMEOUT |
Legacy name of |
300 |
METADATA_CACHE_TTL |
Defines the lifetime in seconds of cached cube metadata (cube definitions, database/table/field lists) and of already-built session responses. After this period expires, XLTable re-reads the data from the database, so an edited cube definition is picked up automatically within this window — no manual cache clearing is required. Set to 0 to disable expiry (cache entries then live until the cache is cleared). |
600 |
RESULT_CACHE_MAX_MB |
Query results larger than this size (MB) are not stored in the shared result cache and are rebuilt on every refresh instead. Storing very large results makes all worker processes queue on the cache write, so oversized responses are cheaper to recompute. Set to 0 to disable result caching entirely (metadata is still cached). |
16 |
SQL_CACHE_ENABLED |
Shared SQL result cache: when several users (or several sessions of
one user) produce an identical SQL query, it is executed in the
database once and the result is shared between them. Safe with
row-level security: per-user access filters are rendered into the SQL
text itself, so users with different permissions generate different
SQL and never share results. Excel Refresh gives the pressing
user fresh data and updates the shared entries for everyone (see
Refreshing data). Set to |
true |
SQL_CACHE_TTL |
Lifetime (seconds) of entries in the shared SQL result cache: within
this window an identical query is served from the cache instead of
the database. Set to 0 to disable expiry. When not set, the value of
|
600 |
SQL_CACHE_MAX_MB |
Total size cap (MB) of the shared SQL result cache. When the cap is exceeded, the least recently used results are evicted. |
256 |
SQL_CACHE_MAX_RESULT_MB |
A single query result larger than this size (MB) is not stored in the
shared SQL result cache and is re-read from the database instead.
(Not to be confused with |
32 |
CACHE_BACKEND |
Storage of the shared session/metadata cache and the shared SQL
result cache. |
sqlite |
REDIS_URL |
Redis connection string for |
— |
CONVERT_FIELDS_TO_STRING |
Forces conversion of certain fields to string type before returning results. |
true |
ADMIN_GROUPS |
Defines user groups for accessing the admin panel ( |
— |
API_TOKENS |
Bearer tokens (a string or a list of strings) accepted by the cache management API (see Cache management API) — intended for external systems such as ETL pipelines, so they do not need an admin password. When not set, the API accepts only admin credentials. |
— |
CREDENTIAL_ACTIVE_DIRECTORY |
Defines connection parameters for Active Directory authentication. |
— |
Applying configuration changes¶
Changes to settings.json are picked up automatically — no service
restart is required. XLTable watches the file and re-reads it within a few
seconds of saving (in multi-process deployments such as IIS, every worker
process picks the change up on its next request).
If the saved file contains a JSON syntax error, the service keeps running with the previous configuration and writes the parse error to the log; the file is re-read once it is fixed.
When the configuration content changes, the cache is cleared automatically, so nothing cached under the previous (for example, incorrect) configuration — authorized sessions, cube metadata — stays in effect. Users re-authorize transparently on their next request.
The same comparison runs on service start, so a restart with a changed
settings.jsonalso begins with a clean cache.
The admin panel (see Admin panel) shows which settings file is in use and when it was last loaded.
Deployment-level parameters that live outside settings.json (service
user, port, IIS application pool settings) still require a service restart.