SSAS Tabular: Query Timeout during Preview, Despite Timeout =0

Question

I am using SSAS Tabular in Visual Studio 2017 against a SQL Server instance (Compatibility 1200)

I have a relatively small query (<1 M rows) that executes fully in 8 seconds in the database. Pulling it into SSAS during design time takes a few extra seconds to transfer the data, but still maybe just a minute or two.

Deployment is fine - never been any problems whatsoever.

Every "query timeout" setting I can find in SSAS is set =0, which should indicate no timeout.

HOWEVER.... in SSAS design mode in Visual Studio, when I try to view the properties for this table to add a couple new columns, the "Preview" bombs out after 30 seconds and tells me the query timed out. When I try to view the properties for the Partitions, the same thing happens. No matter what I do, it will not let me make any new modifications, citing this error: "Failed to retrieve data from dbo.vw_DataView. Reason: Query timeout expired"

Where is the setting for this? Everything I find online tells me to change the query timeout settings, but mine are set to the default, which is no timeout!

Also, can I limit the data preview? I liked multi-dimensional SSAS design-mode better, where you did not have to preview the data unless you wanted to. This silly preview "feature" in Tabular simply eats up all my the RAM on my laptop for no reason. It would be okay if it just "previewed" the top 200 rows, but it doesn't do that. I haven't found any helpful documentation on this.

Answer 1

I found a very "hacky" way around this issue. It allowed me to add the new columns, but it ended up creating a new (minor) problem.

What I did was this: I went into the Partition settings and clicked a little "SQL" button on the right-hand side that changed the source for the table into a query. The query auto-populated as, "select star from view." I changed this query into, "select top 200 star from view." Now, this new query rendered in less than 30 seconds, and i was able to get the new columns added. Once the columns were added, saved, deployed, and safely tucked away in source control, I changed the query back to "select star from view." Voila!

While this "solved" my immediate issue, it still feels hacky. What if, rather than a lot of rows, my query took more than 30 seconds because of a long calculation or an intense group and sort? This hack would not have worked! Further, now I'm left with a query for the source of my table, and there is no way to change it back.

Again, every timeout setting I can find =0, which indicates no timeout. I know for a fact there is no timeout killing my query on the database side because I run longer queries all the time, including some that can take 10+ minutes during deployment.

What in the world is wrong with this silly data preview in Tabular? Can we kill this, or make it optional in a future release? It's awful.

Answer 2

Hi,
I've did some test around Tabular model in Visual Studio, it sees the preview function is built up like this and currently there is not setting to change the row for the preview.
Just to double check have you also set:

The value is by default 60, we could also set this to be 0.

Regards,
Lukas

Answer 3

Had the same exact problem. What I usually do to solve this issue is import a blank view over the table, iso the table itself, just to declare all the columns. It imports automatically and then I just have to switch the view to select * from... when I want to process the data. Hope it helps.