I would like to setup for users work with data located on Azure Synapse Dedicated SQL using Excel. So, now I have Excel -> Power Bi Premium (model and DirectQuery) - > Synapse SQL
For testing purpose, I am using only 3 tables, 2 small dimension tables (replicate distribution) and one big fact tables (93 million rows) (hash distribution). My synapse is DWU100. So, I have a few questions:
- When I added in PivotTable one column in row from small table, it generates 2 identical SQL query on Synapse. Why is that? I saw this behavior quite often.
- Then if I would like to use data from fact tables and add some measures to values I have error
When I investigate on Synapse, I cant understand the main cause of problem. I see again identical queries with cancelled status and some hash_reader error on shuffle operations.
and in error table no any meaningfull description
Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.ActionCancelledException: Query Cancelled. ---> Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.ActionCancelledException: Exception of type 'Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.ActionCancelledException' was thrown. at Microsoft.SqlServer.DataWarehouse.Distributor.SqlDistributorTask.HandleError(Exception handledException) at Microsoft.SqlServer.DataWarehouse.Distributor.SqlDistributorTask.Wait() at Microsoft.SqlServer.DataWarehouse.Engine.Executables.SqlExecutable.TransactionalWork.CleanupSqlDistributor(SqlDistributor sqlDistributor) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.SqlExecutable.TransactionalWork.ExecuteUnitOfWorkForComputeNodes() at Microsoft.SqlServer.DataWarehouse.Engine.Executables.SqlExecutable.ExecuteWork(TransactionScopeOption option, Action callback) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.SqlExecutable.OnExecute(ISessionContext sessionContext) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.DistributedExchangeExecutable.OnExecute(ISessionContext sessionContext) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.AbstractExecutable`1.Execute(ISessionContext session) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.ExecutableProcessor.<>c__DisplayClass40_1.<ExecuteExecutable>b__1(IList`1 propagationTokens) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.ExecutableProcessor.ExecuteActionInDistributedTransactions(Int32 transactionsCount, Action`1 action, IList`1 propagationTokens) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.ExecutableProcessor.ExecuteExecutable(EngineExecutionPlan plan, IExecutable executable, ExecutionContext context) at Microsoft.Practices.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0() at Microsoft.Practices.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.ExecutableProcessor.ExecutePlan(EngineExecutionPlan plan, ExecutionContext context) at Microsoft.Practices.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0() at Microsoft.Practices.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.SqlServer.DataWarehouse.Engine.Executables.ExecutableProcessor.RunExecutables(EngineExecutionPlan plan, IList`1 executables) at Microsoft.SqlServer.DataWarehouse.Engine.Processors.DistributedQueryProcessor.ExecuteDistributedPlan() at Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback) at Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlProcessor.ExecutePlans() at Microsoft.SqlServer.DataWarehouse.Sql.Utilities.QPTelemetryInfo.ExecuteAndTime(Action callback, TimerType timerType) at Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlProcessor.OnExecuteRequest() at Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback) at Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnProcess() at Microsoft.SqlServer.DataWarehouse.Engine.Transactions.TransactionContext.DoTransactionalWork(Action callback, Boolean abortTxnOnFailure) at Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnExecute() at Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigg
I have checked tempdb, it seems ok. Memory ok. If DWU100 is very small to handle 93 millions of rows, would be good to have some error description that no resources or something like that.
When I use smaller fact tables for testing with the same configuration, everything is fast and no errors.
How I could troubleshoot further? From my perspective it is strange, I have only one fact table, yes, it is big but it is shuffling data with itself and due to many rows stuck on something.
Also another example, just using fact table, adding date column to PivotTable, nothing happens in excel, it shows windows to select date and after query completed on Synapse just close filter window in Excel
