On Prem SQL Analysis Service Timeout, Slow response and download restrictions

[Charles-CW]

Good day BI Community,

We recently moved our AS from Azure to an on-prem server. I am seeking for advise on issues that we are having running an on-prem SSAS with the On-Prem data gateway. I don't think the server resources is an issue, it is running a 48 core CPU and 280Gb RAM with plenty of disk space . During peak in a day this server only runs at around 32%. This server is only used for our SQL databases and the SSAS and no other application are running on this server.

We have about 14 Power BI Reports that is currently consumed by around 200 users and it is all connected with a live connection to the SSAS.

These are the issues that we are experiencing:

1. Extremely slow response, this is even on pages with no complex measures and have just a simple aggregation. The visuals at times would just run the little circle and eventually time out with an error: "An exception occurred due to an on premise service issue" as seen in the image below. When you refresh the browser page it will work fine and the data will populate. From time to time it works fine and you don't get any errors. I though it might be network traffic with all the users accessing the data but I even tested this in the evening with the same results.

2. I noticed that since moving to the on-prem SSAS I am now only able to download about 5mb of data. I have a table with around 70 000 rows and about 25 columns. When downloading this file it only downloads around 39 000 rows up to the 5mb. I deleted most of the columns to around 8 columns and it then downloaded all 70 000 rows but the file wa sonly 3Mb. I have seen some posts related to this restriction but that seems to be more when downloading from Power BI Desktop. Is there a restriction on SSAS not downloading more than this?

3. Exporting the report Power Point. We have a customer review report that is exported to a Power Point that the account managers then share with the clients. When downloading this report now with 14 pages, it downloads the file to around 9mb but on some of the pages will have an error: "This visual was not exported due to a timeout" as seen below and the other pages will be fine. How can we fix this?

I have read through the article fomo the guys at SQLBI - Optimizing memory settings in Analysis Services - SQLBI and made some changes to the memory as suggested but that made no difference. 

Is there certain limitation using SSAS or is there specific setting somewhere that we might be missing to get past these issues.

I will greatly appreciate any advice on this as we are having a lot of frustrated users.

[v-yohua-msft]

Hi, @Charles-CW 

Make sure that the network connection between the Power BI service and your on-premises data gateway is stable and has sufficient bandwidth.
Use SQL Server Profiler to track queries sent to SSAS instances and identify any queries that may be causing performance issues. You can check the following link:

Improve SSAS Performance with the Usage Based Optimization Wizard (mssqltips.com)

If an SSAS instance is experiencing memory pressure, it may start canceling queries to free up resources. You can monitor the memory usage of your SSAS instance to see if this is the case. If necessary, you can adjust the memory settings of the SSAS instance. You can check the following link:

Troubleshoot the on-premises data gateway | Microsoft Learn

Analysis Services memory properties | Microsoft Learn

Maximum Capacity Specifications (Analysis Services) | Microsoft Learn

SSAS itself does not limit the amount of data that can be downloaded. However, Power BI does have data export limitations, which can cause you to run into problems. You may want to consider reducing the number of columns or limiting the number of rows exported to stay within this limit. You can see the documentation:

Export data from a Power BI visualization - Power BI | Microsoft Learn

This issue may be due to the time it takes for visuals to render in the report. Power BI has a timeout for exporting visuals to PowerPoint, which can cause timeout errors if visuals take too long to render. You can try simplifying visuals or reducing the amount of data they display to help alleviate this issue. Here is the documentation:
Export entire reports to PowerPoint - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Use SQL Server Profiler to check where exactly your SSAS instance is stuck.  Is that an OLAP cube or SSAS Tabular?

[Charles-CW]

Hi @lbendlin ,

It is an SSAS Tabular.