Analysis Services taking too much time to refresh SSAS tabular model

Ali Javed cheema 0

Hi,

I have a data set of somewhere 40M records in SSMS. My tabular model earlier use to get refresh on S1 tier with 25-30 min. But now I'm using S2 tier and even still its refreshing the data at approx 60 min or sometimes failing and showing timeout error.

Can someone assist me where the issue lies as my ideal case is that I want to run analysis services on S1 tier again and time should reduce as well which is my top priority.

Thank you

1 answer

MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-26T09:14:30.1166667+00:00

Hi，Ali Javed cheema

40 million records is considered a large amount of data. You could try setting up an incremental load, it will reduce the amount of data to send to Azure.

Or you can think about partitioning, you can refer to this link for more information: https://learn.microsoft.com/en-us/analysis-services/tabular-models/partitions-ssas-tabular?view=asallproducts-allversions.

Regarding the loading timeout issue, you can try increasing the timeout value of the refresh operation. You can do this by modifying the refresh settings in Azure Analysis Services.

Regards

Mikey Qiao

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.
Please sign in to rate this answer.
Ali Javed cheema 0 Reputation points

2024-08-26T09:27:39.5333333+00:00

Hi @MikeyQiaoMSFT-0444

I need to process my old data each time for a SP and also there can be changes in historical data so I can't use incremental processing.

I am already using partitions on my tabular model based on the data in the table. Earlier same model was performing efficiently on S1 tier and completing in 25-30 min. But now it has crossed 1 hour limit and I am using S2 tier to process the data. I can increase the timeout value but that's not the proper way to deal with this issue.

Smaran Thoomu 17,525 Reputation points Microsoft Vendor

2024-08-27T17:25:30.3466667+00:00

@Ali Javed cheema Thank you for providing more context about your issue. I understand that you need to process your old data each time for a stored procedure and that there can be changes in historical data, which makes incremental processing not feasible.

I also see that you're already using partitions on your tabular model, which is a good practice. Given that your model was performing efficiently on S1 tier earlier and completing in 25-30 minutes, it's surprising to see that it's now taking over 1 hour on S2 tier.

Before we explore other options, I'd like to ask a few more questions to help us narrow down the issue:

Have there been any changes to your data source, such as an increase in data volume or changes to the data structure?

Have you noticed any performance issues with your Azure Data Factory (ADF) pipeline or the underlying infrastructure?

Are there any other workloads or processes running concurrently that might be impacting the performance of your Analysis Services instance?

Once we have more information, we can explore potential solutions, such as optimizing your data processing, adjusting your partitioning strategy, or investigating other performance tuning options.

Regarding the timeout issue, I understand your concern about increasing the timeout value not being a proper solution. We can work together to identify the root cause of the issue and find a more sustainable solution.

Please let me know your thoughts on the above questions, and we can proceed from there.
I hope this helps! Let me know if you have any further questions.

Smaran Thoomu 17,525 Reputation points Microsoft Vendor

2024-08-29T16:06:00.04+00:00

@Ali Javed cheema Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Analysis Services taking too much time to refresh SSAS tabular model

1 answer

Your answer