Search code examples
ssaspowerqueryssas-tabular

SSAS Tabular - Power Query Editor stuck at "Operation in progress" - "identifying schemas"

In Visual Studio, with an SSAS Tabular model open, in the Power Query Editor window, when I make a change to a large partition (1 million+ rows) that sources its data from an Azure SQL Database, the edit and preview happens quickly in the Power Query Editor window itself. However, when I click "Close & Update" or "Close & Update Without Processing" this message appears for a very long time ("Operation in progress" - "identifying schemas"):

"Operation in progress" - "identifying schemas"

At the same time, Task Manager shows Visual Studio downloading at several Mbps the entire time, so I am assuming that Visual Studio is attempting to download the full contents of the table.

Is there a way to prevent this behavior? I was thinking that "Close & Update Without Processing" would prevent this behavior but it does not.

My current workaround is:

  1. Rename the Azure SQL Database table.
  2. Create a new empty Azure SQL Database table with the same name and fields as the original table.
  3. Perform the "Close & Update" letting it use this empty table as a source so it completes instantly.
  4. Delete the new empty Azure SQL Database table.
  5. Rename the Azure SQL Database table back to what it was previously.
Solution

  • The work around that I have found is to use a parameter to limit the number or rows being used for development. An example of how to set this up is here: https://blog.crossjoin.co.uk/2018/02/26/filtering-data-loaded-into-a-workspace-database-in-analysis-services-tabular-2017-and-azure-analysis-services/