Forem: XLTable

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

XLTable — Sat, 04 Apr 2026 18:49:00 +0000

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:

Table	Rows	Description
`Times`	731	Calendar: every day of 2023–2024
`Regions`	4	Sales regions: North, South, East, West
`Managers`	5	Sales managers linked to regions (many-to-many)
`Stores`	8	Retail stores, each assigned to a region
`Models`	8	Product models (Alpha … Theta)
`Sales`	3 000	Transactions: store, model, date, quantity, amount
`Stock`	500	Inventory snapshots: store, model, quantity on hand
`olap_definition`	1	OLAP cube definition read by XLTable

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

Open Snowflake → Worksheets → + New Worksheet
Paste the full script
Select your warehouse
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

Open Excel → Data → Get Data → From Database → From Analysis Services
Enter the server URL: http://your_server_ip
Log in with analyst / password123
Select myOLAPcube
Drag measures and dimensions onto the Pivot Table

What the Cube Exposes

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

Measures

Field	Description
Sales Quantity	`sum(sales.qty)` across selected filters
Sales Amount	`sum(sales.sum)` — revenue
Sales last year Quantity	Same query, dates shifted +1 year via Jinja
Sales last year Amount	Revenue for the same period last year
Average Stock Quantity	`avg(stock.qty)` per store and model
Turnover	Calculated: Sales Quantity ÷ Average Stock Quantity

Dimensions

Field	Notes
Store ID / Store	Individual retail locations
Region	North · South · East · West
Manager	Many-to-many with Region
Model	Alpha … Theta
Year / Quarter / Month / Day	`Dates` hierarchy with full drill-down

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

XLTable: Bringing the OLAP Experience Back to Excel on Modern Data Warehouses

XLTable — Thu, 29 Jan 2026 15:15:03 +0000

XLTable: Bringing the OLAP Experience Back to Excel on Modern Data Warehouses

)

The problem: data has grown, but users haven’t

Business users have always worked with data.

For many years, Excel was the primary tool for analysis, and it worked well as long as datasets were relatively small.

Over time, data volumes have grown dramatically. Today, meaningful analytical work often requires knowledge of SQL, Python, and modern data warehouses. These technologies are far beyond the skill set of most business users.

As a result, companies are forced to involve data engineers, analysts, or BI specialists even for simple analytical questions. This increases cost, slows down decision-making, and creates a gap between business questions and answers.

Why Excel is still alive

Despite countless predictions, Excel has not disappeared.

The reason is simple: Excel is intuitive. Pivot tables allow users to explore data freely — choosing metrics, slicing by dimensions, drilling down, and rearranging reports on the fly.

No BI tool has fully replicated this combination of flexibility, speed, and familiarity for a broad audience.

Why BI tools did not replace Excel

Modern BI tools such as Power BI, Looker Studio, or DataLens are powerful and well-designed. However, they have not become a true replacement for Excel in everyday analytical work.

In practice:

reports are often predefined,
ad-hoc analysis is limited,
semantic models require maintenance by specialists,
users lose the feeling of direct interaction with data.

When business users want to explore data independently, they still turn to Excel.

OLAP was the right idea

Many years ago, Microsoft took an important step by introducing OLAP technology.

The idea was simple:

data processing happens on a server,
heavy computations are performed centrally,
users work with data through Excel as a client.

This allowed business users to work with large datasets using familiar Excel pivot tables, while all complexity remained on the server side. OLAP dramatically improved accessibility of analytics.

Why classic OLAP stopped evolving

Traditional OLAP systems rely on pre-calculated cubes and aggregations. While this approach worked well in the past, it does not align with modern data architectures.

At the same time, columnar databases and cloud data warehouses became dominant:

ClickHouse
BigQuery
Snowflake
Redshift

These systems are extremely fast and scalable, but working with them still requires SQL, Python, or BI tools.

As a result:

OLAP provides the right user experience but is technologically outdated,
modern data warehouses are powerful but inaccessible to most users.

The idea behind XLTable

XLTable was created to bridge this gap.

Our goal is simple:

Give users the same experience they had with OLAP in Excel, but on top of modern columnar data warehouses.

Users should be able to:

open Excel,
connect to data,
build pivot tables,
work with measures and dimensions,

without learning SQL or Python.

A short overview of OLAP principles

OLAP is based on a multidimensional data model.

Conceptually, data is represented as a multidimensional cube:

Dimensions define axes (time, products, customers, regions)
Measures are stored in cube cells (revenue, quantity, average price)

When a user asks a question like “revenue by year”, they select:

one measure (revenue),
one dimension (year).

Excel pivot tables act as a client for OLAP systems, allowing users to arrange dimensions in rows and columns, apply filters, sorting, and drill-down operations.

OLAP vs columnar databases

Classic OLAP systems:

load data into cubes,
pre-calculate aggregations,
store results for fast access.

Columnar databases work differently:

data is stored in raw, column-oriented form,
aggregations are calculated on the fly,
performance is achieved through compression, parallelism, and query optimization.

Modern columnar systems are designed to compute analytical queries efficiently without pre-aggregation.

Shifting computation to the database

XLTable follows a simple principle:

All heavy computation should happen inside the database.

Instead of pre-calculating cubes, XLTable delegates aggregations and grouping to the underlying columnar database, using it exactly for what it was designed for.

XLTable acts as a semantic and protocol layer, not as a compute engine.

Open OLAP standards: XMLA and MDX

OLAP is an open technology.

Besides Microsoft, OLAP servers have been implemented by multiple vendors such as Oracle and Hyperion. The ecosystem relies on two key standards:

XMLA (XML for Analysis) — a protocol used by clients (such as Excel) to communicate with OLAP servers
MDX — a SQL-like query language for multidimensional data

When Excel works with an OLAP server, it sends XMLA requests containing embedded MDX queries.

Microsoft published the XMLA specification, making it possible to implement compatible OLAP servers.

What XLTable actually is

XLTable is a custom OLAP-compatible server that:

accepts XMLA requests from Excel over HTTP,
translates MDX queries into SQL,
executes them on modern data warehouses,
returns results back to Excel in XMLA format.

From Excel’s perspective, XLTable behaves like a classic OLAP server.

From the database’s perspective, XLTable is a client that generates optimized SQL.

Defining the semantic model

Any OLAP system requires a semantic model:

measures,
dimensions,
and mappings to source tables.

In Microsoft OLAP, this model is defined using graphical tools in Visual Studio.

XLTable takes a different approach. The semantic model is defined using SQL-based configuration scripts.

This approach is:

simpler and more transparent,
easier to version and maintain,
explicit in terms of generated SQL,
important for performance and cost control, especially in systems like BigQuery.

Once the model is defined, users simply connect to it from Excel.

Key features of XLTable

Fully proprietary, in-house development
All data processing happens inside the database (e.g. Snowflake)
Support for multiple measure groups, dimensions, and hierarchies in a single cube
Flexible caching strategies
LDAP / Active Directory integration
Fine-grained access control:
- by measures,
- by dimensions,
- by dimension members

Performance and scalability

XLTable does not perform aggregation or grouping itself
It receives MDX from Excel, translates it to SQL, and executes it in the database
This process requires minimal server resources
Performance depends entirely on database configuration, which aligns with columnar database design
Results can be cached at user or server level

There are no artificial limits on the number of dimensions or measure groups.

A typical production configuration includes:

~30 source tables
total data volume around 2 TB
individual tables with up to 2 billion rows

What’s next

In the next article, we will walk through:

installation,
configuration,
and the first connection from Excel to XLTable.

Connect Snowflake to Excel Pivot Tables with XLTable

XLTable — Sat, 03 Jan 2026 09:57:26 +0000

How to Connect Snowflake to Excel Pivot Tables (Without BI Tools or Data Exports)

Excel is still the most popular analytics tool for business users.

Finance teams, sales managers, and executives rely on Excel Pivot Tables because they are fast, flexible, and familiar.

At the same time, modern companies store analytical data in Snowflake — a cloud data platform built for large-scale analytics.

Yet connecting Snowflake to Excel Pivot Tables is still surprisingly difficult.

Most teams rely on CSV exports, custom SQL queries, or heavy BI tools — all of which limit true self-service analytics.

In this article, we’ll explain how to connect Snowflake to Excel properly, why exports don’t scale, and why OLAP is the missing layer.

The Snowflake + Excel Gap

Snowflake is excellent at:

large-scale analytics
elastic compute
centralized data storage
governance and security

Excel is excellent at:

ad-hoc analysis
Pivot Tables
business exploration
fast decision making

But in many companies, these tools don’t work together smoothly.

A typical workflow today

Analyst writes SQL in Snowflake
Exports data to CSV or Excel
Sends files to business users
Filters change, numbers drift
Trust is lost

This workflow does not scale.

Why CSV Exports from Snowflake Do Not Scale

Exporting data from Snowflake may look simple, but it creates serious problems:

No live data
Manual refreshes
Multiple versions of truth
Broken security model
No semantic layer

Most importantly, business users don’t want raw tables.

They want Pivot Tables.

Why BI Tools Are Often Too Heavy

Power BI, Tableau, Looker, and similar tools solve some problems — but create others:

per-user licensing costs
additional infrastructure
dashboards instead of exploration
another UI to learn

Many companies discover a frustrating reality:

After dashboards are built, users still export data to Excel.

What Excel Pivot Tables Actually Need

Excel Pivot Tables do not work well with raw SQL tables.

They expect:

dimensions and measures
hierarchies
aggregations
metadata
a semantic layer

This is exactly what OLAP provides.

OLAP as the Missing Layer for Snowflake

OLAP sits between Snowflake and Excel, translating analytical data into a structure Excel understands.

Architecture: Snowflake → OLAP → Excel

How XLTable Fits Into This Architecture

This Snowflake → OLAP → Excel architecture is exactly what XLTable is built for.

XLTable is an OLAP server designed to sit between Snowflake and Excel, providing a semantic layer that Excel Pivot Tables can work with natively.

Instead of exporting data or building dashboards, XLTable allows business users to connect directly to Snowflake using familiar Excel Pivot Tables — while all data, logic, and governance remain in Snowflake.

Key principles behind XLTable

Snowflake stays the single source of truth
Business logic is defined once, centrally
Excel users work with Pivot Tables, not SQL
No CSV exports or data duplication

What XLTable Provides

XLTable implements this architecture as a production-ready OLAP layer:

OLAP cubes on top of Snowflake
Native Excel Pivot Table connectivity via XMLA
Centralized definitions for measures and dimensions
Secure, read-only access to analytical data
Scalable query generation optimized for Snowflake

From the Excel user’s perspective, nothing changes — they simply connect to a cube and start building Pivot Tables.

From the data team’s perspective, calculations, access rules, and performance are finally under control.

Typical Use Cases for XLTable

XLTable is commonly used when:

Finance teams need live P&L or revenue analysis in Excel
Sales teams analyze performance by region, product, or customer
Operations teams explore large datasets without waiting for dashboards
Data teams want to reduce BI license and maintenance costs

In all cases, Snowflake remains the backend — Excel becomes the interface.

Excel Is Still a BI Tool — When Connected Correctly

Excel is not outdated.

Disconnected Excel is.

When Excel works directly with Snowflake through an OLAP layer, it becomes a powerful, scalable analytics interface.

Summary

CSV exports from Snowflake don’t scale
BI tools are often heavier than needed
Excel Pivot Tables require a semantic layer
OLAP bridges Snowflake and Excel
XLTable provides this missing layer

See Snowflake Analytics in Excel with XLTable

If your company uses Snowflake and Excel is still the primary analytics tool for business users, XLTable provides the missing connection between them.

You don’t replace Snowflake.

You don’t replace Excel.

You simply connect them correctly.