XLTable + Snowflake: From Zero to Pivot Table in 15 Minutes

This guide shows how to connect Excel to Snowflake using XLTable — from creating sample tables to dragging measures into a Pivot Table.

No custom data required. Everything runs on a free Snowflake trial account.

What You Will Build

By the end of this guide you will have:

• A Snowflake database with realistic sales and inventory data
• An OLAP cube named myOLAPcube registered in XLTable
• A live Excel Pivot Table connected to Snowflake — no CSV exports, no BI tools

Data Model Overview

The sample script creates 8 tables in the olap.public schema:

The relationships are straightforward: Sales and Stock facts join to Stores, Models, and the Times calendar. Stores belong to Regions, and Managers are linked to Regions in a many-to-many relationship.

Prerequisites

Before starting, make sure you have:

• 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 installed and running

Step 1: Run the SQL Script

Download the script and run it against your Snowflake account:

👉 Download snowflake_sample.sql

Option A — SnowSQL CLI

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

Option B — Snowflake Worksheets

1. Open Snowflake → Worksheets → + New Worksheet
2. Paste the full script
3. Select your warehouse
4. Click Run All

Verify the result:

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

You should see 8 tables with the row counts from the table above.

Step 2: Configure XLTable

Open /usr/olap/xltable/setting/settings.json and add the Snowflake connection:

{
    "SERVER_DB": "Snowflake",
    "CREDENTIAL_DB": {
        "user":      "<user>",
        "password":  "<password>",
        "account":   "<your_account>",
        "warehouse": "COMPUTE_WH",
        "schema":    "olap.public"
    },
    "USERS": {"analyst": "password123"},
    "USER_GROUPS": {"analyst": ["olap_users"]}
}

XLTable discovers cubes automatically from the olap_definition table — no additional cube configuration needed.

Step 3: Restart XLTable

sudo supervisorctl restart olap

Step 4: Connect Excel

1. Open Excel → 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 measures and dimensions onto the Pivot Table

What the Cube Exposes

The myOLAPcube cube provides the following fields out of the box:

Measures

Dimensions

How the OLAP Cube Is Defined

The cube definition lives inside the olap_definition table as a SQL script with XLTable annotations. Each source section maps a SQL query to a set of measures or dimensions:

--olap_source Sales
SELECT
--olap_measures
 sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity`
,sum(sales.sum) as sales_sum_sum --translation=`Sales Amount`
FROM olap.public.Sales sales
LEFT JOIN olap.public.Stores stores ON sales.store = stores.id
LEFT JOIN calendar times ON sales.date_sale = times.day_str

Year-over-year comparison is handled with a Jinja transformation — XLTable rewrites the date column at query time, no separate table or materialized view needed:

--olap_jinja
{{ sql_text | replace("salesly.date_sale",
   "TO_VARCHAR(DATEADD(YEAR, 1, TO_DATE(salesly.date_sale)), 'YYYY-MM-DD')") }}

Customising the Sample

Extend the date range to 2025

In the Times table generator, change the row count from 731 to 1096 (731 + 365), then update the cube filter:

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

Add more stores or models

Extend the VALUES lists in the Stores / Models sections and update the MOD(..., 8) expressions in the Sales and Stock inserts to match the new total count.

Use a different schema

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

Common Issues

Database 'OLAP' does not exist

Run the first two statements manually before the rest of the script:

CREATE DATABASE IF NOT EXISTS olap;
USE DATABASE olap;

Insufficient privileges

Switch to a role that has the required privileges:

USE ROLE SYSADMIN;

Virtual warehouse is suspended

Resume the warehouse before running the script:

ALTER WAREHOUSE COMPUTE_WH RESUME;

No cubes visible in Excel

Verify the definition row exists and check that USER_GROUPS in settings.json includes "olap_users" for the connecting user:

SELECT id FROM olap.public.olap_definition;

Invalid account identifier

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

Summary

• One SQL script creates a complete, realistic data model in Snowflake
• XLTable reads the cube definition directly from the database — no YAML, no GUI
• Excel connects natively via XMLA, with no plugins or data exports
• Year-over-year and inventory turnover work out of the box

Try It Yourself

Download the sample script from the XLTable documentation and follow the steps above.

👉 Get a 30-day XLTable trial 👉 Read the full documentation