Power BI refreshes failing against Synapse Workspace if Synapse is updated by its source - is this correct?

Anon101 51 Reputation points
2022-11-23T17:11:08.947+00:00

Hi,
We have recently introduced Azure Synapse in our reporting landscape. The purpose of Synapse is to have a Synapse Workspace to store data from our Dynamics source so Power BI can report on it. We were using Data Export Services (DES) to move data from Dynamics to an Azure SQL Server. However, MS have deprecated DES and so we are using Synapse as the substitute.

When I refresh a Power BI report or amend the report's Power Query it will re-evaluate itself and seek a data update from the Synapse Workspace. What I have found, and this happens 50% of the time during working hours, is the report's re-evaluation will fail if Synapse is being updated by Dynamics during the same time. Some of the reports are complex and can take up to 40 minutes to complete its re-evaluation/data refresh. If such a report is using 10 Dynamics entities and one of those entities is updated during the same 40 minutes period - the report will fail.
The error message received is the 'Operating system error code 12' error. I have included the error message's text below, with the sensitive details replaced with xxxxx.

!-------------------------------------------------------------------------
DataSource.Error: Microsoft SQL: Cannot bulk load because the file "https://xxxxxxxxx.dfs.core.windows.net/dataverse-xxxxxxxxxxxxxx/account/2022-04.csv" could not be opened. Operating system error code 12(The access code is invalid.).
Statement ID: {6717E457-CBCB-489D-9246-4D6BE934F44C} | Query hash: 0x7018903D879BDFB6 | Distributed request ID: {4AD2EEED-A925-4012-B440-2B4115E082A6}. Total size of data scanned is 697 megabytes, total size of data moved is 4 megabytes, total size of data written is 0 megabytes.
Details:
DataSourceKind=SQL
DataSourcePath=xxxxxxxxxxx-ondemand.sql.azuresynapse.net;Synapse_Dynamics_data
Message=Cannot bulk load because the file "https://xxxxxxxxx.dfs.core.windows.net/dataverse-xxxxxxxxxxxxxxx/account/2022-04.csv" could not be opened. Operating system error code 12(The access code is invalid.).
Statement ID: {6717E457-CBCB-489D-9246-4D6BE934F44C} | Query hash: 0x7018903D879BDFB6 | Distributed request ID: {4AD2EEED-A925-4012-B440-2B4115E082A6}. Total size of data scanned is 697 megabytes, total size of data moved is 4 megabytes, total size of data written is 0 megabytes.
ErrorCode=-2146232060
Number=4861
Class=16

--------------------------------------------------------------------------

Having Googled the error it seems to suggest you cannot read (just reading it) the Synapse Workspace's data if that same data (same CSV file) is being updated by the source, in my case Dynamics.
I have generated the error and taken note of the failing entity. I then looked at the same entity's CSV file and could see it was updated by Dynamics during the time the report was refreshed which resulted in the error being generated.

Lets say, Dynamics is updated by colleagues during the hours of Mon-Fri 7am-7pm. If I were to work on a Power BI report (amending the code or refreshing the data) during those hours then I most probably will receive failure in what I'm trying to do. I find this odd as how am I suppose to work during those same Mon-Fri 7am-7pm hours.

I'm now wondering if the Synapse configuration is not correct.
Has anyone else faced the same problem and it is to be expected when using Synapse? Can anyone advise a solution please?

Thanks.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,669 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee
    2022-11-28T06:32:44.38+00:00

    Hi @Anon101 ,

    Thank you for posting query in Microsoft Q&A Platform.

    To me, it seems you should think of changing your design of report dataset refresh. It looks like you have live connection or direct refresh kind of mechanism hence every time when you refresh report page its actually querying back-end source data directly. Instead, you can plan you design in such a way that your report dataset gets updates in regular intervals and report should populate data from that report dataset not from source data(synapse)

    Please let me know how it goes. Thank you.

    1 person found this answer helpful.