Performance Problems with the Excel SSAS Tabular Model

Prabu Chandran 1 Reputation point
2022-07-04T17:04:30.073+00:00

It functions properly when I perform pivoting measures with just one dimension field. The OLAP rotates for many minutes, often 20 to 30 minutes, as soon as I pull the field from other dimension tables. The identical MDX version of the cube functions perfectly.

What drawbacks might there be to the Tabular Cube model? Are there any table joins that need improvement?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 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,243 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2022-07-05T05:01:23.413+00:00

    Hi @Prabu Chandran ,
    In most cases, Excel always perform poorly with Tabular Models.Excel Pivot Tables query Tabular SSAS using MDX, and sometimes the MDX they generate is not ideal. And response times can be particularly slow where the tables have lots of columns and are large in size.

    In order to improve the performance of an Excel report based on a PivotTable, consider the following best practices:

    • Only in SQL Server Analysis Services: Reducing the number of measures included in a PivotTable. (No longer required in Power BI and Azure Analysis Services since October 2020).
    • Optimizing the performance of the measures used in a PivotTable.
    • Avoiding any CallbackDataID especially in measures that are referenced multiple times in the same report.
      Best Regards,
      Joy

    If the answer is the right solution, 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.

    0 comments