Forum Discussion
Pro Incremental Refresh with Detect Data Changes fails
Hi,
I'm getting an error when I enabled the Detect Data Changes option in the new Pro Incremental refresh feature. My data source is an Azure SQL Database in North Europe. Without enabling this feature, incremental refresh works fine in the service when refreshing. The error I get in the service when refreshing if the Detect Data Changes is enabled (the column I'm using in a different datetime column) is:
Message: Resource name and Location need to match. Resource name: DWIncremental FactPurchase. Location: Polling_36686F87_B3FA_4760_A613_9C6D47A37170.
Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 86c1f61d-9449-460b-b1c3-af4c2b989a25
Request ID: 0952351a-5ce0-d781-a883-4da446ec2abc
Time: 2020-02-23 21:50:45Z
My Power BI service region is UK South so I'm wondering if the data source needs to be in the same region.
Is anyone else encourtering this error?
Thanks
11 Replies
- Hi there
I would think that it should not cause any issues.
What happens when you run query profiler on the database can you see the query get there?Hi,
I've ran a SQL Profiler session via Azure Data Studio and the only SQL query I can see running is the following.
-- network protocol: TCP/IPset quoted_identifier onset arithabort offset numeric_roundabort offset ansi_warnings onset ansi_padding onset ansi_nulls onset concat_null_yields_null onset cursor_close_on_commit offset implicit_transactions offset language us_englishset dateformat mdyset datefirst 7set transaction isolation level read committed- Hi there
You will have to look at all the events when using Azure Data Studio to see the event which sends the query down to the Azure SQL DB.
It does take a little bit of digging to find the query but it should be there, it is for me.
HI AndyDC,
If this issue only appears when you turn on a specific feature, you can open a support ticket contact to power bi service team for further support. I think it may be related to the backend reasons for verify or authorizations.
submit a support ticket
Regards,
Xiaoxin Sheng
I got the same problem
When I Enable Detect Data Change options, the refresh in PwBI Desktop works fine, but when Publish in the service, there is no way I can refresh it.
Here is 02 errors
and:
Message: Resource name and Location need to match.
When I come back to pwbi Desktop, disable Detect Data Change options, then publish again. Now I can refresh it in the service.
Please help me to get more knowledge about this situation.
Thank you guys in advance!
I am having the same issue. Is there a support ticket open and should I submit one too?
I managed to solve this by introducing another column for detect changes. Microsoft documentation is also now updated with this change.
Now I have two columsn:
1. Record timestamp that I use for incremental refresh and in power query.
2. Record modification timestamp that I use in setting up the detect changes.
Advanced policy options
Detect data changes
Incremental refresh of ten days is more efficient than full refresh of five years. However, it's possible to do even better. If you select the Detect data changes checkbox, you can select a date/time column used to identify and refresh only the days where the data has changed. This assumes such a column exists in the source system, which is typically for auditing purposes. This should not be the same column used to partition the data with the RangeStart/RangeEnd parameters. The maximum value of this column is evaluated for each of the periods in the incremental range. If it has not changed since the last refresh, there is no need to refresh the period. In the example, this could further reduce the days incrementally refreshed from ten to around two.
