Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi guys,
I was wondering if it is possible to set incremental refesh up out of the box. Without the use of SSMS. Microsoft seems to support it if I read everything in their article, but I cannot get it to work:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
I have my fact table and 2 parameters:
This is the entire M query behind the FACT_Sales:
let
Source = Sql.Database("xxx.database.windows.net", "xxx"),
dbo_FACT_Sales = Source{[Schema="dbo",Item="FACT_Sales"]}[Data],
FilteredRows = Table.SelectRows(dbo_FACT_Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
FilteredRows
Then I've setup the incremental refesh on the FACT_Sales table:
Then I saved and published the model to the service on a premium workspace.
The FACT_Sales table has 2 million records and takes about ~1-2minutes to load. But on purpose I have only a single record within the past 7 days, so I'd expect the refresh to be a few seconds, as it should only refresh the past 7 days. However, every time the refresh takes 1-2 minutes, showing that it loads the entire 2million records:
What do I need to do to make sure the refesh only works for the past 7 days, and ignores all other records? I have the feeling it is not really partitioned, but as far as I know the incremental refresh setup should account for that.
What do I miss, any suggestions are appreciated!
Kind regards,
Igor
Is this only for testing or your planning for doing this in production?
Alright, I have insight now in SSMS in the partitions, and here you can see it is currently only loading that single record.
However it takes just as long (2 minutes), while 1 record should probably only last a few seconds.
When I did not have partitions, it took 2 minutes to load all the records (the ones in partition 2024Q30801).
Or it isn't really working, even though it shows correct details (since I only want it to refresh the past 7 days),
Or having partitions automatically means the scheduled refresh always takes 2 minutes, no matter if you have new rows or not.
Any ideas what this could mean?
"it is taking two minutes" can be a lot of things, including the service being busy with other stuff etc. You will have to provide a lot more data (millions of rows per partition) to see any meaningful effect.
Alright. Reason why I was asking is that before partitioning the scheduled refresh took between 1 and 5 seconds Every single time when it had only ~5K records (~15 times tested). When it had 2 million, it Always took 2min (~15 times tested). And with partioning it still takes 2 minutes (~30 times tested). So I think just a random server things sounds weird to me here with this many tests, its getting statistically unlikely.
So my hypothesis still stands; partitioning is nice but adds minutes delay.
Meanwhile I will create a second dataset with 100 million records and test the same. Because we do want to take this to production eventually if it works properly.
Note that every time you make meta data changes and re-publish to the workspace this will reset the partitions, and do a full refresh. Only the NEXT refresh will then re-start the incremental partition processing.
You need to use SSMS or DAX Studio to examine the partition rows and last update date.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
39 | |
27 | |
21 | |
21 | |
10 |
User | Count |
---|---|
44 | |
36 | |
35 | |
19 | |
15 |