Pro Incremental Refresh with Detect Data Changes fails

[AndyDC]

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

[betizad]

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.

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#:~:text=Incremen....

---

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.

---

[Limecows]

I am having the same issue. Is there a support ticket open and should I submit one too?

[Anonymous]

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!

[v-shex-msft]

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

[GilbertQ]

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?

[AndyDC]

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/IP

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level read committed

[GilbertQ]

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.

[AndyDC]

Hi,

I can see the SQL that is being sent if I don't enable the Detect Data Changes feature.  Unfortunately if it's enabled then the SQL never gets sent to the SQL Database.

[GilbertQ]

Hmm that looks like a bug to me then. I would suggest logging a support ticket.

[betizad]

I do face the same issue. I can perfectly make it work on Azure SQL DB and Cosmos-DB as long as "Detect data changes" is disabled. As soon as I enable the feature, I get the following error.

Something went wrong

There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
MessageResource name and Location need to match. Resource name: EUI. Location: Polling_36686F87_B3FA_4760_A613_9C6D47A37170.
Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net
Activity IDc2cc7ddf-b1f3-4870-8b1f-d5aef96805b1
Request ID7b525d2b-b2c4-48cb-b657-6ec698ff73fa
Time2020-02-27 13:48:50Z

[shaunwilks]

Yes I can add to the weight of people suggesting this is a bug in the Gateway/PBI refresh.

I re-wrote all my queries to support folding.

6 queries in total, 4 use Detect changes the otehr 4 just normal incremental settings.

After finding that the two with detect changes were failing I disabled just that option on the incremental referesh and tried again.

Of course the entire refresh went through without an error so it was working well when Detect Changes was disabled.

That now tells me there is a clear issue in Detect Changes for PBI PRO and Incremental Refresh