BigQuery Sample Data

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

---

What the script creates

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

---

Prerequisites

• A Google Cloud project with the BigQuery API enabled

• The olap dataset created in your project:

  bq mk --dataset <project_id>:olap
  

• A service account (or user account) with the following IAM roles on the dataset: BigQuery Data Editor and BigQuery Job User

• A service account key file (JSON) downloaded to the XLTable server

• XLTable server already installed and running (see Installation)

---

Step 1: Run the SQL script

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

Option A — bq CLI

bq query \
  --use_legacy_sql=false \
  --project_id=<your_project_id> \
  < bigquery_sample.sql

Option B — BigQuery Studio (Cloud Console)

1. Open the BigQuery Studio page.

2. Click + New query.

3. Paste the full contents of bigquery_sample.sql into the editor.

4. Select your project from the project picker.

5. Click Run.

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

SELECT table_id, row_count
FROM `olap.__TABLES__`
ORDER BY table_id;

Expected output:

┌──────────────────────┬───────────┐
│ table_id             │ row_count │
├──────────────────────┼───────────┤
│ 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": "BigQuery",
    "CREDENTIAL_DB": {
        "key_path": "/path/to/service-account-key.json"
    },
    "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.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, adjust the GENERATE_DATE_ARRAY end date:

-- In olap.Times — extend end date by one year
FROM UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2025-12-31')) AS day;

Then update the cube definition inside olap.olap_definition:

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

Add more stores or models

Extend the UNNEST(ARRAY<STRUCT<...>>[...]) literals in the olap.Stores / olap.Models sections. Update the stores_arr / models_arr CTEs in the Sales and Stock inserts accordingly, and adjust the % 8 modulo to match the new count.

Use a different dataset name

Replace every occurrence of olap with your own dataset name. Also update the key_path and project_id in settings.json.

---

Troubleshooting

Not found: Dataset <project>:olap

Create the dataset first:

bq mk --dataset <project_id>:olap

Access Denied: BigQuery BigQuery: Permission denied

Ensure the service account has BigQuery Data Editor and BigQuery Job User roles on the project or dataset.

Syntax error near ARRAY<STRUCT<...>>

Make sure --use_legacy_sql=false is passed to the bq CLI. Legacy SQL does not support standard SQL type syntax.

No cubes visible in Excel

Verify the definition row exists:

SELECT id FROM `olap.olap_definition`;

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

key_path file not found on XLTable startup

The service account JSON file must be accessible to the XLTable process. Use an absolute path and ensure file permissions allow the server user to read it.

---

Full script

-- =============================================================================
-- XLTable OLAP – BigQuery sample data script
-- =============================================================================
-- Creates all required dimension and fact tables in the `olap` dataset,
-- fills them with deterministic test data, and registers the `myOLAPcube`
-- OLAP cube definition (see reference.html#unified-example).
--
-- Prerequisites:
--   - A Google Cloud project with the BigQuery API enabled
--   - The `olap` dataset must already exist:
--       bq mk --dataset <project_id>:olap
--   - A service account (or user) with BigQuery Data Editor + Job User roles
--
-- Usage (bq CLI):
--   bq query --use_legacy_sql=false --project_id=<project_id> \
--     < bigquery_sample.sql
--
-- Usage (Cloud Console / BigQuery Studio):
--   Paste the script contents into the query editor and click Run.
-- =============================================================================


-- ─── 1. Dimension tables ─────────────────────────────────────────────────────

-- Calendar: every day of 2023 and 2024 (365 + 366 = 731 rows)
CREATE OR REPLACE TABLE `olap.Times` AS
SELECT
    FORMAT_DATE('%Y-%m-%d', day) AS day_str,
    FORMAT_DATE('%Y-%m',    day) AS month_str,
    FORMAT_DATE('%Y',       day) AS year_str
FROM UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2024-12-31')) AS day;


-- Sales regions (4 rows)
CREATE OR REPLACE TABLE `olap.Regions` AS
SELECT id, name
FROM UNNEST(ARRAY<STRUCT<id STRING, name STRING>>[
    ('R1', 'North'),
    ('R2', 'South'),
    ('R3', 'East'),
    ('R4', 'West')
]);


-- Sales managers – many-to-many with Regions (5 rows)
CREATE OR REPLACE TABLE `olap.Managers` AS
SELECT name, region
FROM UNNEST(ARRAY<STRUCT<name STRING, region STRING>>[
    ('Alice Johnson', 'R1'),
    ('Bob Smith',     'R2'),
    ('Carol White',   'R3'),
    ('David Brown',   'R4'),
    ('Emma Davis',    'R1')
]);


-- Retail stores, each in one region (8 rows)
CREATE OR REPLACE TABLE `olap.Stores` AS
SELECT id, name, region
FROM UNNEST(ARRAY<STRUCT<id STRING, name STRING, region STRING>>[
    ('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 OR REPLACE TABLE `olap.Models` AS
SELECT id, name
FROM UNNEST(ARRAY<STRUCT<id STRING, name STRING>>[
    ('M01', 'Product Alpha'),
    ('M02', 'Product Beta'),
    ('M03', 'Product Gamma'),
    ('M04', 'Product Delta'),
    ('M05', 'Product Epsilon'),
    ('M06', 'Product Zeta'),
    ('M07', 'Product Eta'),
    ('M08', 'Product Theta')
]);


-- ─── 2. Fact tables ──────────────────────────────────────────────────────────

-- Sales transactions: 3 000 rows spread across 2023–2024
-- FARM_FINGERPRINT provides deterministic pseudo-random distribution.
CREATE OR REPLACE TABLE `olap.Sales` AS
WITH
  stores_arr AS (SELECT ['S01','S02','S03','S04','S05','S06','S07','S08'] AS arr),
  models_arr AS (SELECT ['M01','M02','M03','M04','M05','M06','M07','M08'] AS arr)
SELECT
    (SELECT arr[SAFE_OFFSET(MOD(n, 8))]
     FROM stores_arr)                                                              AS store,
    (SELECT arr[SAFE_OFFSET(MOD(ABS(FARM_FINGERPRINT(CAST(n * 7  AS STRING))), 8))]
     FROM models_arr)                                                              AS model,
    FORMAT_DATE(
        '%Y-%m-%d',
        DATE_ADD(DATE '2023-01-01',
            INTERVAL MOD(ABS(FARM_FINGERPRINT(CAST(n * 3 AS STRING))), 731) DAY)) AS date_sale,
    CAST(1  + MOD(ABS(FARM_FINGERPRINT(CAST(n * 11 AS STRING))), 100)
         AS INT64)                                                                 AS qty,
    ROUND(CAST(50 + MOD(ABS(FARM_FINGERPRINT(CAST(n * 13 AS STRING))), 950)
               AS FLOAT64) * 1.5, 2)                                              AS sum
FROM UNNEST(GENERATE_ARRAY(0, 2999)) AS n;


-- Stock inventory snapshots: 500 rows
CREATE OR REPLACE TABLE `olap.Stock` AS
WITH
  stores_arr AS (SELECT ['S01','S02','S03','S04','S05','S06','S07','S08'] AS arr),
  models_arr AS (SELECT ['M01','M02','M03','M04','M05','M06','M07','M08'] AS arr)
SELECT
    (SELECT arr[SAFE_OFFSET(MOD(n, 8))]
     FROM stores_arr)                                                              AS store,
    (SELECT arr[SAFE_OFFSET(MOD(ABS(FARM_FINGERPRINT(CAST(n * 5  AS STRING))), 8))]
     FROM models_arr)                                                              AS model,
    CAST(10 + MOD(ABS(FARM_FINGERPRINT(CAST(n * 17 AS STRING))), 500)
         AS INT64)                                                                 AS qty
FROM UNNEST(GENERATE_ARRAY(0, 499)) AS n;


-- ─── 3. OLAP cube definition ─────────────────────────────────────────────────
-- XLTable reads cube definitions from the `olap_definition` table.
-- BigQuery triple-quoted strings (""") allow single quotes without escaping.

CREATE OR REPLACE TABLE `olap.olap_definition` AS
SELECT 'myOLAPcube' AS id,
"""
with calendar as (
    SELECT * FROM olap.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", "FORMAT_DATE('%Y-%m-%d', DATE_ADD(PARSE_DATE('%Y-%m-%d', salesly.date_sale), INTERVAL 1 YEAR))") }}

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

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

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

--olap_source Dates
SELECT
--olap_dimensions
 times.year_str as times_year_str --hierarchy=`Dates` --translation=`Year`
,FORMAT_DATE('%Y-%m', DATE_TRUNC(DATE(times.day_str), QUARTER)) 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;