This error occurs when Excel cannot complete a data refresh due to locking conflicts with the Analysis Services backend. The issue typically resolves on its own within minutes, but understanding the root causes helps you avoid repeat occurrences and optimize your PivotTable setup for better performance.
Error Message
"Server: The operation was cancelled because of locking conflicts. We couldn't get data from an external data source. Please try again later. If the problem persists, please contact the administrator for the external data source."
What Causes This Error?
This error occurs when Excel attempts to query the Azure Analysis Services (AAS) cube while the server is performing another operation that requires exclusive access. Understanding the timing helps explain why the error often resolves on its own.
Backend Processing
Scheduled cube refreshes (typically overnight or during specific windows) temporarily lock the database. If your query coincides with this processing, it gets cancelled.
PivotTable Complexity
Too many items in your Rows section creates massive queries that exceed Excel's memory limits or timeout thresholds before completion.
Commit Timeout
Analysis Services has a 30-second default timeout (ForceCommitTimeout). Long-running queries that exceed this window get forcibly cancelled.
Concurrent Users
Multiple users refreshing simultaneously can create resource contention. Heavy usage periods increase the likelihood of conflicts.
Quick Fixes (Try These First)
Most locking conflicts are temporary and resolve within minutes. Start with these immediate actions before diving into more involved troubleshooting.
Wait and Retry
Wait 10–15 minutes, then try refreshing again. Backend processing typically completes within this window, and conflicts resolve automatically.
Restart Excel
Close Excel completely (check Task Manager to ensure it's fully closed), then reopen your file. This clears cached connections and memory issues.
Check System Status
Verify that Catalyst data refresh has completed successfully. If a refresh is in progress or recently failed, wait for it to finish before retrying your Excel refresh.
PivotTable Optimization Strategies
If the error recurs frequently, your PivotTable structure may be requesting more data than Excel can efficiently handle. These optimizations reduce query size and improve refresh performance.
1. Reduce Items in Rows
Placing too many fields in the Rows section forces Excel to enumerate every unique combination—exponentially increasing the query size.
2. Use Filters to Limit Data
Apply report-level filters before adding fields to Rows. Set Scenario, Period, and Company filters first to constrain the data universe, then build your analysis within that filtered scope.
Effective filtering approaches include:
- Slicers: Add visual filters for common selections (Year, Scenario, Company)
- Page filters: Move high-cardinality dimensions to the Filters area
- Default selections: Set sensible defaults rather than "All"
3. Break Down Large PivotTables
Instead of one massive PivotTable trying to show everything, create multiple focused PivotTables:
| Instead of... | Create... |
|---|---|
| One PivotTable with all regions, all products, all periods | Separate tabs for each region or business unit |
| Summary and detail in the same view | Summary tab + Detail tab with drill-through |
| All historical data in one PivotTable | Current year tab + Archive tab for prior years |
4. Optimize Refresh Behavior
Refresh Best Practices
Enable this in PivotTable Options to make multiple changes before refreshing, reducing server round-trips.
Right-click and refresh specific PivotTables instead of using "Refresh All" to avoid concurrent query conflicts.
Schedule large refreshes outside typical processing windows (early morning, month-end close periods).
Additional Troubleshooting Steps
If optimizing your PivotTable doesn't resolve the issue, check these additional factors.
Check for Stale Filter Values
PivotTable filters that reference values no longer in the cube (deleted scenarios, renamed dimensions) can cause performance issues and refresh failures.
Common Scenario
After year-end, PivotTables filtered on a prior-year scenario (e.g., "2024A") that's been removed from the cube will exhibit slow performance or fail to refresh. Reselect valid filter values to resolve.
Verify Data Source Availability
- Confirm the Catalyst cube is online and accessible
- Check if the external data source is undergoing maintenance
- Verify no schema changes (table/column renames) have occurred
- Ensure your network connection is stable
Update Excel and Drivers
Outdated components can cause connection and performance issues:
- Update Excel: File → Account → Update Options → Update Now
- Update MSOLAP driver: Install the latest Analysis Services client libraries
- Clear cached credentials: Windows Credential Manager → Remove saved Analysis Services entries
Free Up System Resources
Large PivotTables connected to external cubes consume significant memory:
- Close other large Excel workbooks
- Close resource-intensive applications (browsers with many tabs, other Office apps)
- Consider using 64-bit Excel for workbooks with large data volumes
- If memory issues persist, Power BI may be better suited for very large datasets
When to Contact EBM Support
Contact support if:
- The error persists after 30+ minutes of waiting
- Multiple users are experiencing the same issue simultaneously
- The issue started immediately after a Catalyst update or change
- You've tried all optimization steps without improvement
- You need to verify whether scheduled processing is causing conflicts
📧 Submit a Support Request
Include the exact error message, when it started, and what you've already tried.
Contact EBM Support →Related Articles
Locking conflicts are typically temporary and resolve within minutes as backend processing completes. If the error recurs frequently, focus on optimizing your PivotTable structure—reducing row items, applying filters early, and breaking large reports into smaller, focused views. These changes not only prevent errors but also improve overall refresh performance. When in doubt, wait a few minutes and try again before escalating to support.
Comments
0 comments
Article is closed for comments.