cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sicilian Member
Member

Incremental refresh -Detect Data Change column vs Partition Column

Hi everyone,

 

We have PBI Premium and I am attemping to have one of our reports use Import from the SQL Azure data warehouse and incrementally load 2 of the main tables. I've followed the documentaiton, but I am having some issues. Is there an easy way to troubleshoot or test what is actually refreshing and/or what the errors are?

 

One thing I noticed here: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh#detect-data-changes

 

Which says do not use the same column for the "Detect Data Changes" as the column you use against the RangeStart and RangeEnd parameters. We do have an auditing/process date/time column on all DW tables which indicates when it was loaded. My thought is to simply use this for the Detect Data Changes column, but then I don't know what to use for the partition column for RangeStart/RangeEnd parameters. We really only need to see the last DW load time in the process date/time column.

 

All advice is appreciated! Thanks.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Incremental refresh -Detect Data Change column vs Partition Column

hi, @Sicilian 

RangeStart and RangeEnd parameters

For incremental refresh, datasets are filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. These parameters are used to filter the data imported into Power BI Desktop, and also to dynamically partition the data into ranges once published to the Power BI service. The parameter values are substituted by the service to filter for each partition. Once published, the parameter values are overridden automatically by the Power BI service. There's no need to set them in dataset settings in the service. Once published, the parameter values are overridden automatically by the Power BI service.

 

For your case, you need to set the RangeStart and RangeEnd parameters for process date/time column, and then use auditing date/time column for "Detect Data Changes".

 

here are documents for you refer to:

https://community.powerbi.com/t5/Community-Blog/Important-Considerations-for-Setting-up-Incremental-...

https://www.nabler.com/articles/Power-BI-data-refresh-and-scheduling-3.asp

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sicilian Member
Member

Re: Incremental refresh -Detect Data Change column vs Partition Column

Hi, 

 

Thank you for the reply. The issue is that my main fact table only has the process date/time column - this is the audting column which is updated in SQL each time the data is loaded. I do not have any other date/time columns in the fact table. I believe I should use this as the Detect Data Changes column, since it will always have a single date of the last load, but I do not have any other date time column to use for RangeStart and RangeEnd. How do I handle this situation? The documentation says not to use the process date/time column for both.

 

Mark

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 115 members 1,572 guests
Please welcome our newest community members: