Greenplum Sample Data¶
This page describes a ready-to-run SQL script that creates a complete set of
sample Greenplum 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: greenplum_sample.sql
What the script creates¶
The script creates a schema named db inside your Greenplum database.
Replace every occurrence of db. with <your_schema>. before running
if your setup differs.
Table |
Rows |
Description |
|---|---|---|
|
731 |
Calendar: every day from 2023-01-01 to 2024-12-31 |
|
4 |
Sales regions: North, South, East, West |
|
5 |
Sales managers linked to regions (many-to-many) |
|
8 |
Retail stores, each assigned to a region |
|
8 |
Product models (Alpha … Theta) |
|
3 000 |
Sales transactions: store, model, date, quantity, amount |
|
500 |
Inventory snapshots: store, model, quantity on hand |
|
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¶
Greenplum instance (local or remote) reachable from your workstation
psqlCLI installed (bundled with Greenplum or PostgreSQL client tools)A Greenplum user with
CREATE SCHEMA,CREATE TABLE,INSERTprivileges on the target databaseXLTable server already installed and running (see Installation)
Step 1: Run the SQL script¶
Download greenplum_sample.sql and run it
against your Greenplum instance using one of the options below.
Option A — psql with TLS (recommended)
psql "host=<your_greenplum_host> \
port=5432 \
dbname=<database> \
user=<user> \
password=<password> \
sslmode=require" \
-f greenplum_sample.sql
Option B — psql without TLS
psql "host=<your_greenplum_host> \
port=5432 \
dbname=<database> \
user=<user> \
password=<password>" \
-f greenplum_sample.sql
Option C — connection URL
psql postgresql://<user>:<password>@<your_greenplum_host>:5432/<database>?sslmode=require \
-f greenplum_sample.sql
After a successful run the output should contain no errors. Verify that all tables were created:
SELECT 'managers' AS "table", COUNT(*) AS rows FROM db.managers
UNION ALL
SELECT 'models', COUNT(*) FROM db.models
UNION ALL
SELECT 'olap_definition', COUNT(*) FROM db.olap_definition
UNION ALL
SELECT 'regions', COUNT(*) FROM db.regions
UNION ALL
SELECT 'sales', COUNT(*) FROM db.sales
UNION ALL
SELECT 'stock', COUNT(*) FROM db.stock
UNION ALL
SELECT 'stores', COUNT(*) FROM db.stores
UNION ALL
SELECT 'times', COUNT(*) FROM db.times
ORDER BY "table";
Expected output:
table | rows
-----------------+------
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": "Greenplum",
"CREDENTIAL_DB": {
"host": "<your_greenplum_host>",
"port": 6432,
"sslmode": "require",
"dbname": "<database>",
"user": "<user>",
"password": "<password>",
"target_session_attrs": "read-write"
},
"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¶
Open Excel and go to Data → Get Data → From Database → From Analysis Services.
Enter the server URL:
http://your_server_ipLog in with
user1 / pass1.Select
myOLAPcube.Drag any measures and dimensions onto the Pivot Table — done.
Available fields in the Pivot Table:
Field name (Excel) |
Type |
Notes |
|---|---|---|
Sales Quantity |
Measure |
|
Sales Amount |
Measure |
|
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 |
|
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 |
|
Customising the script¶
Change the date range
The calendar is generated for 2023–2024 using generate_series.
To extend it to 2025, change the end date and update the cube filter:
-- In db.times INSERT — extend generate_series to 2025-12-31
FROM generate_series('2023-01-01'::date, '2025-12-31'::date, '1 day'::interval) AS d;
Then update the cube definition inside db.olap_definition:
WHERE year_str IN ('2023', '2024', '2025')
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..
Also update the host, dbname, user, and credentials in
settings.json.
Troubleshooting¶
ERROR: schema "db" does not existThe first statement in the script did not run successfully. Try running
CREATE SCHEMA IF NOT EXISTS db;manually first.ERROR: permission denied for schema dbThe Greenplum user needs at minimum:
CREATE,USAGEon the schema, andCREATE TABLE,INSERTon the database. Grant them with:GRANT USAGE, CREATE ON SCHEMA db TO <user>;
ERROR: column "hashtext" does not existor syntax errorsMake sure you are connecting to a Greenplum (or PostgreSQL ≥ 9.4) instance. The
hashtextfunction andgenerate_serieswith dates are built-in and require no extensions.No cubes visible in ExcelVerify the definition row exists:
SELECT id FROM db.olap_definition;
Also confirm that
USER_GROUPSinsettings.jsoncontains"olap_users"for the connecting user.Access deniedwhen running the scriptThe Greenplum user needs at minimum:
CREATE SCHEMA,CREATE TABLE,INSERT,DROP TABLEon the target database and schema.
Full script¶
-- =============================================================================
-- XLTable OLAP – Greenplum sample data script
-- =============================================================================
-- Creates the `db` schema inside your Greenplum database, 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:
-- - Greenplum instance reachable from your workstation
-- - psql CLI installed (bundled with Greenplum / PostgreSQL client tools)
-- - A Greenplum user with CREATE SCHEMA, CREATE TABLE, INSERT privileges
--
-- Usage (psql with TLS):
-- psql "host=<host> port=5432 dbname=<database> \
-- user=<user> password=<password> sslmode=require" \
-- -f greenplum_sample.sql
--
-- Usage (psql without TLS):
-- psql "host=<host> port=5432 dbname=<database> \
-- user=<user> password=<password>" \
-- -f greenplum_sample.sql
--
-- Usage (connection URL):
-- psql postgresql://<user>:<password>@<host>:5432/<database>?sslmode=require \
-- -f greenplum_sample.sql
-- =============================================================================
-- ─── 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 and 2024 (365 + 366 = 731 rows)
CREATE TABLE db.times (
day_str TEXT,
month_str TEXT,
year_str TEXT
);
INSERT INTO db.times
SELECT
to_char(d, 'YYYY-MM-DD') AS day_str,
to_char(d, 'YYYY-MM') AS month_str,
to_char(d, 'YYYY') AS year_str
FROM generate_series('2023-01-01'::date, '2024-12-31'::date, '1 day'::interval) AS 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
-- hashtext() 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(ABS(hashtext(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,
to_char(
'2023-01-01'::date
+ (MOD(ABS(hashtext(CAST(n * 3 AS TEXT))), 731) || ' days')::interval,
'YYYY-MM-DD') AS date_sale,
1 + MOD(ABS(hashtext(CAST(n * 11 AS TEXT))), 100) AS qty,
ROUND(CAST(50 + MOD(ABS(hashtext(CAST(n * 13 AS TEXT))), 950) AS NUMERIC) * 1.5, 2) AS amount
FROM generate_series(0, 2999) AS 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(ABS(hashtext(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(ABS(hashtext(CAST(n * 17 AS TEXT))), 500) AS qty
FROM generate_series(0, 499) AS 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'')
)
--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'')::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_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`
,to_char(date_trunc(''quarter'', times.day_str::date), ''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
');