How to process data from sql server to azure analysis service without getting timeout?

Thor 1 Reputation point
2023-03-30T09:45:37.5033333+00:00

Hello!

Every day, I need to process data from SQL Server to Azure Analysis Services (which I use as a data source for my Power BI report). I have set up a runbook in Azure that uses a PowerShell script to accomplish this. I followed this tutorial: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-refresh-azure-automation.

The problem is that the refresh process takes a very long time, and we have recently been experiencing timeout errors. If I increase the DTU on the Azure portal for our SQL Server, then the process doesn't time out. However, I would like to optimize my processes rather than having to upgrade the SQL Server every few months.

That script does a "full" refresh and as I understand correctly it means that it drops all data and reads it in again.
Is there a way I could only process new and changed data?

I have many tables that are processed daily, but the table that causes the entire operation to fail is the SalesFacts table, which has over 8 million rows and is growing quickly. I have tried increasing various timeout parameters, but with no success.

How can I fix this problem?
Any help is appreciated.

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
431 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Bjoern Peters 8,776 Reputation points
    2023-03-30T14:37:38.48+00:00

    Hi Thor

    Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

    Thank you for posting your question; I'll be more than glad to help you out.

    There is at least one option to resolve your problem, but that is (might be) a significant impact for you and your environment to prepare for...

    Change the database structure to partitioning on that big table(s).

    If you partition your table(s), you are able to run the "full" refresh only on the last partition of your data... depending on the partitioning key and size... that latest partition might only include the last day, week, month, or quarter...

    Also, your queries/reporting might benefit from this change, as the query also just uses the last partition for querying, as SQL Server "knows" about those partitions and creates an execution plan according to the required date range.

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments No comments