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
cpwebb's avatar
cpwebb
Microsoft Employee
2 years ago

I guess the reason scenario (1) works is that, initially, there's no data in your model and since the tables are partitioned that will reduce the amount of parallelism indirectly. If you are ok with your model not being queryable during refresh you could set up incremental refresh but always do a refresh of type ClearValues (which deletes all the data from your model) first. That feels a bit hacky though and I suspect there's no going on here that needs investigation.

The Parallel Loading Of Tables setting in Power BI Desktop is the easy way to control the amount of parallelism at the table level: https://blog.crossjoin.co.uk/2022/10/31/speed-up-power-bi-dataset-refresh-performance-in-premium-or-ppu-by-changing-the-parallel-loading-of-tables-setting/ Setting this to One will ensure all your tables are refreshed sequentially.

 

What do you mean by "child" tables? Are these dimension tables? Are you deriving them from your fact tables somehow, maybe in Power Query?

  • hoosha_11's avatar
    hoosha_11
    Helper I
    2 years ago

    Thanks again cpwebb , I followed the same steps to update the Parallel Loading Of Tables setting in Power BI Desktop.

     

    Since the PeakMemory for the refresh as a whole is around 3.5 GB and I still face Memory capacity errors (even after tuning the power query for a table with very high MashupPeakMemory) , I looked into the calculated columns again and found something interesting:

     

    We have 2 fact tables, and each fact table in linked to multiple dimention tables:

    Fact table A with 350,000 rows.

    Fact table B with 9 million rows.


    There was a calculated column in Fact table A which was referencing both Fact table B and a dimension table linked to the table B.

    Before removing that specific calculated column:

    PeakMemory for the refresh as a whole: 3.5 GB

    PeakMemory for Fact table A: 1.95 GB

    PeakMemory for Fact table B: 2.85 GB

    PeakMemory for the dimension table: 1.8 GB

    After removing that specific calculated column:

    PeakMemory for the refresh as a whole: 2.4 GB

    PeakMemory for Fact table A: 1.3 GB

    PeakMemory for Fact table B: 1.4 GB

    PeakMemory for the dimension table: 175 MB 

     

    Considering the PeakMemory of 2.4GB, I still sometimes face memory errors despite being on an A2 SKU with 5GB of memory.

    (I have defined an incremental refresh policy for the last 6 months with maxParallelism set to 3).

    Since each table uses around 150-200MB of peak memory, and we have only 2 tables with a peak memory usage of around 1.4GB, could the parallel loading of tables be causing this error?

    I believe that maxParallelism = 3 applies only to the partitions, not the tables. Is that correct? It would be great if we could schedule a refresh to refresh each table sequentially, similar to how we process the whole table in SSMS Analysis Services.

    I even created a PowerShell script in Azure Runbooks to schedule a refresh with Logic Apps, but when I add "maxParallelism = 3" to the script, I get an error.

     

    Thanks a lot!