DuckDB Sample Data

This page describes a ready-to-run SQL script that creates a complete set of sample tables inside a DuckDB database file, fills them with test data, and registers the myOLAPcube OLAP cube from the Unified example.

Use this script to explore XLTable features without setting up your own data. DuckDB is an embedded database — there is no separate database server to install: the whole database lives in a single file next to XLTable.

The script file: duckdb_sample.sql

---

What the script creates

The script creates a schema named db inside your DuckDB database file. Replace every occurrence of db. with <your_schema>. before running if your setup differs.

Table	Rows	Description
db.times	1096	Calendar: every day from 2023-01-01 to 2025-12-31
db.regions	4	Sales regions: North, South, East, West
db.managers	5	Sales managers linked to regions (many-to-many)
db.stores	8	Retail stores, each assigned to a region
db.models	8	Product models (Alpha … Theta)
db.sales	3 000	Sales transactions: store, model, date, quantity, amount
db.stock	500	Inventory snapshots: store, model, quantity on hand
db.olap_definition	1	OLAP cube definition read by XLTable

The cube myOLAPcube exposes:

• Measures: Sales Quantity, Sales Amount, Sales last year (Qty & Amount), Average Stock Quantity, calculated Turnover ratio

• Dimensions: Store ID, Store, Region, Manager, Model, Date hierarchy (Year → Quarter → Month → Day)

---

Data model

             ┌─────────────┐
             │  db.times   │
             │  (calendar) │
             └──────┬──────┘
                    │ day_str
       ┌────────────┴────────────┐
       │                         │
┌──────┴──────┐           ┌──────┴──────┐
│  db.sales   │           │  db.stock   │
└──────┬──────┘           └──────┬──────┘
       │ store / model           │ store / model
┌──────┴──────┐           ┌──────┴──────┐
│  db.stores  ├───────────┤  db.models  │
└──────┬──────┘           └─────────────┘
       │ region
┌──────┴──────┐
│ db.regions  │
└──────┬──────┘
       │ id  (many-to-many)
┌──────┴──────┐
│db.managers  │
└─────────────┘

---

Prerequisites

• DuckDB CLI (installation guide) or Python with the duckdb package (pip install duckdb)

• XLTable server already installed and running (see Installation)

No database server, user accounts or network configuration are needed — the script creates a regular file on disk.

---

Step 1: Run the SQL script

Download duckdb_sample.sql and run it to create the database file. Place the file where the XLTable service can read it, e.g. /usr/olap/xltable/data/ on Linux.

Option A — DuckDB CLI

mkdir -p /usr/olap/xltable/data
duckdb /usr/olap/xltable/data/sample.duckdb -f duckdb_sample.sql

Option B — Python

python -c "import duckdb; duckdb.connect('/usr/olap/xltable/data/sample.duckdb').execute(open('duckdb_sample.sql').read())"

Verify that all tables were created:

duckdb -readonly /usr/olap/xltable/data/sample.duckdb \
  "SELECT table_name, estimated_size AS rows FROM duckdb_tables() WHERE schema_name = 'db' ORDER BY table_name"

Expected output:

┌─────────────────┬───────┐
│   table_name    │ rows  │
├─────────────────┼───────┤
│ managers        │     5 │
│ models          │     8 │
│ olap_definition │     1 │
│ regions         │     4 │
│ sales           │  3000 │
│ stock           │   500 │
│ stores          │     8 │
│ times           │  1096 │
└─────────────────┴───────┘

---

Step 2: Configure XLTable

Open /usr/olap/xltable/setting/settings.json and update the database connection block:

{
    "SERVER_DB": "DuckDB",
    "CREDENTIAL_DB": {
        "database": "/usr/olap/xltable/data/sample.duckdb",
        "read_only": true
    },
    "WRITE_LOG": false,
    "DUMP_XMLA": false,
    "LOG_RETENTION_DAYS": 14,
    "MAX_CELLS": 1000000,
    "OVERLOAD_GUARD": {
        "MAX_MEMORY_PERCENT": 90,
        "MAX_CPU_PERCENT": 95,
        "MIN_FREE_DISK_MB": 512
    },
    "CONVERT_FIELDS_TO_STRING": true,
    "USERS": {"user1": "pass1", "user2": "pass2"},
    "USER_GROUPS": {"user1": ["olap_users", "olap_admins"], "user2": ["olap_users"]},
    "ADMIN_GROUPS": ["olap_admins"]
}

Use an absolute path in database so it does not depend on the service working directory. Keep read_only: true (the default): XLTable only reads the data, and read-only mode lets several XLTable worker processes open the same database file simultaneously.

Make sure the file is readable by the account the XLTable service runs under (user= in olap.conf, usually olap):

sudo chown olap /usr/olap/xltable/data/sample.duckdb

XLTable automatically discovers all cubes stored in the olap_definition table, so no additional cube configuration is needed.

---

Step 3: Apply the settings

XLTable re-reads settings.json automatically within a few seconds of saving — no restart is needed. If the service is not running yet, start it:

sudo supervisorctl start olap

---

Step 4: Connect Excel

1. Open Excel and go to Data → Get Data → From Database → From Analysis Services.

2. Enter the server URL: http://your_server_ip

3. Log in with user1 / pass1.

4. Select myOLAPcube.

5. Drag any measures and dimensions onto the Pivot Table — done.

Available fields in the Pivot Table:

Field name (Excel)	Type	Notes
Sales Quantity	Measure	sum(sales.qty)
Sales Amount	Measure	sum(sales.amount)
Sales last year Quantity	Measure	Same query, dates shifted +1 year via Jinja
Sales last year Amount	Measure	Same query, dates shifted +1 year via Jinja
Average Stock Quantity	Measure	avg(stock.qty)
Turnover	Calculated	Sales Quantity ÷ Average Stock Quantity
Store ID / Store	Dimension
Region	Dimension	North · South · East · West
Manager	Dimension	Many-to-many with Region
Model	Dimension	Alpha … Theta
Year / Quarter / Month / Day	Dimension	Dates hierarchy, drill-down supported

---

Using your own data

DuckDB can query Parquet and CSV files directly, so a cube can be built on top of plain files without importing them into tables — reference them in the cube definition the same way as tables:

FROM read_parquet('/usr/olap/xltable/data/sales/*.parquet') sales

Only the olap_definition table itself has to exist inside the .duckdb file.

---

Customising the script

Change the date range

The calendar is generated for 2023–2025 using generate_series. To extend it to 2026, change the end date and update the cube filter:

-- In db.times INSERT — extend generate_series to 2026-12-31
FROM generate_series(DATE '2023-01-01', DATE '2026-12-31', INTERVAL 1 DAY) AS t(d);

Then update the cube definition inside db.olap_definition:

WHERE year_str IN ('2023', '2024', '2025', '2026')

Add more stores or models

Extend the INSERT INTO db.stores / db.models sections and update the CASE expressions in the db.sales and db.stock inserts accordingly.

Use a different schema

Replace every occurrence of db. with your own prefix, e.g. myschema..

---

Troubleshooting

IO Error: Could not set lock on file

Another process holds a read-write connection to the database file (for example, an open DuckDB CLI session without -readonly). Close it, and keep read_only: true in settings.json — read-only connections do not conflict with each other.

IO Error: Cannot open file / Permission denied

The XLTable service account cannot read the file. Check the path in settings.json (use an absolute path) and the file permissions (chown olap on Linux).

No cubes visible in Excel

Verify the definition row exists:

duckdb -readonly /usr/olap/xltable/data/sample.duckdb "SELECT id FROM db.olap_definition"

Also confirm that USER_GROUPS in settings.json contains "olap_users" for the connecting user.

Catalog Error: Table with name ... does not exist

The script did not run against the same file XLTable opens — compare the path used in Step 1 with database in settings.json.

---

Full script

-- =============================================================================
-- XLTable OLAP – DuckDB sample data script
-- =============================================================================
-- Creates the `db` schema inside your DuckDB database file, all required
-- dimension and fact tables, fills them with ~3 500 rows of deterministic
-- test data, and registers the `myOLAPcube` OLAP cube definition
-- (see reference.html#unified-example).
--
-- IMPORTANT: Replace `db.` throughout this script with your own schema name
--            if needed.  Quick search-and-replace:  db.  →  <your_schema>.
--
-- Prerequisites:
--   - DuckDB CLI installed (https://duckdb.org/docs/installation)
--     or the Python package: pip install duckdb
--
-- Usage (DuckDB CLI — creates/opens the database file):
--   duckdb /usr/olap/xltable/data/sample.duckdb -f duckdb_sample.sql
--
-- Usage (Python):
--   python -c "import duckdb; duckdb.connect('sample.duckdb').execute(open('duckdb_sample.sql').read())"
-- =============================================================================


-- ─── 1. Schema ───────────────────────────────────────────────────────────────

CREATE SCHEMA IF NOT EXISTS db;


-- ─── 2. Drop existing tables (safe re-run) ───────────────────────────────────

DROP TABLE IF EXISTS db.olap_definition;
DROP TABLE IF EXISTS db.sales;
DROP TABLE IF EXISTS db.stock;
DROP TABLE IF EXISTS db.managers;
DROP TABLE IF EXISTS db.stores;
DROP TABLE IF EXISTS db.regions;
DROP TABLE IF EXISTS db.models;
DROP TABLE IF EXISTS db.times;


-- ─── 3. Dimension tables ─────────────────────────────────────────────────────

-- Calendar: every day of 2023, 2024 and 2025 (365 + 366 + 365 = 1096 rows)
CREATE TABLE db.times (
    day_str   TEXT,
    month_str TEXT,
    year_str  TEXT
);

INSERT INTO db.times
SELECT
    strftime(d, '%Y-%m-%d') AS day_str,
    strftime(d, '%Y-%m')    AS month_str,
    strftime(d, '%Y')       AS year_str
FROM generate_series(DATE '2023-01-01', DATE '2025-12-31', INTERVAL 1 DAY) AS t(d);


-- Sales regions (4 rows)
CREATE TABLE db.regions (
    id   TEXT,
    name TEXT
);

INSERT INTO db.regions VALUES
    ('R1', 'North'),
    ('R2', 'South'),
    ('R3', 'East'),
    ('R4', 'West');


-- Sales managers – many-to-many with regions (5 rows)
CREATE TABLE db.managers (
    name   TEXT,
    region TEXT    -- references db.regions.id
);

INSERT INTO db.managers VALUES
    ('Alice Johnson', 'R1'),
    ('Bob Smith',     'R2'),
    ('Carol White',   'R3'),
    ('David Brown',   'R4'),
    ('Emma Davis',    'R1');


-- Retail stores, each in one region (8 rows)
CREATE TABLE db.stores (
    id     TEXT,
    name   TEXT,
    region TEXT    -- references db.regions.id
);

INSERT INTO db.stores VALUES
    ('S01', 'Downtown North', 'R1'),
    ('S02', 'Uptown North',   'R1'),
    ('S03', 'South Market',   'R2'),
    ('S04', 'South Center',   'R2'),
    ('S05', 'East Plaza',     'R3'),
    ('S06', 'East Mall',      'R3'),
    ('S07', 'West Gate',      'R4'),
    ('S08', 'West Park',      'R4');


-- Product catalogue (8 rows)
CREATE TABLE db.models (
    id   TEXT,
    name TEXT
);

INSERT INTO db.models VALUES
    ('M01', 'Product Alpha'),
    ('M02', 'Product Beta'),
    ('M03', 'Product Gamma'),
    ('M04', 'Product Delta'),
    ('M05', 'Product Epsilon'),
    ('M06', 'Product Zeta'),
    ('M07', 'Product Eta'),
    ('M08', 'Product Theta');


-- ─── 4. Fact tables ──────────────────────────────────────────────────────────

-- Sales transactions: 3 000 rows spread across 2023–2024
-- hash() provides deterministic pseudo-random distribution.
CREATE TABLE db.sales (
    store     TEXT,
    model     TEXT,
    date_sale TEXT,            -- YYYY-MM-DD, references db.times.day_str
    qty       INTEGER,
    amount    NUMERIC(12, 2)
);

INSERT INTO db.sales
SELECT
    CASE MOD(n, 8)
        WHEN 0 THEN 'S01' WHEN 1 THEN 'S02' WHEN 2 THEN 'S03' WHEN 3 THEN 'S04'
        WHEN 4 THEN 'S05' WHEN 5 THEN 'S06' WHEN 6 THEN 'S07' ELSE      'S08'
    END AS store,
    CASE MOD(hash(CAST(n * 7  AS TEXT)), 8)
        WHEN 0 THEN 'M01' WHEN 1 THEN 'M02' WHEN 2 THEN 'M03' WHEN 3 THEN 'M04'
        WHEN 4 THEN 'M05' WHEN 5 THEN 'M06' WHEN 6 THEN 'M07' ELSE      'M08'
    END AS model,
    strftime(
        DATE '2023-01-01' + CAST(MOD(hash(CAST(n * 3 AS TEXT)), 731) AS INTEGER),
        '%Y-%m-%d')                                                      AS date_sale,
    1  + MOD(hash(CAST(n * 11 AS TEXT)), 100)                            AS qty,
    ROUND((50 + MOD(hash(CAST(n * 13 AS TEXT)), 950)) * 1.5, 2)          AS amount
FROM generate_series(0, 2999) AS t(n);


-- Stock inventory snapshots: 500 rows
CREATE TABLE db.stock (
    store TEXT,
    model TEXT,
    qty   INTEGER
);

INSERT INTO db.stock
SELECT
    CASE MOD(n, 8)
        WHEN 0 THEN 'S01' WHEN 1 THEN 'S02' WHEN 2 THEN 'S03' WHEN 3 THEN 'S04'
        WHEN 4 THEN 'S05' WHEN 5 THEN 'S06' WHEN 6 THEN 'S07' ELSE      'S08'
    END AS store,
    CASE MOD(hash(CAST(n * 5  AS TEXT)), 8)
        WHEN 0 THEN 'M01' WHEN 1 THEN 'M02' WHEN 2 THEN 'M03' WHEN 3 THEN 'M04'
        WHEN 4 THEN 'M05' WHEN 5 THEN 'M06' WHEN 6 THEN 'M07' ELSE      'M08'
    END AS model,
    10 + MOD(hash(CAST(n * 17 AS TEXT)), 500)                            AS qty
FROM generate_series(0, 499) AS t(n);


-- ─── 5. OLAP cube definition ─────────────────────────────────────────────────
-- XLTable reads cube definitions from the `olap_definition` table.
-- Single quotes inside the definition string are escaped by doubling them ('').

CREATE TABLE db.olap_definition (
    id         TEXT,
    definition TEXT
);

INSERT INTO db.olap_definition VALUES (
'myOLAPcube',
'
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.00;-#,##0.00`
--olap_jinja
{{ sql_text | replace("salesly.date_sale", "(salesly.date_sale::date + INTERVAL ''1 year'')::date::text") }}

--olap_source Sales
SELECT
--olap_measures
 sum(sales.qty)    as sales_sum_qty --translation=`Sales Quantity` --format=`#,##0;-#,##0`
,sum(sales.amount) as sales_sum_sum --translation=`Sales Amount`   --format=`#,##0.00;-#,##0.00`
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, 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.amount) as salesly_sum_sum --translation=`Sales last year Amount`   --format=`#,##0.00;-#,##0.00`
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`
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`
,strftime(date_trunc(''quarter'', times.day_str::date), ''%Y-%m'') 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
all
--olap_dimensions_visible
all
--olap_access_filters
');