Share via

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

Ted Rogers 0 Reputation points
2024-12-18T19:59:10.02+00:00

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.User's image

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

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
465 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,052 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,300 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 12,240 Reputation points Microsoft Vendor
    2024-12-19T08:18:01.5033333+00:00

    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.

  2. Chiếu sáng MPE 0 Reputation points
    2024-12-19T08:25:29.03+00:00

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

    Đèn Led MPE

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.