Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Incremental refresh Power BI dataset, how does it work?

Hello

 

I have built a view (an aggregate) on a 35 billion row SQLServer table, 7 years of history. Every day around 14 million rows are added. The view is used in Incremental Refresh (1 Days)

The table has clustered columnstore index. And a unique, non-clustered primary key where creation-date (CRT_DT) is the first column. It is also partitioned on creation-date.


When I refresh today, is SQLServer then reading those 35 billion rows, aggregate them and then select the rows of today to pass through to Power BI?
Or is it immediately selecting only the rows of today (around 14 million) , aggregating them and passing them through to Power BI?

 

I am asking this for a better understanding. But also because, if I do a select query on the 35 billion row table for a specific creation-date and that is equal to the select in the view I get answer within 20 seconds

When I refresh one day partition in Analysis Services in SSMS, the query goes to status suspended, wait type CXCONSUMER.
And stays that way for a very long time.

Regards
Ron

Need Help

3 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hello,

     

    I had two initial thoughts on your question:

     

    • Have you read Microsoft's documentation on incremental refresh? I scanned through and the first section the "Required settings" section seem the most relevant to your overall question.
    • More specifically, a common issue to check: Is query folding intact on that particular query? The easiest way to see if that's true is to right click on the last step in the query and see if the option to see native query is enabled and not greyed out. Is your Power Query break query folding, it's possible it's unable to do an incremental refresh and is doing a full refresh every time instead.

     

    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi, query folding is there. The problem is that the main query is in a Common Table Expression, meaning I presume it will read everytime the whole table. And I think the wait type CXCONSUMER is there because it is waiting for data to consume. But I am not sure

    • Wilson_'s avatar
      Wilson_
      Icon for Memorable Member rankMemorable Member
      3 years ago

      Hello,

       

      I'm not sure. You can probably take the native query from Power Query (I'm guessing it will add the date filters to the WHERE clause?) and check the query plan in SQL and compare it to an unfilitered query plan to see what changes when it runs through incremental refresh (or at least when it's filtered between RangeStart and RangeEnd, hah).

       

      Just some thoughts.


      ----------------------------------
      If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)