Pivot Table Issues

Fix Excel PivotTable Connected to OLAP Cube Stops Working After Reopening

Algirdas JasaitisAlgirdas Jasaitis Nov 10, 2026 869 views

Question details

The user needs to restore functionality to a PivotTable connected to an OLAP database that breaks or refuses to refresh upon reopening the saved file.

How to Fix an Excel PivotTable Connected to an OLAP Cube That Stops Working After Reopening
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user creates and saves an Excel workbook containing a PivotTable linked to an OLAP cube. When the workbook is closed and reopened later, the connection drops, preventing the PivotTable from refreshing or displaying data.
Observed behavior
The OLAP-connected PivotTable becomes unusable and triggers refresh failures or connection errors when the Excel file is reopened.
Before you start

Ensure your device is connected to the appropriate company network or VPN required to reach the OLAP server, as offline environments will naturally block the data connection.

Solution 1Recommended

Update OLAP Connection Properties and Credentials

Configure the workbook's data connection properties to ensure Excel saves and passes the correct authentication credentials to the OLAP cube upon reopening.

By default, Excel may not retain certain database credentials for security reasons. If the connection requires explicit authentication, the PivotTable will fail to refresh until the correct user name and password are provided in the connection definition.

1
Open Workbook Connections

Open the affected Excel workbook. Navigate to the Data tab located on the top ribbon menu and click on the Connections button.

2
Access OLAP Properties

In the Workbook Connections dialog box, locate and select your specific OLAP connection from the list, then click the Properties button.

3
Modify Definition Settings

Switch to the Definition tab within the Connection Properties window. Review the connection string and authentication parameters.

4
Enter Credentials

If your database setup requires it, check the box for 'Use the following User Name and Password'. Enter your valid database credentials and click OK to save the configuration.

Update OLAP Connection Properties and Credentials
Test the Connection: After saving the properties, click 'Refresh All' on the Data tab to verify that the PivotTable successfully pulls the latest data from the OLAP cube.
Free Microsoft Office alternative

Experience Seamless Data Analysis with WPS Office

If complex Microsoft Excel database connections and persistent OLAP errors are slowing down your productivity, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment for seamless daily reporting without the heavy overhead.

  1. 1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheets and seamlessly open your existing .xlsx or .xls files.
  3. 3. Analyze Data Instantly: Use WPS Office's intuitive PivotTable and charting tools to analyze your offline datasets without complex database configuration errors.
Completely free, lightweight, and fast-loading Office suiteFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Robust built-in PivotTable features for local data aggregationFamiliar user interface requiring zero learning curve for Excel users
microsoft office alternative - wps office

Frequently Asked Questions

Why does my OLAP connection drop every time I close the Excel file?

This typically occurs because the 'Save Password' option is disabled in the connection properties for security reasons. Every time the file is opened, Excel drops the connection until new credentials are provided or Single Sign-On authenticates.

Can I refresh an OLAP PivotTable without an active network connection?

No, OLAP PivotTables require a live connection to the database server. If you need to work offline, you must convert the PivotTable data to an Offline Cube file (.cub) while you are still connected to the network.

What should I do if the 'Use the following User Name and Password' option is grayed out?

If this option is disabled, your organization's IT policy likely forces Windows Authentication for database connections. You will need to rely on your active Windows domain login to authenticate, or contact IT to adjust your Active Directory permissions.