Forum Discussion

AnandGNR's avatar
AnandGNR
Icon for Helper I rankHelper I
4 years ago

Incremental Refresh takes almost same time as Full refresh in PBI Service

Hi, I have set up incremental refresh on 2 fact tables that are part of my dataset. I have given an active range of 12 months with an archive of 2 years and use LastModified to detect changes. I kno...
Need Help
AnandGNR's avatar
AnandGNR
Icon for Helper I rankHelper I
4 years ago

Hello! Thanks for responding.

1. Data source for PBI are Synapse Serverless Views based on Gen2 Azure Data Lake (Delta - parquet files).

2. With regards to concurrency and time etc: defenitely are valids concerns but I did try to run tests (full and incremental) at different times of the day including some late hours post business. Results were always consistent so far in that full load refresh time is pretty much the same as the incremental refresh time with the same kind of range ratios that I had mentioned above when I look at the capacity metrics.

 

Partition sizes : roughly around 700,000 rows per active range monthly partition. I have added a snip of the distribution after an incremental refresh below. 

 

One additional aspect that i found was that a model with a bigger footprint(archive with 10 years and active range of 12 months with the exact same row counts in the partitions) takes more time on an incremental refresh as compared to a smaller footprint model such as the one below ( 2 years archive and active range of 12 months) which also seems strange if incremental refresh hits only the needed partitions. In a bigger model too, i found the same behaviour of the full refresh just maybe a 1-2 mins longer than the incremental refresh.

 

Partitions

 

Thanks,

Anand

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

My guess would be that your data source takes a long time to provision each time you run any type of query, which then negates the supposed advantages of the incremental refresh.  You can do real time monitoring of the dataset refresh to see the gap between connection start and data spooling start.

Visualise your Power BI refresh - in real-time - Phil Seamark on DAX

  • AnandGNR's avatar
    AnandGNR
    Icon for Helper I rankHelper I
    4 years ago

     I did try a trace using SQL profiler mentioned here. Also added Command Event in addition to Progress Events. The command is consistant with the TMSL for an incremental refresh.

    {
    "sequence": {
    "maxParallelism": 6,
    "operations": [
    {
    "refresh": {
    "type": "full",
    "applyRefreshPolicy": true,
    "effectiveDate": "07/15/2022",
    "objects": [
    {
    "database": "XXXXX"
    }
    ]
    }
    }
    ]
    }
    }

     

    Trace also shows that it only hits the needed partitions with a ExecuteSQL command post the "Analyze refresh policy" phase

     

    If Synapse serverless views were slow, I would think that a full refresh would much slower since its hitting all partitions againt the same views based on looking at the Trace. Really drawing a blank..

     

    Thanks,

    Anand