Connecting Excel

XLTable exposes a standard XMLA endpoint, so connecting Excel to XLTable is identical to connecting to Microsoft SQL Server Analysis Services (SSAS).

Any Excel feature that works with SSAS works with XLTable — Pivot Tables, Power Query, slicers, named sets, MDX formulas and more.


Adding an Analysis Services data source

  1. Open Excel.

  2. Go to Data → Get Data → From Database → From Analysis Services (not the neighbouring …SQL Server Analysis Services Database (Import) item — that Power Query importer hangs on connect).

    In older Excel versions: Data → From Other Sources → From Analysis Services.

  3. In the Server name field, enter the XLTable server address: http://your_server_ip

    The server listens on port 5000 by default, so unless it is behind a proxy include the port in the address — for example http://127.0.0.1:5000 when Excel runs on the same machine as the server, or http://your_server_ip:5000 over the network.

    Note

    Prefer 127.0.0.1 or a real host name over localhost. On Windows, localhost resolves to IPv6 first; if the server only listens on IPv4, every request waits for the IPv6 attempt to time out, adding about 2 seconds of latency.

  4. In the Log on credentials section, select Use the following User Name and Password and enter the credentials configured in settings.json.

    If Active Directory integration is enabled, select Use Windows Authentication — Excel will use the current domain session credentials automatically.

  5. Click Next.

  6. Select the database and cube from the list.

  7. Click Finish.

Excel will create a new Pivot Table connected to XLTable.


Authentication modes

Basic authentication (username and password)

Used when USERS are defined in settings.json.

Select Use the following User Name and Password in the connection wizard and enter the credentials from the USERS section of settings.json.

Active Directory (Windows authentication)

Used when CREDENTIAL_ACTIVE_DIRECTORY is configured in settings.json.

Select Use Windows Authentication in the connection wizard. Excel will use the current domain session credentials automatically — no username or password needs to be entered manually.

Note

Workbooks created against XLTable 1.0 may use the address http://your_server_ip/ad. This path is kept as an alias of the root address, so existing connections keep working after an upgrade — no need to reconfigure them.


Connection string (advanced)

If you need to connect programmatically or configure the data source manually, use the following OLEDB connection string:

Provider=MSOLAP;Data Source=http://your_server_ip;Initial Catalog=;

Replace http://your_server_ip with the actual server address.

This connection string is used by Excel through the MSOLAP provider. XLTable currently targets Excel as its client application.


Refreshing data

Pivot Table data is refreshed on demand:

  • Right-click the Pivot Table → Refresh

  • Or use Data → Refresh All

XLTable executes SQL queries against the database on refresh, with two levels of caching that reduce database load: each session keeps its already-built responses, and SQL query results are shared between users, so repeated report views are served from the cache within milliseconds. How the cache is organized, its TTLs and the ways to clear it are described in Caching.

Pressing Refresh always gives the pressing user fresh data: cached results obtained before the refresh are bypassed for that user and, once re-read from the database, updated for everyone else as well. Users who do not press Refresh keep being served from the cache until its TTL expires.

Refresh also picks up changes to the cube definition itself — a new measure, dimension or translation appears in the PivotTable field list without closing and reopening the workbook. The server moves the cube version forward on Refresh, so Excel re-reads the cube structure at exactly that moment instead of relying on the copy cached inside the workbook.


Slicers

Slicers work the same way as with Analysis Services: select the Pivot Table, choose Insert → Slicer, pick a field, and click tiles to filter.

The slicer shows the field’s members in cube order, with members that have no data under the current measures greyed out at the end of the list. Role access filters apply: values hidden from the user by --olap_access_filters (including not in exclusions) never appear on slicer tiles. Slicers on hierarchy levels are supported too — a slicer on Quarter lists each quarter under its year. A slicer on a field that also sits in the report filter area stays in sync with it: picking a tile updates the filter, as in Analysis Services.

Note

Timelines (Insert → Timeline) are not supported yet: cube fields are served as text, so Excel reports that the PivotTable has no date fields. Use a slicer on the date hierarchy instead.


Drill through to detail rows

Double-click any value cell in a Pivot Table to drill through. Excel opens a new sheet listing the underlying detail rows behind that aggregated value — the individual records that were summed into the cell.

Excel itself blocks drill-through in a few cases, and the request never reaches the server: a subtotal cell while an axis field is filtered, and any cell while a report filter or slicer has more than one value selected. Drill into a specific value cell with a single-value filter context to see the detail rows.

The columns shown are configured per measure group in the cube definition with the olap_drillthrough tag (see Drillthrough). The cell’s row, column and slicer context is applied automatically as a filter, so you only see the rows that make up that specific cell. The number of rows is capped by the drillthrough limit Excel sends with the request.

Drill through is available on measures. Calculated fields cannot be drilled — double-clicking such a cell returns a message instead of data, because a calculated field has no single set of underlying rows.


Working with large results

A Pivot Table result is capped in size (100,000 cells in the shipped configuration, set by MAX_CELLS — the same way SSAS limits oversized results). When a layout is too detailed to fit, the detailed result can be previewed right in the pivot or exported in full to a CSV file — see Working with large results: preview and file export.


Filtering by selected items

Besides the field filter dropdown, Pivot Table items can be filtered directly from the selection: select one or more items in the Pivot Table, right-click and choose Filter → Keep Only Selected Items or Hide Selected Items.

Both commands work the same way as with SSAS, including items of multi-level hierarchies. Excel first asks the server for the hierarchy position of each selected item and then applies the resulting filter to the field.

Note

Items of a field come in the order the server returns them — alphabetical by text, the same as with Analysis Services — and Label Filters compare that text. Dates in ISO form and month names with a number sort naturally; see Member order for the cube-side recipes.


Expanding and collapsing fields

Nested Pivot Table fields can be expanded and collapsed the same way as with SSAS — per item with the + / − buttons, or for the whole field at once: right-click an item and choose Expand/Collapse → Collapse Entire Field or Expand Entire Field.

All combinations are supported, both for separate nested fields and for levels of a multi-level hierarchy: collapsing an entire field, expanding an entire field or hierarchy level (all items at once), expanding a single item of a collapsed field back (only that item shows the nested field), and collapsing single items of an expanded field. A collapsed field costs nothing on the database side — its table is not scanned or joined at all until the field is expanded again.

The Expand/Collapse → Expand To “<level name>” command is supported as well: a single item of a multi-level hierarchy can be expanded several levels deep in one click — for example, a year straight down to days.


Show Values As (additional calculations)

The Show Values As calculations of a value field — % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Rank Largest to Smallest, Difference From and the rest — are computed by Excel itself from the numbers already in the pivot. Nothing has to be added to the cube for them, and the server sees at most a repeated query of the same layout.

To show a measure both as a number and as a percentage, drag the same measure into Values a second time: Excel names the copy Sales Quantity2, and Show Values As is set on the copy. The server answers such a layout the way Analysis Services does — the measure comes back twice in the result, so both columns fill in.

Note

Difference From / % Difference From with the base item (previous) or (next) compare with the neighbouring item on the axis, not with the same period of the previous calendar year: if a year is missing from the data or filtered out, “previous” is the previous visible year. A calendar year-over-year comparison is a measure of its own in the cube — the sample cubes define Sales last year Quantity this way, the same measure with --offset (see Smart measures and ClickHouse Sample Data).


Troubleshooting connection issues

See the Support page for common connection problems and solutions.