Forum Discussion

hoosha_11's avatar
hoosha_11
Helper I
2 years ago

Defragment not working

Hi,  As explained by cpwebb in this article: https://blog.crossjoin.co.uk/2022/11/14/why-you-should-defragment-your-fact-tables-if-youre-using-incremental-refresh-in-power-bi-premium/   I used "De...
Need Help
hoosha_11's avatar
hoosha_11
Helper I
2 years ago

Thanks again cpwebb for your valuable comment.

I removed all the transformations and started from scratch, and noticed that the groupby (Table.group) query is not folding. Also as you mentioned, changin data type needs to be done after the Merge.

I tried a lot, but I wasn't able to fold the Groupby function, so I created a view in SQL server for the same table with same steps and columns. After refreshing the dataset with this view, and tracing the Profiler, the MashupPeakMemory dropped from 1.4GB to 140 MB for that table, and PeakMemory in CommandEnd for all operations was 3.4 GB. 

 

I followed the same steps for another small dataset, and even though the MashupPeakMemory for that specific table dropped from 1.65GB to 120 MB, the refresh failed again. After some tests, I found that removing 4 calculated columns resolved the issue (I had another table with 9 Million rows and 4 calculated columns).

Below is one of the calculated column that is causing the issue (all other columns have exactly the same function):

Even I had optimized this query before by changing CALCULATE to MAXX!

 

By removing these 4 calculated columns the PeakMemory for all operations dropped to 2 GB.

 

I will try reducing the amount of parallelism to see how much PeakMemory changes.

I guess the challenge is to identify which calculated column is consuming more memory. With a larger dataset containing more columns, it becomes difficult to pinpoint the memory-hungry calculated columns.

 

Thanks again!

cpwebb's avatar
cpwebb
Microsoft Employee
2 years ago

I'm glad to hear you've made progress - pushing transforms as far upstream as possible is always a good thing.

 

Regarding your calculated columns, and DAX expression that uses FILTER() on a whole table always carries a risk of a memory spike. How many rows are in the FollowUps table? It could be that an expression something like this would be better from the point of view of memory:

 

Response(patient contact)_=
var CurrentInspectionId = SELECTEDVALUE(FollowUps[InspectionId])
return
CALCULATE(
MAX(FollowUps[Response]),
FollowUps[InspectionId] = CurrentInspectionId,
FollowUps[QuestionId] = "973")

 Apologies for any typos/syntax errors, or if this is exactly what you had before you optimised.

 

I don't think there's a way of identifying how much memory each calculated column in a table  uses during a refresh, so it will have to be a process of elimination - you'll need to add each calculated column individually, refresh and see how the memory usage is affected.

  • hoosha_11's avatar
    hoosha_11
    Helper I
    2 years ago

    Hi and thank you again, cpwebb . I've continued working on this and managed to create a view of the table that previously had a query folding issue in SQL Server. Also we have 2 large tables, each containing around 9 million rows, where I've transferred almost all of the calculated columns for these tables into SQL server. Also adjusted the maximum parallelisim in PBI desktop to 3 and 1 before publishing. 

    Eventually, the PeakMemory dropped to around 3.4GB-3.6GB, but the refreshes still fail. 

    It's quite confusing, as I'm not sure where to look next.

     

    Here are some findings from my tests:

    1- When I define incremental refresh policy and publish the dataset, the first refresh, which creates the partitions, succeeds (tested multiple times), but subsequent refreshes fails.

    2- Without defining an incremental refresh policy, the first refresh fails. 

    3- The CPU Time for the 2 large tables is 229000 and 103000. Even the manual refresh in PBI desktop for these 2 tables takes a considerable amount of time to complete.

    4- Refreshed each table in SSMS and checked the profiler. We have 2 Fact tables, one with 300K rows and PeakMemory of 1.95GB and the other one with 9 million rows and PeakMemory of 2.9GB. There are 2 other child tables with PeakMemory of 1.7GB and 1.3GB and the remaining tables are around 200 MB. 
    5- Any time I publish the semantic model to PBI service, I observe the dictionary size for the ID columns of the 2 large tables is very big and I have to do the defragment, which sometimes works and reduces the size of dictionary and sometimes doesn't work.

     

    Additionally, I would like to know if it's possible to refresh the tables sequentially in PBI service? Does defining maximum parallelism (let's say to one) affect the tables, or is it only for the partitions?

    I've created a Powershell query in Azure Runbooks to refresh the tables, but defining "maxParallelism" is resulting in an error (perhaps I'm not defining it correctly).

     

    Thanks again, your ideas and comments are really really appreciated!