Excel pivot connected to SSAS tabular model locking up when filtered on record that no longer exists and also has another large filter

Question

Hello, I found a potential "bug" or performance issue for a specific setup when referencing an analysis services cube via an Excel pivot table. The specific scenario is like the below:

• You have a pivot table referencing an analysis services cube (found in Azure Analysis Services)
• The pivot has a large filter, with many items selected (the more selected the worse performance)
• Lastly, you have a dimension pulled into the cube and it is filtered onto a record that no longer exists within the cube.

Here is an example:

I have a large filter (random) with 1000 items selected. Under scenariodesc, I removed the 2023A scenario that we are filtered on here.

In this situation if you right click and hit refresh on the Excel file, the pivot will repeatedly send discover event queries to the AS server, it sends a discover query for each item that you have selected onto in your large filter. This refresh here took 5 minutes. But it can be worked around by simply reselecting the scenariodesc filter manually. Doing that, you get the same output, but it is nearly instant.

Curious if anyone else has experience this issue before as well. Or how this could be fixed

Answer 1

Hi @Ted Rogers

If the filter conditions of an Excel pivot table (such as a specific value of a dimension) no longer exist in SSAS, it may cause unnecessary calculations or rolling back the query, thereby affecting performance.

Please ensure that the SSAS Tabular model used by the Excel pivot table is up to date. If the SSAS data model has changed (such as some records or dimensions being deleted), you can update the data synchronously by reconnecting to SSAS or refreshing the PivotTable.

If the filters contain deleted records (for example, a dimension value was deleted), try removing the filters. You can manually modify the filter conditions in the PivotTable report, or reset the PivotTable report.

For large filter conditions, try to narrow the filter scope and reduce the number of data scans. You can apply different filters in batches to avoid filtering too much data at once.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Doing that, you get the same output, but it is nearly instant.

Đèn Led MPE