1

I am using Azure DWH tables in power bi report. Whenever the report queries were refreshed until the refresh completed I am unable to execute any queries in SSMS for the same Azure DWH connection.

Please find the below attachments.

Refresh button

while refreshing the power bi

enter image description here

when queries the same table or other , the data is not accessible.

please find the query used in SSMS tool.

enter image description here

Here my table consists of only 29 records but in my original scenario the table has 10 million records. Until the refresh completes, I cannot even get the result of the following query

Select Getdate()
Smart003
  • 1,119
  • 2
  • 16
  • 31

1 Answers1

3

This is caused by the concept of concurrency limits in Azure Data warehouse. Essentially by default your login is set to smallrc (resource class) which only has access to two concurrency slots and probably uses both for your refresh.

You can verify this is the issue by creating another user and trying to run your PowerQuery with one login and your SSMS query with another.

You can also change your resource class by running: EXEC sp_addrolemember 'largerc', 'loaduser';

If needed you can read up more on resource class and concurrency management here: https://learn.microsoft.com/en-us/azure/sql-data-warehouse/resource-classes-for-workload-management

It's a complex subject overall and may be easier to dig through that whole document versus my attempt at explanation.

Finally, just a note of advice, unless you are planning for a large OLAP workload (larger than a terabyte and heavily CPU bound) and planning on putting in some sort of semantic layer between the users doing queries and the DW I would suggest just a plain SQL Azure DB with ColumnStore enabled on relevant tables.

cbattlegear
  • 834
  • 4
  • 11