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. |
— |
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
( |
1000000 |
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 of cached cube metadata and query results in seconds: cube definitions, database/table/field lists and MDX query results. 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 |
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 ( |
— |
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.