DEV Community: XLTable

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.