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.

    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

  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.

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 compatible with Excel, Power BI Desktop, and any other OLAP client that supports the MSOLAP provider.

Refreshing data

Pivot Table data is refreshed on demand:

  • Right-click the Pivot Table → Refresh

  • Or use Data → Refresh All

XLTable will execute SQL queries against the database for each refresh. Query result caching reduces database load for repeated requests.

Troubleshooting connection issues

See the Support page for common connection problems and solutions.