Trino Sample Data

This page describes a ready-to-run SQL script that creates a complete set of sample Trino tables, 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.

The script file: trino_sample.sql

---

What the script creates

The script uses catalog hive and schema db by default. Replace every occurrence of hive.db with <your_catalog>.<your_schema> before running if your setup differs.

Table	Rows	Description
hive.db.Times	731	Calendar: every day from 2023-01-01 to 2024-12-31
hive.db.Regions	4	Sales regions: North, South, East, West
hive.db.Managers	5	Sales managers linked to regions (many-to-many)
hive.db.Stores	8	Retail stores, each assigned to a region
hive.db.Models	8	Product models (Alpha … Theta)
hive.db.Sales	3 000	Sales transactions: store, model, date, quantity, amount
hive.db.Stock	500	Inventory snapshots: store, model, quantity on hand
hive.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

• Trino cluster (local or remote) reachable from your workstation

• trino CLI installed (available at trino.io/download)

• A catalog configured in Trino (e.g. hive, iceberg, delta)

• A Trino user with CREATE SCHEMA, CREATE TABLE, INSERT privileges

• XLTable server already installed and running (see Installation)

---

Step 1: Run the SQL script

Before running, open trino_sample.sql and replace every occurrence of hive.db with your actual <catalog>.<schema>.

Then execute it using one of the options below.

Option A — Trino CLI with TLS (recommended)

trino --server     https://<your_trino_host>:8443 \
      --user       <user> \
      --password \
      --file       trino_sample.sql

Option B — Trino CLI without TLS

trino --server http://<your_trino_host>:8080 \
      --user   <user> \
      --file   trino_sample.sql

After a successful run the output should contain no errors. Verify that all tables were created and populated:

SELECT 'Times'            AS "table", COUNT(*) AS rows FROM hive.db.Times
UNION ALL SELECT 'Regions',            COUNT(*) FROM hive.db.Regions
UNION ALL SELECT 'Managers',           COUNT(*) FROM hive.db.Managers
UNION ALL SELECT 'Stores',             COUNT(*) FROM hive.db.Stores
UNION ALL SELECT 'Models',             COUNT(*) FROM hive.db.Models
UNION ALL SELECT 'Sales',              COUNT(*) FROM hive.db.Sales
UNION ALL SELECT 'Stock',              COUNT(*) FROM hive.db.Stock
UNION ALL SELECT 'olap_definition',    COUNT(*) FROM hive.db.olap_definition
ORDER BY "table";

Expected output:

table            | rows
-----------------+------
Managers         |    5
Models           |    8
Regions          |    4
Sales            | 3000
Stock            |  500
Stores           |    8
Times            |  731
olap_definition  |    1

---

Step 2: Configure XLTable

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

{
    "SERVER_DB": "Trino",
    "CREDENTIAL_DB": {
        "host": "<your_trino_host>",
        "port": 8443,
        "user": "<user>",
        "password": "<password>",
        "catalog": "hive",
        "http_scheme": "https",
        "verify": false
    },
    "USERS": {"analyst": "password123"},
    "USER_GROUPS": {"analyst": ["olap_users"]}
    ...
}

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

---

Step 3: Restart XLTable

sudo supervisorctl restart 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 analyst / password123.

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

---

Customising the script

Change the catalog or schema

Do a global search-and-replace in trino_sample.sql: hive.db → <your_catalog>.<your_schema>. Also update catalog in settings.json accordingly.

Change the date range

The calendar is generated for 2023–2024. To extend it to 2025, adjust the SEQUENCE upper bound:

-- In hive.db.Times INSERT — add 365 days for 2025 (730 + 365 = 1095)
FROM UNNEST(SEQUENCE(0, 1095)) AS t(n);

Then update the cube definition inside hive.db.olap_definition:

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

Add more stores or models

Extend the INSERT INTO hive.db.Stores / hive.db.Models sections and update the CASE blocks in the hive.db.Sales and hive.db.Stock inserts accordingly.

Use a different schema name

Replace every occurrence of hive.db. with your preferred <catalog>.<schema>., including inside the OLAP cube definition string stored in hive.db.olap_definition.

---

Troubleshooting

Schema not found: hive.db

Make sure the first statement ran successfully. Try running CREATE SCHEMA IF NOT EXISTS hive.db; manually first, or verify the catalog name with SHOW CATALOGS;.

Catalog 'hive' does not exist

Replace hive throughout the script with the name of a catalog actually configured in your Trino cluster. Check available catalogs with SHOW CATALOGS;.

Table not found during INSERT

The corresponding CREATE TABLE did not succeed. Re-run the CREATE TABLE block for that table manually and check for permission errors.

No cubes visible in Excel

Verify the definition row exists:

SELECT id FROM hive.db.olap_definition;

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

Access denied when running the script

The Trino user needs at minimum: CREATE SCHEMA, CREATE TABLE, INSERT, DROP TABLE on the target catalog and schema.

---

Full script

-- =============================================================================
-- XLTable OLAP – Trino sample data script
-- =============================================================================
-- Creates the `db` schema in your Trino catalog, 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 `hive` throughout this script with the name of your
--            actual Trino catalog (e.g. iceberg, delta, memory).
--            Quick search-and-replace:  hive.db  →  <your_catalog>.db
--
-- Prerequisites:
--   - Trino cluster reachable from your workstation
--   - A catalog configured in Trino (e.g. hive, iceberg, delta)
--   - A Trino user with CREATE SCHEMA, CREATE TABLE, INSERT privileges
--
-- Usage (Trino CLI with TLS):
--   trino --server https://<host>:8443 \
--         --user <user> --password \
--         --file trino_sample.sql
--
-- Usage (Trino CLI without TLS):
--   trino --server http://<host>:8080 \
--         --user <user> \
--         --file trino_sample.sql
-- =============================================================================


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

CREATE SCHEMA IF NOT EXISTS hive.db;


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

DROP TABLE IF EXISTS hive.db.olap_definition;
DROP TABLE IF EXISTS hive.db.Sales;
DROP TABLE IF EXISTS hive.db.Stock;
DROP TABLE IF EXISTS hive.db.Managers;
DROP TABLE IF EXISTS hive.db.Stores;
DROP TABLE IF EXISTS hive.db.Regions;
DROP TABLE IF EXISTS hive.db.Models;
DROP TABLE IF EXISTS hive.db.Times;


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

-- Calendar: every day of 2023 and 2024 (365 + 366 = 731 rows)
CREATE TABLE hive.db.Times (
    day_str   VARCHAR,
    month_str VARCHAR,
    year_str  VARCHAR
);

INSERT INTO hive.db.Times
SELECT
    date_format(date_add('day', n, date '2023-01-01'), '%Y-%m-%d') AS day_str,
    date_format(date_add('day', n, date '2023-01-01'), '%Y-%m')    AS month_str,
    date_format(date_add('day', n, date '2023-01-01'), '%Y')       AS year_str
FROM UNNEST(SEQUENCE(0, 730)) AS t(n);


-- Sales regions (4 rows)
CREATE TABLE hive.db.Regions (
    id   VARCHAR,
    name VARCHAR
);

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


-- Sales managers – many-to-many with Regions (5 rows)
CREATE TABLE hive.db.Managers (
    name   VARCHAR,
    region VARCHAR
);

INSERT INTO hive.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 hive.db.Stores (
    id     VARCHAR,
    name   VARCHAR,
    region VARCHAR
);

INSERT INTO hive.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 hive.db.Models (
    id   VARCHAR,
    name VARCHAR
);

INSERT INTO hive.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
-- xxhash64 provides deterministic pseudo-random distribution.
CREATE TABLE hive.db.Sales (
    store     VARCHAR,
    model     VARCHAR,
    date_sale VARCHAR,
    qty       INTEGER,
    amount    DOUBLE
);

INSERT INTO hive.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(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 7  AS VARCHAR))))), 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,
    date_format(
        date_add('day',
            MOD(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 3  AS VARCHAR))))), 731),
            date '2023-01-01'),
        '%Y-%m-%d')                                                                 AS date_sale,
    CAST(1  + MOD(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 11 AS VARCHAR))))), 100) AS INTEGER)     AS qty,
    ROUND(CAST(50 + MOD(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 13 AS VARCHAR))))), 950) AS DOUBLE) * 1.5, 2) AS amount
FROM UNNEST(SEQUENCE(0, 2999)) AS t(n);


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

INSERT INTO hive.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(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 5  AS VARCHAR))))), 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,
    CAST(10 + MOD(ABS(from_big_endian_64(xxhash64(to_utf8(CAST(n * 17 AS VARCHAR))))), 500) AS INTEGER) AS qty
FROM UNNEST(SEQUENCE(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 hive.db.olap_definition (
    id         VARCHAR,
    definition VARCHAR
);

INSERT INTO hive.db.olap_definition VALUES (
'myOLAPcube',
'
with calendar as (
    SELECT * FROM hive.db.Times WHERE year_str IN (''2023'', ''2024'')
)

--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", "date_format(date_add(''year'', 1, date(salesly.date_sale)), ''%Y-%m-%d'')") }}

--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 hive.db.Sales sales
LEFT JOIN hive.db.Stores stores ON sales.store = stores.id
LEFT JOIN hive.db.Models models ON sales.model = models.id
LEFT JOIN calendar times ON sales.date_sale = times.day_str

--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 hive.db.Sales salesly
LEFT JOIN hive.db.Stores stores ON salesly.store = stores.id
LEFT JOIN hive.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 hive.db.Stock stock
LEFT JOIN hive.db.Stores stores ON stock.store = stores.id
LEFT JOIN hive.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 hive.db.Stores stores
LEFT JOIN hive.db.Regions regions ON stores.region = regions.id

--olap_source Regions
SELECT
--olap_dimensions
 regions.name as regions_name --translation=`Region`
FROM hive.db.Regions regions
LEFT JOIN hive.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 hive.db.Managers managers

--olap_source Models
SELECT
--olap_dimensions
 models.name as models_name --translation=`Model`
FROM hive.db.Models models

--olap_source Dates
SELECT
--olap_dimensions
 times.year_str as times_year_str --hierarchy=`Dates` --translation=`Year`
,date_format(date_trunc(''quarter'', date(times.day_str)), ''%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
');