Forum Discussion

Charles-CW's avatar
Charles-CW
Advocate I
2 years ago

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

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.

3 Replies