Snowflake Sample Data

This page describes a ready-to-run SQL script that creates a complete set of sample Snowflake 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: snowflake_sample.sql

---

What the script creates

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

             ┌──────────────────────────┐
             │  olap.public.Times       │
             │  (calendar)              │
             └──────────┬───────────────┘
                        │ day_str
       ┌────────────────┴────────────────┐
       │                                 │
┌──────┴──────┐                   ┌──────┴──────┐
│   .Sales    │                   │   .Stock    │
└──────┬──────┘                   └──────┬──────┘
       │ store / model                   │ store / model
┌──────┴──────┐                   ┌──────┴──────┐
│   .Stores   ├───────────────────┤   .Models   │
└──────┬──────┘                   └─────────────┘
       │ region
┌──────┴──────┐
│   .Regions  │
└──────┬──────┘
       │ id  (many-to-many)
┌──────┴──────┐
│  .Managers  │
└─────────────┘

---

Prerequisites

• A Snowflake account (Trial or paid)

• A user with SYSADMIN role or CREATE DATABASE privilege

• A running virtual warehouse (e.g. COMPUTE_WH)

• SnowSQL CLI installed, or access to Snowflake Worksheets

• XLTable server already installed and running (see Installation)

---

Step 1: Run the SQL script

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

Option A — SnowSQL CLI

snowsql \
  --accountname <your_account> \
  --username    <user> \
  --dbname      olap \
  --schemaname  public \
  -f snowflake_sample.sql

Option B — Snowflake Worksheets (Web UI)

1. Open Snowflake → Worksheets → + New Worksheet.

2. Paste the full contents of snowflake_sample.sql.

3. Select your warehouse from the dropdown.

4. Click Run All.

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

SELECT table_name, row_count
FROM olap.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY table_name;

Expected output:

┌──────────────────────┬───────────┐
│ TABLE_NAME           │ ROW_COUNT │
├──────────────────────┼───────────┤
│ MANAGERS             │         5 │
│ MODELS               │         8 │
│ OLAP_DEFINITION      │         1 │
│ REGIONS              │         4 │
│ SALES                │      3000 │
│ STOCK                │       500 │
│ STORES               │         8 │
│ TIMES                │       731 │
└──────────────────────┴───────────┘

---

Step 2: Configure XLTable

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

{
    "SERVER_DB": "Snowflake",
    "CREDENTIAL_DB": {
        "user":      "<user>",
        "password":  "<password>",
        "account":   "<your_account>",
        "warehouse": "COMPUTE_WH",
        "schema":    "olap.public"
    },
    "WRITE_LOG": false,
    "MAX_ROWS": 100000,
    "CONVERT_FIELDS_TO_STRING": true,
    "USERS": {"user1": "pass1", "user2": "pass2"},
    "USER_GROUPS": {"user1": ["olap_users", "olap_admins"], "user2": ["olap_users"]},
    "ADMIN_GROUPS": ["olap_admins"],
    "CREDENTIAL_ACTIVE_DIRECTORY": {
       "server_address": "..",
       "domain": "..",
       "domain_full": "..",
       "username": "..",
       "password": "..",
       "access_groups": ["..", ".."]
     },
    "LDAP_CACHE_TIMEOUT": 300
}

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 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.sum)
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–2024. To extend it to 2025, increase the generator row count and adjust the filter:

-- In olap.public.Times — add 365 rows for 2025 (731 + 365 = 1096)
FROM TABLE(GENERATOR(ROWCOUNT => 1096))

Then update the cube definition inside olap.public.olap_definition:

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

Add more stores or models

Extend the VALUES lists in the olap.public.Stores / olap.public.Models sections. Update the CASE MOD(..., 8) expressions in the Sales and Stock inserts accordingly, changing 8 to the new total count.

Use a different database or schema

Replace every occurrence of olap.public with your own database and schema. Also update "schema" in settings.json.

---

Troubleshooting

SQL compilation error: Database 'OLAP' does not exist

Run the first two statements manually:

CREATE DATABASE IF NOT EXISTS olap;
USE DATABASE olap;

Insufficient privileges to operate on database

Grant the required privileges or switch to a role that has them:

USE ROLE SYSADMIN;

Virtual warehouse is suspended / query times out

Resume the warehouse before running the script:

ALTER WAREHOUSE COMPUTE_WH RESUME;

No cubes visible in Excel

Verify the definition row exists:

SELECT id FROM olap.public.olap_definition;

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

Invalid account identifier

The account field in settings.json must use the Snowflake account locator format, e.g. xy12345.eu-west-1. Find it in Snowflake UI → Admin → Accounts.

---

Full script

-- =============================================================================
-- XLTable OLAP – Snowflake sample data script
-- =============================================================================
-- Creates the `olap` database, all required dimension and fact tables,
-- fills them with deterministic test data, and registers the `myOLAPcube`
-- OLAP cube definition (see reference.html#unified-example).
--
-- Prerequisites:
--   - A Snowflake account with SYSADMIN or equivalent role
--   - A warehouse (e.g. COMPUTE_WH) available
--   - A user with CREATE DATABASE, CREATE TABLE, INSERT privileges
--
-- Usage (SnowSQL CLI):
--   snowsql -a <account> -u <user> -f snowflake_sample.sql
--
-- Usage (Snowflake Worksheets):
--   Paste the script contents into a worksheet and click Run All.
-- =============================================================================


-- ─── 1. Database & schema ────────────────────────────────────────────────────

CREATE DATABASE IF NOT EXISTS olap;
USE DATABASE olap;
CREATE SCHEMA IF NOT EXISTS public;
USE SCHEMA public;


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

-- Calendar: every day of 2023 and 2024 (365 + 366 = 731 rows)
CREATE OR REPLACE TABLE olap.public.Times AS
WITH seq AS (
    SELECT SEQ4() AS n FROM TABLE(GENERATOR(ROWCOUNT => 731))
)
SELECT
    TO_VARCHAR(DATEADD(DAY, n, '2023-01-01'), 'YYYY-MM-DD') AS day_str,
    TO_VARCHAR(DATEADD(DAY, n, '2023-01-01'), 'YYYY-MM')    AS month_str,
    TO_VARCHAR(DATEADD(DAY, n, '2023-01-01'), 'YYYY')       AS year_str
FROM seq;


-- Sales regions (4 rows)
CREATE OR REPLACE TABLE olap.public.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 olap.public.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 olap.public.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 olap.public.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() provides deterministic pseudo-random distribution.
CREATE OR REPLACE TABLE olap.public.Sales AS
WITH seq AS (
    SELECT SEQ4() AS n FROM TABLE(GENERATOR(ROWCOUNT => 3000))
)
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(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,
    TO_VARCHAR(
        DATEADD(DAY, MOD(ABS(HASH(n * 3)), 731), '2023-01-01'),
        'YYYY-MM-DD')                                                            AS date_sale,
    CAST(1  + MOD(ABS(HASH(n * 11)), 100) AS INTEGER)                           AS qty,
    ROUND(CAST(50 + MOD(ABS(HASH(n * 13)), 950) AS FLOAT) * 1.5, 2)            AS sum
FROM seq;


-- Stock inventory snapshots: 500 rows
CREATE OR REPLACE TABLE olap.public.Stock AS
WITH seq AS (
    SELECT SEQ4() AS n FROM TABLE(GENERATOR(ROWCOUNT => 500))
)
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(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 + MOD(ABS(HASH(n * 17)), 500) AS INTEGER)                           AS qty
FROM seq;


-- ─── 4. OLAP cube definition ─────────────────────────────────────────────────
-- XLTable reads cube definitions from the `olap_definition` table.
-- Snowflake dollar-quoting ($$) allows single quotes without escaping.

CREATE OR REPLACE TABLE olap.public.olap_definition AS
SELECT 'myOLAPcube' AS id,
$$
with calendar as (
    SELECT * FROM olap.public.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", "TO_VARCHAR(DATEADD(YEAR, 1, TO_DATE(salesly.date_sale)), 'YYYY-MM-DD')") }}

--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`
FROM olap.public.Sales sales
LEFT JOIN olap.public.Stores stores ON sales.store = stores.id
LEFT JOIN olap.public.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.sum) as salesly_sum_sum --translation=`Sales last year Amount`   --format=`#,##0.00;-#,##0.00`
FROM olap.public.Sales salesly
LEFT JOIN olap.public.Stores stores ON salesly.store = stores.id
LEFT JOIN olap.public.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 olap.public.Stock stock
LEFT JOIN olap.public.Stores stores ON stock.store = stores.id
LEFT JOIN olap.public.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 olap.public.Stores stores
LEFT JOIN olap.public.Regions regions ON stores.region = regions.id

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

--olap_source Models
SELECT
--olap_dimensions
 models.name as models_name --translation=`Model`
FROM olap.public.Models models

--olap_source Dates
SELECT
--olap_dimensions
 times.year_str as times_year_str --hierarchy=`Dates` --translation=`Year`
,TO_VARCHAR(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
$$ AS definition;