question

BrainE-1601 avatar image
1 Vote"
BrainE-1601 asked ·

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

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.


sql-server-analysis-services
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BrainE-1601 avatar image
0 Votes"
BrainE-1601 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered ·

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:
17941-1.jpg

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

Regards,
Lukas



1.jpg (11.9 KiB)
· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm a little skeptical, but I will try that. If the default is 60 seconds, but the preview bombs out at exactly 30 seconds, that might not be the answer...

But I want to try that if you can tell me where to find that setting.

0 Votes 0 ·

Yes, you are right. For now, I am thinking this should be more possible a problem with some connection opened in visual studio, if we have all timeout in SSAS server to "0"
Unfortunately I don't have too much knowledge about AS vsix inside structure. Maybe you could ask the issue at Microsoft Analysis Services Projects

And also you could give you advice about the Preview part of the tool.


0 Votes 0 ·