DEV Community: XLTable

A Semantic Layer That Reads as SQL

XLTable — Sun, 19 Jul 2026 08:12:49 +0000

A Semantic Layer That Reads as SQL

Why a cube definition should be an executable query — not a YAML description of one. And what AI has to do with it.

Ten versions of the truth

Every sufficiently large company has lived through the same conversation. The CFO asks, "What was our revenue this quarter?" — and gets three different answers from three different analysts. Not because anyone made a mistake. One counted revenue with VAT, another without, a third excluded returns but included intercompany sales.

The data, meanwhile, sits in one place — ClickHouse, BigQuery, Snowflake, or another analytical warehouse. The problem isn't the data. The problem is that the definition of a metric — what "revenue" means, how sales relate to stores, who is allowed to see which regions — doesn't live in one place. It lives in analysts' heads and in hundreds of scattered SQL queries.

And business users end up in Excel anyway. You rolled out dashboards, bought licenses, ran trainings, built self-service portals — and still, every report ends its life as an Excel export on someone's desktop. That's not a failure of your team; it's how people actually work. Meanwhile, between Excel and the warehouse grows a pipeline of CSV exports and "intermediate" files, each one another opportunity for the numbers to drift apart.

This is exactly the problem a semantic layer solves: define your metrics once, under IT control with fine-grained access — then let thousands of users explore them and get the same numbers. Excel becomes the front end. Your warehouse stays the single source of truth.

A beautiful idea. So why do so many semantic layer projects stall at the pilot stage?

Why data teams struggle with semantic layers

Ask a data engineer what a semantic layer is, and the answer will depend on whose documentation they read last. The term is overloaded: metrics layer, headless BI, semantic layer, universal semantic layer — every vendor defines it differently and draws its own architecture diagram. A team reads three articles and comes away with three different pictures of the world.

Then it turns out that a semantic layer is, as a rule, a separate system with its own lifecycle. Its own modeling language to learn. Its own compiler to run. Its own deployment process, its own versions, its own breaking changes. The "warehouse + orchestrator + BI" stack gains yet another moving part that has to be studied, upgraded, and fixed.

The cost of entry before the first result turns out to be high. Before the business sees a single number, someone has to design the model, absorb a new vocabulary — semantic models, entities, measures, metrics, with metrics further split into types: simple, ratio, derived, cumulative — wire up integrations, and explain to everyone why any of this was necessary. That's weeks of work before the first "wow," and an organization's budget of patience is not unlimited.

Finally, the layer often has no obvious owner. Analysts consider it infrastructure ("that's for the engineers"), engineers consider it analytics ("that's about business metrics"). The layer hangs between teams, and once the enthusiasm of the first sprint wears off, nobody is left to maintain it.

None of this says the idea of a semantic layer is bad. All of it says one thing: the cost of adoption of a typical implementation is too high. Let's see where that cost comes from.

How it's done today: YAML on top of SQL

The dominant approach in modern semantic layers is declarative metadata. The model is described in YAML files: here are the entities, here are the dimensions, here are the measures, here are the metrics built on top of the measures. A dedicated engine reads these files and generates SQL at query time.

It looks roughly like this. Suppose we need one single metric — the number of units sold:

semantic_models:
  - name: sales
    description: "One row per sale"
    model: ref('fct_sales')
    defaults:
      agg_time_dimension: sale_date
    entities:
      - name: sale
        type: primary
        expr: sale_id
      - name: store
        type: foreign
        expr: store_id
    dimensions:
      - name: sale_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: qty
        agg: sum

metrics:
  - name: sales_quantity
    label: Sales Quantity
    type: simple
    type_params:
      measure: qty

Almost thirty lines, two sections, four levels of nesting — and notice: the measure qty is not yet a metric by itself. For the user to see a number, a metric of type simple must be declared on top of the measure — one that "simply uses the measure as is."

This approach has real strengths, and they deserve an honest mention. YAML is machine-readable — convenient for building APIs and code generation on top of. A declarative model isn't tied to any particular SQL dialect. The files live happily in git. All true.

But there is a price, and here it is.

YAML cannot be executed. It is a description of a query, not a query. The debugging loop looks like this: edit the YAML → run the compiler → the engine generates SQL → execute it → get an error in the generated SQL → mentally translate it back to lines of YAML that don't exist in that SQL. Every iteration passes through a layer of indirection. The engineer debugs something other than what they wrote.

The SQL never went away — it just moved into string literals. As soon as an expression gets any more complex than a sum over a column, an expr field appears in the YAML with a chunk of SQL inside: expr: case when status = 'closed' then amount end. You get the worst of both worlds: expressions are still written in SQL, but now they live inside YAML strings — no syntax highlighting, no validation, with escaping rules and multi-line blocks via |. Some systems add a templating engine on top — and now you have Jinja inside YAML inside SQL.

Every tool is a new system of concepts. Entities with types primary/foreign/unique/natural, dimensions with type_params, metrics of four kinds — each vendor has its own ontology, and none of it transfers between tools. Unlike SQL, which every person on a data team already knows.

Join semantics are declared abstractly. You write type: foreign — but which JOIN the engine will actually build, and which path it will take through the model graph, only becomes visible after compilation. When the engine picks an unexpected join path, debugging turns into a study of the generator's internals.

A definition can only be validated through the toolchain. Validating a model means running the vendor's compiler with its own error messages. Without the tool installed and configured, a YAML file is just text.

Add it all up and you get exactly that high cost of adoption from the previous section. It isn't accidental — it's built into the architecture of the approach: between the engineer and their data stands a layer of abstraction that must be learned before it starts to help.

Before building XLTable, we spent years implementing and operating Microsoft Analysis Services in large retail companies — watching the business live in PivotTables while IT lived in cube definitions. XLTable grew out of that experience, not out of a desire to invent a new format. And so we took a different path.

The XLTable principle: the executable SELECT

XLTable is an XMLA-compatible OLAP semantic layer for ClickHouse, Snowflake, BigQuery, Databricks, StarRocks, Trino, Greenplum, and DuckDB. Excel sees it as a familiar OLAP cube: MDX queries, PivotTables, hierarchies — everything works just as it does with Microsoft Analysis Services (for which XLTable is a drop-in replacement). What reaches the database is already optimized SQL. No SQL for end users, no data copies. The semantic layer here is the cube definition: measures, dimensions, hierarchies, relationships, and permissions.

And that cube is defined not in YAML and not in a graphical designer, but in SQL. Here is the equivalent of those thirty lines of YAML from the previous section:

--olap_source Sales
SELECT
--olap_measures
    sum(sales.qty) as sales_sum_qty --translation=`Sales Quantity` --format=`#,##0`
FROM db.Sales sales

Five lines. But the key property isn't brevity — it's this: this is a working query. Mentally strip the comments and you're left with an ordinary SELECT that can be copied into any database client, executed, and it returns real rows. The tags in the comments (--olap_source, --olap_measures, --translation) merely tell XLTable how to assemble such queries into a cube.

We call this the executable SELECT principle, and it is not a syntactic quirk — it is the foundation of the whole system. Several things follow from it:

Debugging is execution. "Does the model compile?" is not a question that exists. The question is "does the query run" — and the database itself answers it, with an ordinary SQL error, in the dialect you write every day.

The barrier to entry is knowing SELECT. There is no ontology of entities and metric types to absorb. If an engineer can write SELECT ... FROM ... LEFT JOIN, they can define cubes. A first cube is realistically an evening's work, and the full path from installing the server to the first PivotTable in Excel takes about an hour.

Reviews work like regular code reviews. The diff of a definition reads at a glance; the reviewer sees actual SQL, not a description of SQL.

No hidden magic. The queries XLTable sends to the database are exactly the ones written in the definition, plus aggregation over the fields the user selected. Turn on logging — and you see every generated query.

A fair objection at this point: aren't the comment tags themselves a DSL? They are — any convention is. The difference is where that DSL lives. Strip every tag from an XLTable definition and the host language still executes: what remains is a valid SELECT. The surface area is a dozen tags, not an ontology of entities and four kinds of metrics. And validation doesn't depend on a proprietary compiler — the database checks the query, and XLTable checks the tags.

Anatomy of a cube

Let's walk through what a definition consists of — with examples, each of which, remember, can be executed as is.

Measures are the numbers the business wants to count. A measure is an aggregate function, an alias, and optional display tags:

--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 db.Sales sales
LEFT JOIN db.Stores stores ON sales.store_id = stores.id

The translation tag sets the name the user sees in the Excel field list; format sets the numeric format in the PivotTable. Everything sits on the same line as the measure itself — the definition and its metadata never drift apart into separate files.

Dimensions are the context of analysis: stores, regions, products, time. Syntactically they mirror measure groups:

--olap_source Stores
SELECT
--olap_dimensions
 stores.id as stores_id
,stores.store_name as stores_name --translation=`Store`
FROM db.Stores stores

Hierarchies are parent-child relationships between attributes, so a user can drill from year to day in one click. Four tags — and the hierarchy is done:

 times.year    as times_year    --hierarchy=`Dates`
,times.quarter as times_quarter --hierarchy=`Dates`
,times.month   as times_month   --hierarchy=`Dates`
,times.day     as times_day     --hierarchy=`Dates`

Relationships between measures and dimensions are not described with abstract types — they are written as ordinary, explicit LEFT JOINs. A dimension links to a measure group through a shared table alias: if the measure group contains LEFT JOIN db.Stores stores and the dimension's source is FROM db.Stores stores, XLTable connects them on that alias. The JOIN is visible to the naked eye — what is written is what runs.

Special cases get clarifying tags. The most popular one in the ClickHouse world is one-table, for denormalized tables where measures and dimensions live in one wide table and no real JOIN is needed. There is many-to-many for classic many-to-many relationships (several managers assigned to one store) and part-source for helper lookup tables that are needed inside a single source but should not become part of the cube.

Calculated fields are virtual measures built on top of other measures — including measures from different measure groups:

--olap_cube
--olap_calculated_fields Calculated fields
(sales_sum_qty / nullIf(stock_avg_qty, 0)) as turnover --translation=`Turnover`

Here inventory turnover is computed from sales and average stock — two different fact tables. XLTable merges the per-group results with a FULL JOIN on its own; all the expression has to do is guard against division by zero.

Security — in the same file, in the same syntax

In a typical architecture, access rights live apart from the data model — in an admin panel, in separate configs, in the BI tool. In XLTable, a role is just another block of the same definition:

--olap_user_role
--olap_user_groups
finance_users
--olap_measures_visible
sales_sum_sum
--olap_dimensions_visible
Stores, Regions
--olap_access_filters
region in (`EU`, `NA`)

A role defines which measures and dimensions a user group can see, and olap_access_filters sets row-level security: the finance users in this example physically receive only rows for the EU and NA regions — the filter is baked into every SQL query the server generates for them. Groups come from Active Directory, so access is granted to directory roles rather than individuals — as it should be in the enterprise.

Crucially, all of this is part of the same text definition. Permissions go through the same code review and live in the same git history as the model itself.

When static isn't enough: Jinja

Honesty requires an admission: we have templating too. Cube definitions support Jinja — for the cases where SQL has to change dynamically: relative dates ("last 30 days" from the current moment), conditional logic, swapping a table for a pre-computed aggregate for performance.

The difference from the YAML approaches lies in the role the templating engine plays. There, the declarative layer is mandatory for every metric; here, Jinja is an optional add-on for the five percent of cases where plain SQL falls short. Our own documentation puts the recommendation plainly: use Jinja for small SQL adjustments and avoid complex logic. The core of the cube remains an executable SELECT.

What the business gets

The user-facing side of the story fits in one sentence: business users stay in Excel, and there is nothing to retrain them on. No add-ins, no plugins, no "new, more convenient interface." A native PivotTable connects to XLTable the same way it has connected to Analysis Services for twenty years — while the data lives in ClickHouse, billions of rows deep.

Two details users appreciate most.

Drillthrough. Double-click any cell in the PivotTable — and Excel shows the underlying rows that produced that number: the individual receipts, dates, stores. All of the cell's filters and slicers apply, including row-level security — a user sees only the detail rows they are entitled to. For trust in the numbers, this beats any written policy: every figure can be opened up down to the raw records.

Speed. Query results are cached, so typical PivotTable work — rearranging fields, filtering, slicing — feels instant. The heavy lifting is done by the database itself: XLTable never copies data to its side; it translates Excel's MDX into optimized SQL. A read-only service account is all the server needs.

What IT gets

The engineering side of the story is about transparency and control.

Semantic layer as code. Cube definitions are plain SQL files. They live in Git, changes are reviewed in pull requests and deployed through the CI/CD flow your team already uses. No black-box UI with a graphical designer — the semantic layer lives in the repo.

Everything is visible. The WRITE_LOG setting turns on logging of every SQL query the server generates for a user's specific field selection. An unexpected result or a slow query stops being a mystery: open the log and read the actual SQL.

Broken definitions never reach users. The definition_check_on tag enables mandatory validation of the definition before connecting to data: if the definition contains an error, the connection is refused and an error is returned — users never see a half-working cube.

The practical workflow: run each block of the definition by hand in a database client (every block is an executable SELECT), enable definition checking, review the final queries in the log. Three steps, zero new tools.

Light footprint. XLTable is a single service in front of your warehouse — no cube storage layer, no heavy infrastructure to babysit. It goes live in a day, not a six-month rollout — a direct answer to the "cost of adoption" from the beginning of this article.

And finally, the perimeter: your data never leaves. Most managed semantic layer offerings are cloud services: metadata, queries, and results pass through someone else's infrastructure. For a bank, a retailer handling personal data, or the public sector, that is often not a "downside" but a hard stop — compliance and data-residency requirements are not negotiable. XLTable pushes queries down to your warehouse — no extracts, no in-memory copies, no data shipped to anyone's cloud. It deploys on-premises, up to and including fully air-gapped environments.

The semantic layer in the age of AI

So far we've talked about two readers of a cube definition: the database that executes it and the human who writes and reviews it. In the last couple of years a third reader has appeared — the large language model. And for that reader, the executable SELECT principle turned out to matter more than we could have planned.

AI writes cube definitions — because they are just SQL. Language models are trained on colossal amounts of SQL; for them, SQL is close to a native tongue. Ask an assistant to write an XLTable definition block and it will manage, because it's an ordinary SELECT with comments. Ask it to write a model in a proprietary YAML dialect and it will start hallucinating fields of a schema it saw a thousand times less often in training. A proprietary DSL is a barrier not only for humans, but for models too.

The feedback loop works as well: the AI writes a block → the block is executed in the database → the error comes back as an ordinary SQL error → the AI understands it and fixes it. The loop closes using standard tools. With a YAML layer the loop breaks: the error comes from a vendor's compiler whose quirks the model somehow has to know.

XLTable takes this a step further: it can generate a cube definition from your tables automatically — from raw schema to a working model in minutes — which you then refine in Git like any other code. And for hands-on work there's a practical trick: hand the assistant the entire XLTable documentation as a single file, and you get a genuine pair-designer for your cubes.

AI reads data through the semantic layer. XLTable supports MCP (Model Context Protocol) — the open standard for connecting AI assistants to data. The assistant sees the same cube the Excel user sees: the same measures with human-readable names, the same dimensions, the same hierarchies. Instead of dragging fields with a mouse — a question in plain language: "show me sales by region for the last quarter versus a year ago."

And because both interfaces read the same semantic layer, the numbers always agree: ask the chat for Q4 margin and you get exactly the figure your PivotTable shows. One model, two ways to explore, one number in the answer.

This is where the semantic layer solves the central problem of the text-to-SQL approach. When a model writes SQL against a raw database schema, it is forced to guess: which column is revenue with VAT and which without, how to join facts to reference tables, what amt_2 means in table t_sales_f. Every guess is a potentially confident wrong number. The semantic layer removes the guessing: metrics are already defined and named, relationships are already spelled out. The AI picks from a ready menu instead of inventing a recipe.

And — the decisive point for the enterprise — security does not break. The assistant goes through the same layer Excel does: the same roles, the same row-level security, the same read-only database access. The AI physically cannot show a user data they are not entitled to. And since XLTable lives inside your perimeter, the entire AI scenario runs without data leaving the boundary — even the AI interface can run on locally hosted models, no cloud APIs required.

One file, three readers

Let's sum up. A semantic layer does not have to be a separate platform with its own modeling language, compiler, and learning curve. It can be a SQL file — one that can be read, executed, and version-controlled.

A cube definition in XLTable is equally legible to three very different readers. The database — because every block runs as is. The human — because it's SQL the data team already knows, with no new system of concepts. The AI — because a model can both write such definitions fluently and query data through them, losing neither the semantics nor the access controls.

And business users never see any of these layers at all. They open Excel, refresh the PivotTable, and get numbers that agree.

Don't take our word for any of this. Take a sample dataset, define the same metric both ways, and see which one you'd rather debug, review, and hand to an AI.

You can try it in about an hour: the Quickstart walks you from installing the server to your first PivotTable, and ready-made sample datasets are available for all eight supported databases — from ClickHouse to DuckDB. Documentation: xltable-olap.readthedocs.io. Questions: help@xltable.com.

Excel Is Still the #1 Self-Service Analytics Tool. Here's Why That's Not a Problem Anymore.

XLTable — Tue, 09 Jun 2026 14:12:52 +0000

Excel Is Still the #1 Self-Service Analytics Tool. Here's Why That's Not a Problem Anymore.

Your company just renewed its Tableau license. Your analysts just exported the data to Excel.

This scene plays out in nearly every enterprise analytics department in the world. And yet, IT leaders keep funding BI platforms, training programs, and data governance initiatives — while business users quietly keep opening spreadsheets.

This isn't a failure. It's a signal. And understanding what it means is the key to building an analytics stack that people actually use.

What Self-Service Analytics Actually Means

Self-service analytics refers to the ability of business users — not data engineers or BI developers — to independently access, explore, and analyze data without submitting a ticket to IT.

The market has taken notice. Analysts estimate the global self-service analytics market was valued at roughly $5–6 billion in 2024, growing at a CAGR of ~16% toward a potential $17–27 billion by the early 2030s. The growth is real. But it masks a more nuanced picture on the ground.

For CTOs and IT directors, the promise of self-service is compelling: fewer bottlenecks, faster decisions, reduced dependency on data teams. The challenge is delivering on that promise without creating a mess of shadow reports and disconnected data sources.

The Landscape: A Quick Map of the Tools

There are five broad categories of tools that compete — and coexist — in the self-service space:

Spreadsheets (Excel, Google Sheets) — The original self-service layer. Every business user already knows them.
Modern BI platforms (Power BI, Tableau, Looker) — Visualization-first tools with strong governance features and growing AI capabilities.
Embedded analytics (Metabase, Redash) — Lightweight, often open-source, embedded inside applications.
Headless BI / semantic layer (dbt Metrics, Cube.dev) — Code-first approaches to defining business logic independently of visualization.
OLAP servers (Microsoft Analysis Services, XLTable) — Traditional and modern cube-based engines that sit between databases and analytics clients.

Each category has genuine strengths. The problem is that organizations often treat them as competitors when they are more naturally complements.

The Excel Paradox

Let's be direct: Excel is not going away. Over 200 million enterprise users are licensed on Microsoft 365 globally. More than 1.3 million companies in the US alone use it. It is the most widely declared skill on LinkedIn among corporate professionals, ranging from 17% to 32% penetration depending on job function.

And yet, the standard narrative in enterprise IT is that Excel is a legacy tool — a crutch for users who haven't been properly trained on "real" analytics platforms.

This framing is wrong, and it's causing expensive mistakes.

Executives don't use Tableau to build their Monday morning model. They use Excel. That's not a training problem. That's a product-market fit signal.

Excel's persistence isn't about inertia. It's about control. A pivot table in Excel is something a finance director can manipulate in real time, in a meeting, with no dependency on a dashboard developer. The cognitive overhead is near zero. The flexibility is near infinite.

The actual problem with Excel isn't Excel. It's the data feeding it.

The Real Bottleneck: Data Access, Not the Spreadsheet

When analysts export from Snowflake or ClickHouse into a CSV and then import it into Excel, two things go wrong:

The data is stale the moment it lands in the file.
The analyst becomes a manual ETL pipeline — a job they weren't hired to do.

This is where the architecture conversation needs to shift. The question isn't "How do we get users off Excel?" It's "How do we give Excel a live, governed connection to real data?"

The answer exists and has for decades — it's called XMLA. It's the protocol that Microsoft built for exactly this scenario: connecting Excel PivotTables to analytical databases through a standardized interface. Analysis Services used it. Power BI uses it. And modern OLAP servers can expose it to any backend — ClickHouse, Snowflake, BigQuery, StarRocks, Databricks.

When you connect Excel to a properly modeled OLAP cube, several things change simultaneously:

Users get live data, not exports.
Business logic (metrics, hierarchies, access rules) lives in one place — the semantic layer.
Finance stays in Excel. IT controls the data.
No SQL required for end users. No BI license required per seat.

What CTOs Should Actually Be Asking

Most analytics infrastructure reviews focus on the wrong question: "Which BI tool should we standardize on?" A better set of questions:

Where does data actually get consumed? Follow the spreadsheets, not the dashboards.
What is the total cost of the export-import loop? Count analyst hours, not just license fees.
Do we have a semantic layer? If business logic lives in reports rather than in a governed model, you have a governance problem regardless of which tool you use.
Can our stack serve both technical and non-technical users from the same source of truth? If the answer is "no," you are maintaining two parallel data cultures.
What is the adoption rate of our BI investments? If it's below 30%, the tool is not the answer.

A modern analytics stack doesn't have to choose between Excel and BI. It can serve both — provided the data layer is properly architected.

The Modern Answer: Embrace Excel, Fix the Data Layer

Here is a practical architecture that resolves the Excel paradox without forcing users to abandon tools they trust:

Build a semantic layer on top of your analytical database. Define measures, dimensions, hierarchies, and access rules once.
Expose that layer via XMLA so Excel connects natively — no plugins, no extensions.
Keep BI tools for dashboards, alerts, and shared views. They're excellent at that.
Let Excel do what Excel does: ad-hoc analysis, financial modeling, executive reporting.

The result is a stack where data governance and user freedom aren't in tension. IT owns the model. Business owns the analysis.

What About AI Chat? ChatGPT, Claude, and the "Just Ask" Trend

There's a growing narrative that AI chat interfaces will replace both Excel and BI tools entirely. Users are already asking ChatGPT for dashboards and getting back static charts. The question CTOs are asking: is this the end of structured analytics?

The honest answer is: AI chat and Excel are not competing for the same job.

AI chat interfaces excel at one thing — answering a one-off question quickly. "What was our best-performing region last quarter?" works well in a chat window. But the moment you need that answer to be repeatable, auditable, consistent across departments, and connected to data that updates daily — chat alone falls short.

Here's why:

Data is not live. Users upload a CSV from yesterday. Tomorrow they upload a new one. The export-import loop moves from email to chat — but it's still a loop.
No single source of truth. Every employee gets their own answer from their own data. Finance and Sales produce different numbers for the same metric.
No audit trail. A CFO cannot sign off on a report generated by a language model without traceability.
Security. Most enterprise data governance policies prohibit sending sensitive business data to external AI providers.

AI chat is an interface for one-off questions. Excel and BI are the environment for repeatable, governed, auditable decisions. They are not competitors — they are built for different jobs.

But here is where the picture gets more interesting — and where the real opportunity lies.

When an AI assistant connects directly to a live semantic layer via a protocol like MCP (Model Context Protocol), the equation changes entirely. Instead of answering from a stale uploaded file, an AI can query a live OLAP cube and return current, governed data — directly in the chat interface.

This means the user gets the conversational experience they want, with the data integrity the organization requires. The AI becomes a natural language interface to the same semantic layer that powers Excel PivotTables and BI dashboards — not a replacement for it.

For organizations building on a modern OLAP layer, this is not a threat. It is an additional channel — one that makes the investment in a proper semantic layer even more valuable.

Conclusion

Self-service analytics is not a tool. It's a capability. And the most effective organizations build it around how their people actually work — not around how vendors think they should work.

Excel is not the enemy of modern analytics. Ungoverned data is. Once you solve the data layer, the spreadsheet becomes a feature.

Stop trying to replace Excel. Start connecting it to something worth analyzing.

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.