Databricks Sample Data

This page describes a ready-to-run SQL script that creates a complete set of sample Databricks (Delta) 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: databricks_sample.sql

---

What the script creates

The script creates schema db in the current catalog — hive_metastore by default, which matches the default catalog behaviour of XLTable. On Unity Catalog, run USE CATALOG <name>; first and set the same catalog in settings.json.

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

• A Databricks workspace with a running SQL warehouse (or an all-purpose cluster)

• A user with CREATE SCHEMA, CREATE TABLE privileges in the catalog

• A personal access token for XLTable (User Settings → Developer → Access tokens)

• XLTable server already installed and running (see Installation)

---

Step 1: Run the SQL script

Download databricks_sample.sql and run it using one of the options below.

Option A — Databricks SQL editor (recommended)

1. Open your workspace and go to SQL Editor.

2. Select a running SQL warehouse.

3. Paste the script contents into a new query and click Run all.

Option B — Databricks SQL CLI

dbsqlcli --hostname   <workspace-host> \
         --http-path  <warehouse-http-path> \
         --access-token <dapi...> \
         -e databricks_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 db.Times
UNION ALL SELECT 'Regions',           COUNT(*) FROM db.Regions
UNION ALL SELECT 'Managers',          COUNT(*) FROM db.Managers
UNION ALL SELECT 'Stores',            COUNT(*) FROM db.Stores
UNION ALL SELECT 'Models',            COUNT(*) FROM db.Models
UNION ALL SELECT 'Sales',             COUNT(*) FROM db.Sales
UNION ALL SELECT 'Stock',             COUNT(*) FROM db.Stock
UNION ALL SELECT 'olap_definition',   COUNT(*) FROM db.olap_definition
ORDER BY `table`;

Expected output:

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

---

Step 2: Configure XLTable

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

{
    "SERVER_DB": "Databricks",
    "CREDENTIAL_DB": {
        "server_hostname": "adb-xxxxxxxxxxxx.azuredatabricks.net",
        "http_path": "/sql/1.0/warehouses/xxxxxxxxxxxx",
        "access_token": "dapi..."
    },
    "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"],
    "LDAP_CACHE_TIMEOUT": 300
}

server_hostname and http_path can be found in the Databricks workspace under SQL Warehouses → Connection details. If you created the sample in a Unity Catalog catalog (not hive_metastore), add "catalog": "<name>" to CREDENTIAL_DB.

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

---

Customising the script

Change the date range

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

-- In db.Times — add 365 days for 2026 (1096 + 365 = 1461)
FROM range(0, 1461);

Then update the cube definition inside db.olap_definition:

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

Add more stores or models

Extend the VALUES lists in db.Stores / db.Models and update the CASE blocks in the db.Sales and db.Stock queries accordingly.

Use a different schema or catalog

Replace every occurrence of db. with your own prefix, e.g. mydb., including inside the OLAP cube definition string stored in db.olap_definition. For a non-default catalog, run USE CATALOG <name>; before the script and set "catalog" in settings.json.

---

Troubleshooting

Schema 'db' not found

Make sure CREATE SCHEMA IF NOT EXISTS db; ran in the same catalog you are querying. Check the current catalog with SELECT current_catalog();.

PERMISSION_DENIED when creating tables

On Unity Catalog the user needs USE CATALOG, USE SCHEMA, CREATE TABLE grants. Ask your workspace admin, or run the sample in hive_metastore.

Warehouse is stopped

Databricks SQL warehouses auto-stop when idle. Start the warehouse in SQL Warehouses before running the script or connecting from Excel.

No cubes visible in Excel

Verify the definition row exists:

SELECT id FROM db.olap_definition;

Also confirm that USER_GROUPS in settings.json contains "olap_users" for the connecting user, and that catalog in CREDENTIAL_DB matches the catalog where the sample was created.

Invalid access token

Personal access tokens expire. Generate a new one in User Settings → Developer → Access tokens and update access_token in settings.json.

---

Full script

-- =============================================================================
-- XLTable OLAP – Databricks sample data script
-- =============================================================================
-- Creates the `db` schema, all required dimension and fact tables (Delta),
-- fills them with ~3 500 rows of deterministic test data, and registers
-- the `myOLAPcube` OLAP cube definition (see reference.html#unified-example).
--
-- The script uses two-level names (db.<table>) and runs in the current
-- catalog — `hive_metastore` by default, matching the default `catalog`
-- behaviour of XLTable. On Unity Catalog, run `USE CATALOG <name>;` first
-- and set the same catalog in settings.json.
--
-- Prerequisites:
--   - A Databricks SQL warehouse (or an all-purpose cluster)
--   - A user with CREATE SCHEMA, CREATE TABLE privileges in the catalog
--
-- Usage (SQL editor, recommended):
--   Paste the script contents into a new query in the Databricks SQL editor
--   and click Run.
--
-- Usage (Databricks SQL CLI):
--   dbsqlcli --hostname <workspace-host> --http-path <warehouse-http-path> \
--            --access-token <dapi...> -e databricks_sample.sql
-- =============================================================================


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

CREATE SCHEMA IF NOT EXISTS db;


-- ─── 2. Dimension tables ─────────────────────────────────────────────────────

-- Calendar: every day of 2023, 2024 and 2025 (365 + 366 + 365 = 1096 rows)
CREATE OR REPLACE TABLE db.Times AS
SELECT
    date_format(date_add(DATE'2023-01-01', CAST(id AS INT)), 'yyyy-MM-dd') AS day_str,
    date_format(date_add(DATE'2023-01-01', CAST(id AS INT)), 'yyyy-MM')    AS month_str,
    date_format(date_add(DATE'2023-01-01', CAST(id AS INT)), 'yyyy')       AS year_str
FROM range(0, 1096);


-- Sales regions (4 rows)
CREATE OR REPLACE TABLE db.Regions AS
SELECT * FROM VALUES
    ('R1', 'North'),
    ('R2', 'South'),
    ('R3', 'East'),
    ('R4', 'West')
AS t(id, name);


-- Sales managers – many-to-many with Regions (5 rows)
CREATE OR REPLACE TABLE db.Managers AS
SELECT * FROM VALUES
    ('Alice Johnson', 'R1'),
    ('Bob Smith',     'R2'),
    ('Carol White',   'R3'),
    ('David Brown',   'R4'),
    ('Emma Davis',    'R1')
AS t(name, region);


-- Retail stores, each in one region (8 rows)
CREATE OR REPLACE TABLE db.Stores AS
SELECT * FROM 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')
AS t(id, name, region);


-- Product catalogue (8 rows)
CREATE OR REPLACE TABLE db.Models AS
SELECT * FROM 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')
AS t(id, name);


-- ─── 3. Fact tables ──────────────────────────────────────────────────────────

-- Sales transactions: 3 000 rows spread across 2023–2024
-- hash() (murmur3) with pmod() provides deterministic pseudo-random distribution.
CREATE OR REPLACE TABLE db.Sales AS
WITH seq AS (
    SELECT CAST(id AS INT) AS n FROM range(0, 3000)
)
SELECT
    CASE pmod(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 pmod(hash(n * 7), 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(DATE'2023-01-01', pmod(hash(n * 3), 731)),
        'yyyy-MM-dd')                                               AS date_sale,
    CAST(1 + pmod(hash(n * 11), 100) AS INT)                        AS qty,
    ROUND((50 + pmod(hash(n * 13), 950)) * 1.5, 2)                  AS amount
FROM seq;


-- Stock inventory snapshots: 500 rows
CREATE OR REPLACE TABLE db.Stock AS
WITH seq AS (
    SELECT CAST(id AS INT) AS n FROM range(0, 500)
)
SELECT
    CASE pmod(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 pmod(hash(n * 5), 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 + pmod(hash(n * 17), 500) AS INT)                       AS qty
FROM seq;


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

CREATE OR REPLACE TABLE db.olap_definition (
    id         STRING,
    definition STRING
);

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", "date_format(add_months(to_date(salesly.date_sale), 12), ''yyyy-MM-dd'')") }}

--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`
,date_format(date_trunc(''QUARTER'', to_date(times.day_str)), ''yyyy-MM'') 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
');