Forum Discussion
Helper IIncremental Refresh takes almost same time as Full refresh in PBI Service
Helper IHi Prachi,
1. Incremental refresh tends to have some overhead of its own since it manages partitions at the back end. (create new, merge existing ones etc). But overall it gets more effective as loads increase.
2. Also I think, the point about using an unmutable date is key for both performance and accuracy. Not really sure if this applies to your scenario specifically but typically, "last updated" is used with "detect data changes" setting to further speed up the refresh process. The refresh partitions should be based on an unmutable date (such as "Order Date" if its present in the sales table. I believe lbendlin mentioned creation date as when the record was originally created in the source database and not when it was first loaded into the model ) so that 2022 records are its own partition in this example.
3. There are also model specific characteristics (no of calculated columns, presense of multiple text feilds in the fact data ) that can make the process slow down.
Thanks,
Anand
Hello AnandGNR and lbendlin
I have tried Incremental Refresh (Import Mode) with the TransactionDate where its in DateTime format having the different time stamps and Data detect change propery in Incremental Refresh is set to UpdateDate. But this process is also taking almost 40 mins (previously time taken was around 55-60 mins). Another observation is, data issue. For year 2023 data for 1st Jan is more (more than 2 M) compared to the actual data. This Incremental Refresh I am trying with the XMLA end-point method.
Has anyone faced such type of issue. And what are the solutions which you all have tried.
Thanks & Regards
Prachi
- lbendlin
Super User
Keep in mind that "Detect Change" will create a canary table, doubling your space requirements.
Duplicate data means your filters are set incorrectly. Only one of the ranges can be inclusive, usually RangeStart.
lbendlin Thank you for the quick revert.
Below are the parameters I am using for it,
= Table.SelectRows(#"Changed Type", each [TransactionDate] >= RangeStart and [TransactionDate] <= RangeEnd)
TransactionDate is in DateTime Format and I am using just date part from this column (named as TransactionsDate) to connect to the Calendar dimension table.
My queries:1. Does the mentioned part above makes any difference in the data.
2. Detect data change I am seting on the UpdateDate - this the date (in our datawarehouse table) reflecting when the source data is actually updated. As TransactionDate is the combination 2-3 date columns combination from the source. So we are maintaining InsertDate (data loaded date) and UpdateDate (to reflect the updation in source data).
So, still you have the same concern about the data detect change using UpdateDate ?
Kindly suggest a solution for Import data.
Thanks and Regards,
Prachi- lbendlin
= Table.SelectRows(#"Changed Type", each [TransactionDate] >= RangeStart and [TransactionDate] < RangeEnd)
