Search code examples
crashssmsdaxsql-server-2016ssas-tabular

Microsoft SSAS-Tabular Model: SSMS crashes after 90 seconds - EVALUATE 'LargeFactTable'

I have a Microsoft SQL Server 2016, with Tabular Model (SSAS-TM) as the native instance of Analysis Services.

I developed a project in Visual Studio 2015 and deployed it in the SSAS-TM server.

I am able to query dimension tables easily using the SSMS (SQL Server Management Studio).

However, for large fact tables (about 5 M records), the following query crashes the SSMS, roughly about 90 seconds:

EVALUATE 'LargeFactTable'

I have installed all the necessary service packs, the latest being SP3 for SQL Server 2016, a cumulative release in Sep 2021.

Is there any memory setting that I should change ? May be in the SSMS properties for the Analysis Services Server. Please let me know.

Solution

  • I would never EVALUATE more than 1000 rows in SSMS. It's not meant to be a client tool. Instead, use Power BI Desktop, or Excel if you must. If you want to report on more than a million rows in Excel, you either need a live connection or load the data to PowerPivot.

    If the end result is that you are trying to export the data from SSAS, either use DAX Studio or Excel VBA.