Fix Excel PivotTable Connected to OLAP Cube Stops Working After Reopening
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.

- 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.
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.
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.
Open the affected Excel workbook. Navigate to the Data tab located on the top ribbon menu and click on the Connections button.
In the Workbook Connections dialog box, locate and select your specific OLAP connection from the list, then click the Properties button.
Switch to the Definition tab within the Connection Properties window. Review the connection string and authentication parameters.
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.

Verify User Account Database Permissions
Confirm that your user account has the necessary security roles to query the OLAP cube, as missing permissions will block the refresh process.
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. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and seamlessly open your existing .xlsx or .xls files.
- 3. Analyze Data Instantly: Use WPS Office's intuitive PivotTable and charting tools to analyze your offline datasets without complex database configuration errors.

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.




