Erroneous and inconsistent refresh of Datamart'S SQL db

[smpa01]

I have created a datamart

↪ I connected multiple excels  (large excel) from sharepoint folder and deployed table in the datamart (I chose not to use PQ for data transformation).

↪ in turn, I am accessing the table that is available to the SQL server upon deploying datamart

↪ I created a dataflow by querying the SQL db table and used SQL for complex data transformation (chose SQL to achieve performance over output). Also, I need to use cte  and table variable for transformation (which is why it can't be utilized in a Direct Query---I would have been extremely happy if DQ supported  cte and other crazy sql staff. I can altogether bypass a df)

↪ FInally the BI is built by querying the dataflow.

However, the scheduled refresh to the dataflow (querying SQL db) is extremely inconsistent. The SQL query is super optimized, yet I see this. Sometimes the refresh goes through, sometimes they don't. It only takes 6sec for a 300k+ db tables (rapidly incrementing).

The final report is extremely time-sensitive and I can't have so frequent refresh failures. How can I know what is going on with the server, why there is so large a difference between different refreshes and how can I ensure that it never happens?

I am not looking for Power Automate suggestions (trigger subsequent refresh upon current refresh failure - there is no guarantee that the subsequent refresh will not fail again). I need to know how can I access and tune the server.

[smpa01 · Accepted solution]

I had an elaborate discussion with MS support staff upon opening up a ticket on this issue. They recognized the issue and they confirmed that it is happening due to datamart currently being in preview which is not a stable version. I was also informed that MS has already received a lot of similar complaints from other users where the refresh of the dataflow built upon a datamart's server is inaccessible/erroneous at times (both through SSMS/datamart connection). I will assess this again once a stable datamart version is released.

[v-cazheng-msft]

Hi @smpa01,

What kind of errors will you see when you click on the refresh fail message? What kind of capacity is your dataset resided in? Shared capacity or Premium capacity? Do you have Premium capacity?

Best Regards,

Community Support Team _ Caiyun

[smpa01]

@v-cazheng-msft  thanks for the prompt response and apologies for the delay in replying.

I am getting this error

Error: PipelineException: Internal connection fatal error. Error state: 15  Token : 0 . RootActivityId = Lorem ipsum dolor sit amet.Param1 = PipelineException: Internal connection fatal error. Error state: 15  Token : 0  Request ID: Lorem ipsum dolor sit amet.

I am on premium capacity.

[smpa01]

@v-cazheng-msft  any update?